You want to take a database off line, delete it or just do something else that requires that no one else is using it.

This can be accomplished by putting the database into single user mode.

However, if that doesn’t work, you will need to find the PID of the user using the database then kill that. Be careful when you are doing this. It stands to reason that if something has a lock / an open connection, it has this for a reason. You could very likely cause data corruption by following these instructions.

1. Open SQL Server Management Studio
2. Log in with your domain credential. Try not to use SA. Please. SA is for emergencies.
3. Open a query window. Press control n on the keyboard, or click the new button on the top left of the screen. There are a few things you should know about the query editor window.
   Commands should usually end with a “;”. Although this isn’t strictly necessary with MS SQL. But it’s a good habit.
   You can run / execute all lines on the screen by pressing F5.
   You can run / execute one specific line or several lines by highlighting those lines then press F5.
4. Type SP_WHO2
   This shows you all the open connections. Look down through this list to see if there are any connections open to that database. Keep a note of the PID for each connection. Hopefully there will only be one.
5. Now you’re ready to try to set the database to single user mode:
   ALTER DATABASE <DatabaseName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
   Use that command. Remember, highlight the line then press F5 when you’re ready. Of course, change the database name to the database you are working on.
6. Now, before you go any further, bring the database out of single user mode. It’s served it’s purpose.
   ALTER DATABASE <DatabaseName> SET MULTI_USER;
7. Press F5 of course.
8. Watch the output window on the bottom to see if it’s worked.
9. If you get a message saying the command can’t be completed because of locking, you might need to kill that user’s connection using a more blunt tool. Enter the Kill command. The error you might get is:
   ALTER DATABASE failed because a lock could not be placed on database
10. Remember the PID that you kept in a safe place earlier? You want to use that now: Replace XXX with the PID.
    Kill XXX;
11. That’s all there is too it. Be careful. with this kind of work, there’s a risk of corruption. Try to take backups.

I have a lot of updates to write here. It has been a crazy and wonderful few months. But this quick technical guide will get me back into the blogging frame of mind.