Connections

So I found myself needing to restore a production database in the middle of a work day this last Thursday.  I’ll save the details of why for a future blog post, because right now I’m still frustrated from the entire day and reasons leading up to the restore.  So we sent out an email asking everyone in the system to log completely out of their View session and to hang by while I kicked off the restore. So I log into a couple of different servers and I stop all the services that I know keep a connection open to the database in question.  I then went to restore and it failed!  The failure message was telling me that SQL could not get exclusive access to the database to perform the restore.

 

Exclusive_Access
Exclusive_Access

Well this sucks was my first thought.  I tried this process about 2 more times with the same results.  I even logged out of the SQL server and back in thinking that would clear my issue.  It would not!  So now I need to see who has a connection open to the database.  Now in some instances you can run exec sp_who or exec sp_who2 but the one thing that does not tell me is the machine name.  If there is a service I forgot about I need to know what box that service is running on.  So this script will tell you that information.

 

SELECT
DB_Name(dbid) AS Database_Name
, loginame AS Users_Name
, hostname AS Machine_ID
FROM sys.sysprocesses

If you happen to know which dbid is the database in question you could narrow the results down a bit further by adding in a WHERE clause at the end with the dbid of your datbase.  It would look like

SELECT
DB_Name(dbid) AS Database_Name
, loginame AS Users_Name
, hostname AS Machine_ID
FROM sys.sysprocesses
WHERE dbid='1'

 

Add a Comment

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