SQL Server – Kill all sessions using database

Before an existing database can be restored, there should be connections using the database in question. If the database is currently in use the RESTORE command fails with below error:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

To avoid this, we need to kill all sessions using the database. All sessions using the database can be queries using system stored procedure sp_who2 or using sys.dm_exec_sessions DMV:


SELECT   session_id
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = 'SqlAndMe'

You need to terminate each of the sessions returned individually by using KILL command.

If there are large number of sessions to kill, or you need to do this on a routine basis it gets boring to do it this way. You can *automate* this using below script, which takes database name as input, and kills all sessions connecting to it.


-- Kill all sessions using a database
-- Vishal - http://SqlAndMe.com

USE [master]
GO

DECLARE @dbName SYSNAME
DECLARE @sqlCmd VARCHAR(MAX)

SET @sqlCmd = ''
SET @dbName = 'SqlAndMe' -- Change database name here

SELECT   @sqlCmd = @sqlCmd + 'KILL ' + CAST(session_id AS VARCHAR) +
         CHAR(13)
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = @dbName

PRINT @sqlCmd

--Uncomment below line to kill
--EXEC (@sqlCmd)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. Aviv
    November 25, 2013 at 7:18 pm

    Another way to quickly kill all connected sessions is by setting the database to be in single user mode, that will rollback all open transactions.
    Here’s how to do it:

    ALTER DATABASE [DB_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    Aviv.

  2. March 18, 2014 at 8:38 am

    Hello Vishal

    I’d like to point out that your script in this post is not compatible with sql server version older than 2012. Dynamic management view “sys.dm_exec_sessions” doesn’t have ‘database_id’ column in either 2005 or 2008(R2).

    I used sys.sysprocesses to locate all the spid’s that are connected to the target DB and I’ve posted my script on my blog: http://sqlnickxu.wordpress.com/2014/03/17/t-sql-stored-procedure-to-kill-all-connections-to-a-db/

    cheers
    Nick

  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 285 other followers

%d bloggers like this: