Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I use the stored proc sp_dboption2 (http://vyaskn.tripod.com/code/sp_dboption2.txt) to close all connections to a database so I can restore it as part of refreshing our test server (SQL 2005). It has been working flawlessly for a couple of years but in the last couple of weeks it has failed both times I've used it. The output looks like this:

SPID 55: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds. [SQLSTATE 01000]
SPID 56: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds. [SQLSTATE 01000]
SPID 55: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds. [SQLSTATE 01000]
Msg 6106, Sev 16, State 1, Line 1 : Process ID 56 is not an active process ID. [SQLSTATE 42000]

The procedure basically loops through the spids in sysprocesses and KILLs them until there are none left. From the above it doesn't look like there's a problem KILLing the same spid twice. It looks more like spid 56 started a rollback on the first KILL, then was still in sysprocesses so a second KILL was issued but in between the rollback finished and the spid disappeared, causing the second KILL to fail. I can't think of anything that has changed recently that should have affected this - no SQL updates installed etc.

For now I'm going to try wrapping the KILL in a try...catch and ignoring error 6106. Has anyone else had this problem? Do you know what has happened to break this? How did you fix it? Is there a more modern way to kick all users out for a restore?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Try this

ALTER DATABASE [DBName]

SET RESTRICTED_USER

--SET MULTI_USER

WITH ROLLBACK IMMEDIATE

It will put the database in Restricted User mode which means only members of the db_owner, dbcreator, or sysadmin roles can use the database.

Also, the "WITH ROLLBACK IMMEDIATE" clause will cause all connections to the database to be terminated prior to putting it in restricted user mode.

This should work fine with in SQL 2005 and SQL 2008. You can then do the needful and run the command with the "MULTI_USER" clause to make it available again.

More info can be found at: http://msdn.microsoft.com/en-us/library/bb522682.aspx

Hope this helps.

share|improve this answer
    
Doh! Thanks, Chirag, I just hadn't put 2 and 2 together. –  David Wimbush Oct 27 '09 at 13:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.