Search This Blog

Friday, 18 May 2012

SQL Server session kill

So, I had an issue today where I wanted to boot out all the user sessions from a specific database and wanted to do so in script.  I'm sure there is a more elegant way to do this, but this works OK:



DECLARE @sessions TABLE(
      [spid] FLOAT,
      [ecid] FLOAT,
      [status] VARCHAR(1000),
      [loginame] VARCHAR(1000),
      [hostname] VARCHAR(1000),
      [blk] FLOAT,
      [dbname] VARCHAR(1000),
      [cmd] VARCHAR(1000),
      [request_id] FLOAT
)

SET NOCOUNT ON
INSERT INTO @sessions
EXEC sp_who

DECLARE @spid int
DECLARE db_cursor CURSOR FOR 
SELECT spid FROM @sessions WHERE dbname = 'xxxxxx' and spid <> @@SPID

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @spid  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      DECLARE @killStatement varchar(100)
      SELECT @killStatement = 'kill '+CONVERT(VARCHAR, @spid)
      EXEC (@killStatement)

      FETCH NEXT FROM db_cursor INTO @spid  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

No comments:

Post a Comment