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:
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.
This totally worked for me. I had a database that was in single-user mode, and executing the statement to put it back into multi-user mode was continuing to fail *because* the db was in single-user mode.
Any reason why we can't kill the session for that particular database? Even if the spid is changing its going against that particular db correct?
Technically, you could kill the session, since it would be the only non-system session against that database. However, killing an application session on a production system may not be something you could just do at will, as it could possibly break the application. Even if you kill the session, with sufficiently high level of activity (as was the case in this example), another application session will immediately grab the single-mode connection, and you will be back to square one.
I had this issue where a User database was in single user mode and could not tell what spid have the connection. I used SSMS set results to Grid and then ran sp_who. This blewup and through an error because the DB was in Single User Mode. I reviewed the out out of the query just before it blewup and thankfully sp_who lists SPIDs in order! The last spid I saw was 385, so I ran the "Kill 386" and killed the next spid. Than I ran "sp_dboption DatabaseName, 'single', false". This worked. :)
Very useful method which solved our problem (the offending session could not be killed), many thanks!
Totally worked for me, Thanks