Taking SQL Server Database Offline Hangs

by Administrator 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.

Tags: ,

SQL

Comments (1) -

Dinesh Vishe
Dinesh Vishe India
1/14/2013 12:07:46 PM #


i check all process but no process shown in sysprocesses and Sp_who/sp_who2.
I had check database id from.
select * from sys.databases
then
select * from sys.sysprocesses WHERE dbid = 84

SELECT * FROM sys.dm_exec_requests WHERE database_id = 84

select * from sys.dm_tran_locks where resource_database_id=84
After that you will get procees id.

then kill corresponding session and check database status.

About the author

for comments and suggestions contact:

 

Month List