SQL error 3101 (database is in use) during log shipping

Category: Troubleshooting & error messages
Date: 15 Jul 2009
Product: SQL Backup
Versions: 3,4,5
Fix Version: 6

In a functioning log shipping scenario, the standby database which is restoring the log backups may report the following error during restoral:

Msg 3101, Level 16, State 2, Server MYSERVER, Line 1
Exclusive access could not be obtained because the database is in use.

The cause is a limitation in SQL Server that makes it impossible to restore a database while there is an open connection to it. This would happen, for example, if the log shipping was set up with an UNDO file, making the standby server a read-only decision server. The other type of log shipping scenario does not allow any users to connect to the standby database, so this error would not occur.

There are a few options for avoiding this message. One is to upgrade previous versions of SQL Backup to version 6, which includes an option to automatically disconnect any users from the database before restoring.

You may also try changing the schedule on your restore job on the standby so that it restores all log files outside of business hours, when nobody would normally be connected.

Another option is to break the log shipping and configure it without the UNDO option so nobody could possibly connect to the database during the restore operation.

The final option is to write a script that forcibly ejects any process that tries to connect to the database before running the restore job.
To do this, you could edit the restore job using Management Studio or Enterprise Manager, inserting a job step before the restore step. This step would be a T-SQL step that runs the following stored procedure, which will kill any processes currently connected to the database.

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
WHILE @@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

WHILE @@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