30
Mar

How to find (and kill) those SQL processes using a database

If you ever struggle to backup, process or use a database only to find that somebody (somewhere) is using it, then this collection of SQL commands will help.

They will list the processes and will then allow you to either shout at the people, or if needed, kill the processes.  If you KILL them, SQL will take care of making sure its a clean kill – in that any updates will be rolled back, and the server left in a tidy state.

– General, list all processes connected to SQL – useful if you want to shut the whole server down

USE master;

SELECT spid , nt_username,login_time

datediff(s,login_time,GETDATE()) as how_long_on

FROM sysprocesses order by login_time;

– For a specific user (just replace the JAFFA below with part of the UPPERCASE version of the user you need to find

USE master;

SELECT spid , nt_username,login_time

datediff(s,login_time,GETDATE()) as how_long_on

FROM sysprocesses

where upper(nt_username) like ‘%JAFFA%’ order by login_time;

– Connected to a database – shows all users connected to a specific database (replace MyDatabaseName with your database)

USE master;

select DB_NAME(dbid),* from sysprocesses

where DB_NAME(dbid) = ‘MyDatabaseName’

order by login_time

– And to kill it

EXEC (‘kill xx’); — where xx is the spid number

2 Trackbacks / Pingbacks for this entry

Ruben, July 4th, 2014 on 10:22 pm

richard, July 28th, 2014 on 1:00 am