Finding and Killing Sessions in Microsoft SQL Server
The first step in killing a session in a MS SQL Server database is to find the session to kill. Please be sure to connect to SQL Server as a user that has the privileges necessary to run queries to find sessions and execute commands to kill sessions. The following query will list running sessions in a MS SQL Server database.
SELECT pr.spid, master.dbo.sysdatabases.name as dbname, pr.loginame, pr.login_time, pr.last_batch, pr.ecid, pr.open_tran, pr.status as procStatus, pr.hostname, pr.program_name, pr.hostprocess, pr.cmd, pr.nt_domain, pr.nt_username, pr.net_address, pr.net_library, block.spid as blocking FROM master.dbo.sysprocesses pr LEFT JOIN master.dbo.sysdatabases ON pr.dbid = master.dbo.sysdatabases.dbid LEFT JOIN master.dbo.sysusers ON pr.uid = master.dbo.sysusers.uid LEFT JOIN master.dbo.sysprocesses block ON pr.spid = block.blocked;
The value from the result of the above query that is needed to kill the session is the spid. To kill the session, execute the following series of commands using the spid of the session to kill from the above query results. The below assumes the spid is 52.
set implicit_transactions on; KILL 52; IF @@TRANCOUNT > 0 ROLLBACK TRAN; IF @@TRANCOUNT > 0 COMMIT TRAN set implicit_transactions off;