One of our production servers running SQL Server 2005 has LiteSpeed log shipping configured. It is configured to bring the database into Single user mode, apply the transaction log backups from Primary server and put the database back into Multi_User mode. Due to some configuration changes at the Primary database, the logshipping job at the secondary instance failed. This left the Secondary database in Single User mode.
In order to resolve the issue the DBA had to bring back the database into Multi_User mode and apply the transaction log backups manually. Like I discussed earlier in this post, DBA issued the ALTER DATABSE command with termination option to bring the database into Multi_User mode.
ALTER DATABASE databasename SET MULTI_USER WITH ROLLBACK IMMEDIATE
But this query returned error which read
Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database 'databasename' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed
Error message clearly stated that the database cannot be brought into Multi_User mhode since it is in Single User mode and currently one user is already connected to it. This is because a user having similar privileges as that of the one issuing the Alter Database command was already connected to the database. Hence the Rollback Immediate option will not terminate that connection.
The DBA immediate checked if he has connected to that database already. He was not using that database anywhere. To check which process was using that database, the DBA issued the following commands.
select * from sys.sysprocesses WHERE dbid = 5 SELECT * FROM sys.dm_exec_requests WHERE database_id = 5
Both these queries returned no results. Now this became more interesting, no one is using this database, still SQL Server reports that the database has an open transaction. Then the DBA decided to run sys.dm_tran_locks DMV which reports information about currently active lock manager resources on the instance.
select * from sys.dm_tran_locks where resource_database_id=5
This DMV did the trick and reported the locks currently held on that database.
The request_session_id column in the output reported that SPID 55 was holding these locks on the database. This information can also be obtained from the Activity Monitor in SSMS. SPID 55 was accessing this database through a nested query.
Now that the DBA has identified which process was active on this database, all he needed to do was to kill this process so that the database can be brought into Multi User mode. After confirming with the Application Team, this process was killed and the database was brought into Multi User mode and the transaction log backups were applied manually.