Log Shipping as a reporting database.
JHammond
Posts: 6
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
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.
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?
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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