I was recently attempting to detach/drop a database from SQL Server Management Studio when the detach suddenly failed.
Of course, this suddenly placed the database in Single User Mode. When I subsequently tried to connect to the database again, I could not access it!
If I tried to open a query and run the following command:
USE master
ALTER DATABASE MyDatabase Set MULTI_USER
The query inevitably failed because the database was already locked and in use by an existing process.
Therefore, in order to figure out who was holding the locks on the database, I had to run the following command:
sp_who2
Once I got a list of all the sessions and their associated databases, I had to run the following command:
KILL <session id>
Now, once I had killed all of the sessions that were attached to the database, I could once again run the following command:
USE master
ALTER DATABASE MyDatabase Set MULTI_USER
Now, the database was back in Multi User mode again and I could proceed with detaching/dropping the database directly from SQL Server Management Studio!
Of course, this suddenly placed the database in Single User Mode. When I subsequently tried to connect to the database again, I could not access it!
If I tried to open a query and run the following command:
USE master
ALTER DATABASE MyDatabase Set MULTI_USER
The query inevitably failed because the database was already locked and in use by an existing process.
Therefore, in order to figure out who was holding the locks on the database, I had to run the following command:
sp_who2
Once I got a list of all the sessions and their associated databases, I had to run the following command:
KILL <session id>
Now, once I had killed all of the sessions that were attached to the database, I could once again run the following command:
USE master
ALTER DATABASE MyDatabase Set MULTI_USER
Now, the database was back in Multi User mode again and I could proceed with detaching/dropping the database directly from SQL Server Management Studio!
No comments:
Post a Comment