Find connections to a database

Often DBAs need to delete old databases. As soon as I receive a list of databases that can be deleted the first thing I would check is if there are any active connections to the databases and know the spids of the active connections and notify the appropriate user about the database. Active connections will not let us delete a database quickly. They can be identified by running queries against the sql server system table sys.sysprocesses. Below is one such query that will help us identify active connections to a database:


SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('Databasename')

If you identify any active connections and get confirmation to kill those spid’s then just execute:


kill <spid>

After killing the appropriate spid try deleting the database and it will get deleted quickly.

Hope this helps!

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , , , . Bookmark the permalink.

6 Responses to Find connections to a database

  1. sqluser says:

    You definitely covered this subject well. Are there other choices that i’ll must examine out?

  2. testuser says:

    You wouldn’t believe it but I’ve wasted all day digging for some posts about this. You will be a lifesaver, it was an very good read and has helped me out to no end. Cheers!

  3. user1 says:

    Hi buddy, your blog’s design is easy and clean and i like it. Your blog content articles are superb. Please preserve them coming. Greets!!!

  4. sqldba says:

    Hey admin, extremely informative blog post! Pleasee continue this awesome work..

  5. sqldude says:

    Searching for this for some time now – i guess luck is far more advanced than look for engines 🙂

  6. MoonLightDBA says:

    Thanks for your comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s