Log Shipping as a reporting database.

JHammond

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.

Brian Donahue

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.

JHammond

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.

petey

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?

JHammond

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.

smwilliams

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