Tuesday, June 25, 2013

Stored Procedure to kill all database sessions

I am sure a lot of us would have seen that "database in use" error while restoring database over an existing database or detaching a database. The stored proc in this post will help with this situation. You can use it to kill all the sessions in the database. It takes database name as a parameter.
 
CREATE PROCEDURE [dbo].[vspKillDBSessions]
(
@databaseName   VARCHAR(200)
)
AS

 SET NOCOUNT ON

 DECLARE @killStr NVARCHAR(4000);

 WITH killList (killStr)
 AS(
  SELECT ' KILL ' + CAST(spid AS VARCHAR(5))  + CHAR(10) FROM master..sysprocesses
  WHERE dbid = db_id(@databaseName)
  FOR XML PATH(''), TYPE
   )
 
SELECT @killStr = CAST (killStr AS NVARCHAR(4000) ) FROM killList;
EXEC sp_executesql @killStr



No comments: