Friday, 30 July 2010

How to kill a process in SQL Server 2005 Process with ID –2

Recently, I encountered the problem where a transaction had been left open and therefore was causing issues with a specific table in the database. When looking at the SSMS in Maintenance –> Activity Monitor, the tables were being locked by Process ID –2. Normally you would kill processes by using the:

kill xx


kill –2

Unfortunately, the kill command only allows you to kill process above 0. So how do you kill it?

The other way to kill processes is to use the GUID assigned to the process, which is found if you scroll slightly to the right. Like this

Although the GUID here is all zeroes (possibly because it is an internal process locking it), this is the GUID you use. So the command would look something like:

kill '4ab0d37b-566d-44ab-8396-4d35a53f955b'

After executing it in the Query Window, you should find the locks are released, but note that since you have killed the transaction, the data changes will be rolled back.