Kill database processes

This guide was created with Microsoft SQL Server 2008 in mind, but should work for any version of SQL Server.

If you are trying to perform a task on a database and are getting a message that the database is locked, you may wish to kill some of the processes. This is a bit of a dirty method of going about this, and you need to be completely sure that no users or processes are using the database.

In order to kill the right process you need to obtain the Process ID. To get hold of this you will need to log on to the database server, open SQL Server Management Studio and connect to correct database instance, you can then either;

1. Right-click on Activity Monitor (connect to database instance, expand Management and then Activity Monitor is listed) and select View Processes. You can then View filter settings to set a filter to view only the database you are interested in (as long as Activity Monitor is installed).

or

2. Run exec sp_who2 against the database instance and manually look through to find the processes associated with the database you are interested in.

Then to kill the processes it’s a simple matter of executing KILL 12 against the database (where 12 is the Process ID obtained in the first step).

Posted in Guides Tagged with:

Leave a Reply

Your email address will not be published. Required fields are marked *

*