Log Shipping as a reporting database.

JHammondJHammond Posts: 6
edited August 16, 2006 2:00PM in SQL Backup Previous Versions
I had an issue with log shipping with SQL Backup v. 4.0. this turned out to be my maintenance script for nightly backup and shrink of the log files. Duh changes the LSN number sql guy...But this led me to another question in my research of the issue. With RED-GATE sqlbackup does the log shipping restore have an exclusive lock on the restoring db's tables. I assume that it does not. I ask this because all of my restores are being used for reporting and redundancy not just redundancy.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi,

    Not specifically a lock, but the restore WILL FAIL if other users are connected to the database, even if your restore specifies an UNDO file. I don't know how people work around this. Probably you'd run the restore jobs at night when people are out of the office and settle for a decision server with day-old data on it.
  • I have scheduler currently setup to rerun the last job as many times possible until success. I have yet to have an issue with a user telling me he is blocked during a restore, but wanted to verify this wasn't a possibility.
  • peteypetey Posts: 2,358 New member
    A transaction log restore using SQL Backup imposes the same requirements as a transaction log restore using SQL Server, and one of them is that no other user is currently using the database being restored.

    If the database is in use, the restore cannot be performed. Since you already have a job to rerun the restore until it succeeds, the restore will eventually be performed.

    While the restore is running, any user trying to connect will either

    - wait until the connection timeout value elapse, and receive a connection failure message
    - wait a short while while the restore runs, which should be quick if the files aren't large

    Do you already have a job to forcibly disconnect all users before the restore runs?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I do not have a script to drop all users before restore as I feel the restore itself should run at a lower precedence than the users queries and speed. ie a users query should run smooth with no interruption and no speed loss even if a log restore starts. If it fails it shouldn't move the file to the processed folder but continue residing in the current folder until next execution.
  • You can use this script to kill all the users in a given database prior to executing a log restore.

    CREATE PROCEDURE sp_Kill_All_Processes
    @pDbName varchar (100)=NULL,
    /*database where we will kill processes. If NULL-we will attempt to kill processes in all DBs*/
    @pUserName varchar (100)=NULL
    /*user in a GIVEN database or in all databases where such a user name exists, whose processes we are going to kill. If NULL - kill all processes. */

    /*Purpose: Kills all processes in a given database and/or belonging to a specified user.
    If no parameters supplied it will attempt to kill all user processes on the server.
    Server: all
    Database: DBAservice
    Created: Igor Raytsin,Yul Wasserman 2000-10-13
    Last modified: Yul Wasserman 2002-02-08
    */
    AS
    SET NOCOUNT ON

    DECLARE @p_id smallint
    DECLARE @dbid smallint
    DECLARE @dbname varchar(100)
    DECLARE @exec_str varchar (255)
    DECLARE @error_str varchar (255)

    IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases where name=ltrim(rtrim(@pDbName)) or

    @pDbName is NULL)
    BEGIN
    Set @error_str='No database '+ltrim(rtrim(@pDbName)) +' found.'
    Raiserror(@error_str, 16,1)
    RETURN-1
    END
    Create Table ##DbUsers(dbid smallint,uid smallint)

    If @pUserName is not null
    BEGIN
    --Search for a user in all databases or a given one
    DECLARE curDbUsers CURSOR FOR
    SELECT dbid,name FROM master.dbo.sysdatabases where name=ltrim(rtrim(@pDbName)) or

    @pDbName is NULL
    OPEN curDbUsers
    FETCH NEXT FROM curDbUsers INTO @dbid,@dbname
    @FETCH_STATUS = 0
    BEGIN
    SELECT @exec_str='Set quoted_identifier off
    INSERT ##DbUsers SELECT '+cast(@dbid as char)+', uid FROM

    '+@dbname+'.dbo.sysusers
    WHERE name="'+ltrim(rtrim(@pUserName))+'"'
    EXEC (@exec_str)
    FETCH NEXT FROM curDbUsers INTO @dbid,@dbname
    END
    CLOSE curDbUsers
    DEALLOCATE curDbUsers
    If not exists(Select * from ##DbUsers)
    BEGIN
    Set @error_str='No user '+ltrim(rtrim(@pUserName)) +' found.'
    DROP TABLE ##DbUsers
    Raiserror(@error_str, 16,1)
    RETURN-1
    END
    END

    ELSE --IF @pUserName is null
    BEGIN
    INSERT ##DbUsers SELECT ISNULL(db_id(ltrim(rtrim(@pDbName))),-911),-911
    END

    --select * from ##dbUsers

    DECLARE curAllProc CURSOR FOR
    SELECT spid,sp.dbid FROM master.dbo.sysprocesses sp
    INNER JOIN ##DbUsers t ON (sp.dbid = t.dbid or t.dbid=-911) and (sp.uid=t.uid or t.uid=-911)
    OPEN curAllProc
    FETCH NEXT FROM curAllProc INTO @p_id, @dbid

    @FETCH_STATUS = 0
    BEGIN
    SELECT @exec_str = 'KILL '+ Convert(varchar,@p_id)+ ' checkpoint'
    SELECT @error_str = 'Attempting to kill process '+Convert(varchar,@p_id)+' in database

    '+db_name(@dbid)
    RAISERROR (@error_str,10,1)with log
    EXEC (@exec_str)
    FETCH NEXT FROM curAllProc INTO @p_id, @dbid
    END
    CLOSE curAllProc
    DEALLOCATE curAllProc
    DROP TABLE ##DbUsers
    SET NOCOUNT OFF

    GO
Sign In or Register to comment.