Monthly Archives: July 2011

Finding existing connections to the database and closing them

So like my case, you also find SQL Server not following you instructions :-). If you have any existing connections to your database, SQL Server will not let you rename or do any Alter Database operations because it cannot get an exclusive lock on the DB until all connections are dropped or closed or in worse case KILLED.

1. To find all the existing connections to your database. Do the following.

  • Go to SQL Server Management Studio
  • Open up Query editor and type ‘Exec sp_who’ or ‘Exec sp_who2’. This will display all the processes/connections that SQL server is serving at that moment. These two are the system stored procedures within the master database. Also note that these two stored procedure uses [master].[sys].[sysprocesses] system view to retrieve necessary information. For details on the processes/connections you can directly retrieve records from this view by writing SELECT statement.

Execution of stored procedure will display something like below:

2. Now, Take the value of ‘spid’ against the database (dbname). Don’t forget to check the login and hostname, just in case you’re killing the right connection. Suppose I want to kill the connection associated with ‘tempdb’ database then the spid will be 10. Also, remember you can only delete user process not the system processes. tempdb is taken here only for the sake of example.

3. Now, you’ve the spid of the process/connection you want to kill. Now go to query editor and write ‘KILL <spid>’. In our case, it should be ‘KILL 10’. This will kill the process with spid 10.

While killing the process, if you get an error saying ‘Cannot use KILL to kill your own process.’ then make sure the status of the process is not ‘runnable’ when you executed the sp_who.

If you have number of connections to kill and you don’t want to go one-by-one then you can certainly write a script to do that for you.

Tagged