Killing Connections to MSSQL Database

Every once in a while when I’m trying to restore a backup for a database on Microsoft SQL Server Management Studio (SSMS), I get something like the following error:

Title: Microsoft SQL Server Management Studio

Restore failed for Server ‘[server name]’.  (Microsoft.SqlServer.SmoExtended)

Additional information:

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)

From what I understand, this means that some process is holding on to a connection to the database and, therefore, I can’t do anything drastic. Because my job deals with the web, I usually find that the culprit holding on to the database connection is IIS. Typically, if I stop the application pool of the site that’s using the database, or the site itself, I’m able to complete my restore. But sometimes I can’t track it down or I’m just too lazy to do anything about it.  So I wrote a little script that kills the connections to a database by putting the database in single user mode and then back into multi-user mode. Here it is for reference:

DECLARE @DB VARCHAR(255)
SET @DB = 'databaseName'

DECLARE @SINGLE_USER VARCHAR(MAX)
DECLARE @MULTI_USER VARCHAR(MAX)

SET @SINGLE_USER = 'ALTER DATABASE {DB} SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
SET @MULTI_USER = 'ALTER DATABASE {DB} SET MULTI_USER WITH ROLLBACK IMMEDIATE'

DECLARE @SCRIPT VARCHAR(MAX)

USE MASTER

SET @SCRIPT = REPLACE(@SINGLE_USER, '{DB}', @DB)
EXECUTE(@SCRIPT)

SET @SCRIPT = REPLACE(@MULTI_USER, '{DB}', @DB)
EXECUTE(@SCRIPT)

I found this concept on an MSDN forum post and I borrowed the idea of using a variable for the the database name from stackoverflow.

Advertisements

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s