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;
