22. September 2011 02:50
... happened to me yesterday and was really annoying. I wanted to take a TFS database offline and then bring it back on to get rid of rogue locks and found out that taking it offline took more than 10 minutes without success. The database was still shown as being online, but I was not able to connect to it.
Showed the following error:
Msg 952, Level 16, State 1, Line 1
Database 'xxx' is in transition. Try the statement later.
After some googling I found this post on MSDN.
Taking a database offline has one nuisance pretty much to what happens when you restore a backup. Open connections/running commands prevent the thing from happening. While in the newer version of SQL Management Studio you can drop existing connections when restoring a backup, there is no frontend support for doing such a thing when taking databases offline. This is extremely annyoing, because sometimes the reason why you do take a db offline is an urgency anyway.
To get everything working again, I followed the script at the bottom of the post:
lets you find the processes that execute actions on the database you want to drop. Then you can use the KILL command to kill the processes preventing you from taking your database offline. Right after killing the process, perform your action again:
Please note that this is not a clear way to do it. You will rollback transactions and might lose data depending on who was connected to your db with what application.