Getting out of single user mode

Sometimes, brute force is the best way to get something done.

Imagine a busy production server with about thirty databases. Somehow, during an administrative task, one of the databases ended up in single user mode. The application using the database is multithreaded and normally opens and closes multiple connections to the database at a high rate, so the only available connection to the database was constantly in use by sessions with different SPIDs. All we had to do to fix this was put the database back into multi user mode, but whenever we tried to execute ALTER DATABASE AppDB SET MULTI_USER, we would always get a deadlock between the ALTER DATABASE session and the application’s session already exclusively using the database. Invariably, the ALTER DATABASE session was chosen as deadlock victim.

We couldn’t disable the login used by the application, because the same login was used to access all databases on the server. We also couldn’t kill the session using the database because the SPID was changing randomly all the time, and we couldn’t risk killing the wrong process on a production server. Then I thought I had the solution: we would open a transaction, make data modifications to generate a lot of transaction log, and then run ALTER DATABASE AppDB SET MULTI_USER in the same transaction. The idea was to have the other session selected as the deadlock victim, since SQL Server generally resolves deadlocks by killing the session with the least amount of generated transaction log. This would let the ALTER DATABASE command complete successfully. I thought this was an elegant way to resolve this, before recalling that ALTER DATABASE cannot be executed in a transaction.

The solution that actually worked was much simpler and rather straightforward. We executed this from SSMS:

USE AppDB;

GO 1000

The number after the GO batch separator tells SSMS to execute the preceding batch that many times. After getting about 700 error messages saying that the database is in single user mode and can only be used by one user at a time, the USE command succeeded, we got the only available session to ourselves, and put the database back into multi user mode.

Leave a Reply

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