Monday, 8 September 2014

Kill all of the Processes Accessing a Microsoft SQL Server Database

I had an issue today trying to take a Microsoft SQL Server database offline (for a SharePoint Search Service). It was taking a very long time, and I was getting impatient!

Because there were still connections to the database, the command to take it offline line wouldn't complete. What a pain!

Time to kill all of the processes with open connections to the database. I'd previously taken the SharePoint Search Service offline, so I wasn't worried about forcefully closing connections.

I used sp_who2 to return a list of processes accessing the Search Service database (called SvcApp_Search_Admin). I saved this to a temporary table (to filter and group the results), and then used a cursor to kill all the processes associated with the database.


DROP TABLE #sp_who2
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
Select * from #sp_who2 where DBName = 'SvcApp_Search_Admin'
Select SPID from #sp_who2 where DBName = 'SvcApp_Search_Admin' Group By SPID
DECLARE @spid INT
DECLARE @dbname NVARCHAR(1000)
DECLARE c CURSOR FOR Select distinct(SPID) as Int,DBName from #sp_who2 where DBName = 'SvcApp_Search_Admin'
OPEN c
FETCH NEXT FROM c INTO @spid,@dbname
WHILE @@FETCH_STATUS = 0
BEGIN  
    PRINT ' Killing Connection to: ' + @dbname + ' with ID ' + CAST(@spid as nvarchar)
    declare @sqls nvarchar(50)=''
    SELECT @sqls = 'kill ' + CAST(@spid as varchar(20))+';'
    EXEC(@sqls)
    FETCH NEXT FROM c INTO @spid,@dbname
END
CLOSE c
DEALLOCATE c


I borrowed the idea of saving the results from sp_who2 into a temporary table from SQLMatters, here: http://www.sqlmatters.com/Articles/sp_who2%20-%20filtering%20and%20sorting%20the%20results.aspx