Archive

Posts Tagged ‘sp_who2’

SQL Server – Kill all sessions using database

25.11.2013 3 comments

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

Check currently blocked processes – “sp_who_blocked !”

21.04.2011 No comments

We use the undocumented stored procedure sp_who2 to check for blocked process on the server. The output returned by this stored procedure returns all processes on the server.

image

now, to find the processes which are actually blocked, you need to go through the result set, which will be almost > 50 on a normal DB server (during off-peak hours).

What we need most of the time is “Are there any processes which are currently blocked?”. To achieve this we can add a condition in sp_who2 which will restrict the result set to blocked processes:


and
blocked <> ''0''


You need to add this condition on line 246 of existing sp_who2 definition:

             spid >= ' + @charspidlow  + '
      and    spid <= ' + @charspidhigh + '
    and blocked <> ''0''
    — (Seems always auto sorted.)   order by [BlkBy] Desc

after retrieving the definition, create a new stored procedure once you add the required condition.

The output of the new stored procedure will be only thr currently blocked processes (if any):

image 

Hope This Helps!

Vishal