May 29, 2013

SQL Database in use: How to find and close existing database connections

This is useful in many situations (SharePoint restores, etc):

In SQL Management Studio:
1. exec sp_who2; 
  This returns a list of all current sessions.  You will normally be interested in the sessions with SPID 50 or higher.
2. kill blockingSPID
  Try to close the connection from the client side, but if that doesn't work, you can kill the session from SQL Server.

Many client applications will quickly try to restore a connection, so to block access:

1. Go to the Properties > Options of the database in question.
2. In the State section, change the Restricted Access from MULTI_USER to RESTRICTED_USER to allow only database owners to connect. 
3. Take the database offline via Tasks > Take offline

No comments:

Post a Comment