When your SQL database is stuck in single user mode

A come problem we have is when a restore of a dev database is done (usually on a weekly schedule in our environments) and for some reason it fails.

We have a lot of things working on timers that poll the database, so these end up stealing the single connection and we can access it.

So here is the script we use to kill all processes and set the database back to multi-user mode, sometimes needs a few goes to work.


USE master
GO

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('mDB')

EXEC(@kill);
GO
ALTER DATABASE [myDB] SET MULTI_USER

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s