Answered by:
Error 952 Database is in Transition

Question
-
One of our SQL 2005 database started to give us "Database is in
transition...Error 952". We were trying to Take the DB offline when this
problem occurred. Restarting SQL service did not help us. We were unable to do anything with this database as we were unable to obtain any locks.
What resolved the problem? Re-starting manamgement studio on client machine.
Go Figure!- Changed type Kalman Toth Wednesday, February 19, 2014 1:13 PM
Wednesday, January 18, 2006 7:44 PM
Answers
-
I simply stop-start SQL Server Service using Mgmt Studio and it worked for me!
-P
- Marked as answer by Kalman Toth Wednesday, February 19, 2014 1:14 PM
Tuesday, February 20, 2007 9:03 PM -
Hi,
sorry to add some info years later, but...
I had to take one of our databases offline. When I was about to bring it online, and saw that the 'database is in transition', I found this post.
However, it was enough for me to disconnect from the server in Object Explorer.
(Strange thing was that it disconnected from all open connections... But at least it worked after this, _without_ restarting SSMS.)
Kind regards,
Zoli- Marked as answer by Kalman Toth Wednesday, February 19, 2014 1:15 PM
Thursday, July 16, 2009 11:48 AM
All replies
-
SJ100,
Thanks for posting this. It saved my customer at 5am this morning...
I will be opening a case with MS to see if this is a known bug, or "by design" behavior.
Kevin3NF
http://kevin3nf.blogspot.com
Wednesday, December 6, 2006 7:32 PM -
Thanks mate!Tuesday, December 26, 2006 10:20 PM
-
Thanks folks! It saved my day.Wednesday, February 7, 2007 1:53 PM
-
I simply stop-start SQL Server Service using Mgmt Studio and it worked for me!
-P
- Marked as answer by Kalman Toth Wednesday, February 19, 2014 1:14 PM
Tuesday, February 20, 2007 9:03 PM -
one more time, thanks a lotWednesday, February 28, 2007 9:53 PM
-
It worked for me too, closing a forgotten QueryAnalayzer solve the problem on a sql2000 database.
Thanks a lot again.
Friday, March 23, 2007 2:44 PM -
Thank YOU for this post. We could not turn of the SQL Service as have more than one db active. This is definitely a bug...you try to take a db offline but it does not go, just stays inaccessible and in the "transition" state. Closing Management Studio released whatever locks were still alive and we could reconnect fine.
Thank you again.
Friday, March 30, 2007 3:03 PM -
I agree on the other comments: it worked out very fine with no server stop.
Thanks for your post.Thursday, July 19, 2007 8:11 AM -
I got DB in transition message too, and restarting Management Studio resolved the issue. Thanks for the tip !
Thursday, October 11, 2007 10:15 AM -
Yes, practical approach, strange error with SQL-2005 Management Studio.Friday, February 15, 2008 5:01 AM
-
Yes.. it work on my problem tooTuesday, April 8, 2008 2:26 PM
-
It's 4am and I was about to start panicing
killing management studio did the trick.
ThanksThursday, June 5, 2008 3:19 AM -
SJ, may God help you win the big pot at Loto!
It was the only reference regarding this error that I found while browsing forums.
My problem was that I couldn't connect to some database because of the same problem:
"Msg 952, Level 16, State 1, Line 1
Database 'xxx' is in transition. Try the statement later."
I looked in sys.databases for the state of this db, and it was Online.
One of my colleagues tried to take that db offline and the Management Studio was waiting...and the operation didn't finish.
Looking in sysprocesses I found that there was a connection that had "waitresource" as "db: xxxID" (the id of the db with problem) and with a locking process (spid yyy). The strange thing is that the process with spid = yyy (the blocking process) was NOT shown in Sysprocesses system table. Killing that process worked though, and the take offline operation finished successfully.Thursday, June 26, 2008 8:29 AM -
Maybe I'm just a little thick but how are you killing the program? I went into task manager and killed sqlwb.exe and restarted it and still have the problem.
What am I doing wrong?
Please help...
***EDIT***
I found that if I run this query:
USE masterGOALTER DATABASE Your_Database_Name
SET OFFLINE WITH ROLLBACK IMMEDIATE
I can take the database offline without problems.
Thursday, June 26, 2008 1:32 PM -
Yes.It is working Good.Just close Management Studio on client Machine that can enough
Thanks
sat
BI Architect & DB Designer
Wednesday, July 9, 2008 10:06 AM -
Huge thanks for this info! Helped me out greatly.
And shame on MS for not providing it "officially".
Friday, July 25, 2008 6:36 AM -
Another way out of this is to run the following query select * from sys.dm_exec_requests.
Look for the offending statement in the results, most likely has an alter database statment in the command column.. Obtain the SPID and execute the kill statement
Wednesday, August 20, 2008 4:08 PM -
simple... but also very efficient.
Tks
Wednesday, September 17, 2008 2:06 PM -
I saw this in our Dev. environment after a Developer had tried to OFFLINE a database using Management Studio.
Using sp_who2 it was easy to see what was going:
1 blocked process: the Command was ALTER DATABASE, and the ProgramName was Management Studio; this was blocked by an existing database connection (OFFLINE needs exclusive access).
I terminated the Developers connection which was in a SUSPENDED state:
KILL <spid>
database access was immediately restored.
I prefer to have more control than Managment Studio affords, especially when doing detach/attach, restore, or otherwise altering databases. In this case:
...
ALTER DATABASE <db_name> SET ONLINE
Cheers,
Chris.
Tuesday, October 21, 2008 12:44 PM -
Good lord, what a bug! Thanks all for this thread.Friday, January 9, 2009 10:41 PM
-
Thanks for posting this and all of the follow up!
We had the same problem this morning and thanks to this thread we were able to track the process down in 'select * from dbo.sysprocesses where dbid = xxx' and find our offending user who had been logged in for over 16 hours.
What an oddball bug to run into.Thursday, March 12, 2009 1:00 PM -
Thanks for the posting... saved me valuable hours.
Friday, May 1, 2009 3:18 AM -
Hi,
sorry to add some info years later, but...
I had to take one of our databases offline. When I was about to bring it online, and saw that the 'database is in transition', I found this post.
However, it was enough for me to disconnect from the server in Object Explorer.
(Strange thing was that it disconnected from all open connections... But at least it worked after this, _without_ restarting SSMS.)
Kind regards,
Zoli- Marked as answer by Kalman Toth Wednesday, February 19, 2014 1:15 PM
Thursday, July 16, 2009 11:48 AM -
Oh, my.
Thank you, man!Tuesday, August 11, 2009 11:01 AM -
Yay - thanks for this post! And next time I will explore some more using sp_who2 instead of sp_who.
Thanks,
Rob
http://www.simplesql.netWednesday, October 7, 2009 6:00 PM -
Boy oh boy, what a little bug!I was just running an Alter DB to set it Offline but it got stuck and sp_who2 or querying sysprocesses won't work!Just followed the suggestion on this thread and simply RESTARTED Management Studio and it worked!Thanks for the tip!Thursday, October 15, 2009 3:28 AM
-
You are a super star....!!! Worked like a miracle to me!Monday, December 7, 2009 12:20 PM
-
Thank you very much. This tip really helps me !!! :)Thursday, December 10, 2009 12:51 PM
-
Hi,
I have did the following steps and resolved the issue
alter database dbname set offline
alter database dbname set single_user with rollback immediate
alter database dbname set offline
Then refresh the db
alter database dbname set multi_user with rollback immediate
Regards,
Arun karthikeyan.MFriday, December 18, 2009 1:55 PM -
Stoping and Re-starting manmgement studio on client machine did not work for me
But
After I logged off and logged back in to the computer with a different account and Launched management studio
that worked.Wednesday, January 27, 2010 2:24 AM -
Another way out of this is to run the following query select * from sys.dm_exec_requests.
Look for the offending statement in the results, most likely has an alter database statment in the command column.. Obtain the SPID and execute the kill statement
Wednesday, February 17, 2010 1:58 PM -
Saved my bacon too. If it helps, I had been trying to take a database offline to attach a newer copy but didn't have rights. Left SSMS open overnight and then this problem started happening.
Very helpful. Thanks!
Thursday, July 8, 2010 4:26 PM -
Four years since the original post, and the kudos just keep on comin'... Thanks for this, easiest fix I've had all week!Friday, July 9, 2010 5:06 PM
-
Thanks was a painful memory from the past, Enterprise Manager had some of the same issues.
Thursday, August 5, 2010 4:35 PM -
thanks guys this whole page helps me a lot.
--get the dbid which is in Transition mode, check the status is 0 or not.
select * from sys.databases where name = <your db name>
--if status is not zero reset db status
exec sp_resetstatus <your db name>
--look for blocking spid or IF WaitRsource column has that DBID
select * from sys.sysprocesses
--Look for waitresource column if that dbid is blocking
select * from sys.dm_exec_requests
--kill the spid or sessionid which is Bloking, OR try to kill session/process which is waiting on resurces.
kill <blocking prosess/session ID>
-- immediatly execute put db offline with rolback
ALTER DATABASE <your db name> SET OFFLINE WITH ROLLBACK IMMEDIATE
Thanks.
Friday, August 27, 2010 3:59 PM -
I could not believe it...but It worked for me!
Thanks man!
PaulinoWednesday, February 9, 2011 9:58 PM -
5 years and still going strong. Cheers, I too was beginning to panic. I'm off to grab a backup of my dev database!Tuesday, March 1, 2011 3:05 PM
-
Thanks a lot. This problem came immediately after I was trying to rename the database. Restarting the Client SSMS Studio worked.
The solution has saved hours of my time.
Tuesday, March 29, 2011 7:11 AM -
Thanks a lot. That solve lot of frustration for meFriday, April 8, 2011 1:29 PM
-
Thanks you for this help.Monday, September 12, 2011 7:09 PM
-
5 years later.
Still happening.
Thanks for the tip
Wednesday, September 21, 2011 7:42 AM -
Yep. 5 years later and it's still happening and the soln is good. The problem we have are SQL 2008 Enterprise edition so bug still exists. Thanks for saving us some valueable time at 4 AM!Sunday, September 25, 2011 8:51 AM
-
This post was really helpfull.
Even after 5 years its worthy.
Thanks a lot for participating in improving this post.
Monday, October 10, 2011 6:25 PM -
ThanksFriday, October 14, 2011 10:10 AM
-
I made the DB to single user mode and then made to multi user... it worked for me...Friday, November 11, 2011 7:08 PM
-
Thanks!
I got the error when I canceled a "take offline" task.
I tried starting management studio on the server and got the same error.
First I thought about restarting management studio but then i thought it was to far out of the box and instead I googled it :)
Thanks again!
JimiSweden
- Edited by JimiSweden Monday, December 19, 2011 1:09 PM
Monday, December 19, 2011 1:09 PM -
Hi there,
Thankssssss ... Restarting the MSSQL solved my issue with this error.
Monday, January 2, 2012 8:15 AM -
Check following link
http://sqlsolace.blogspot.com/2011/06/sql-2005-database-is-in-transitionerror.html
It says that
The solution. RESTART MANAGEMENT STUDIO.
Monday, January 2, 2012 5:05 PM -
RESTART MANAGEMENT STUDIO, and that's it!Tuesday, January 3, 2012 6:26 PM
-
Thanks! Yes restarting SSMS fixe the issue, the DB returns available for access
Jeremy
Wednesday, February 1, 2012 12:44 PM -
Thanks for posting..this helped me as well.Friday, March 16, 2012 8:19 PM
-
Thanks for the answer...It worked absolutely fine...:))))Thursday, March 29, 2012 11:09 AM
-
worked like a charm! =)Tuesday, May 8, 2012 3:18 AM
-
Excellent! Thanks!Tuesday, July 3, 2012 8:39 PM
-
A few words... Your a star! :)Wednesday, August 8, 2012 10:31 AM
-
Damn. Just happened to me on 2008R2 ...4276
I guess this is not something MS can be bothered fixing.
What a surprise.
Thanks for the tip, restarting SSMS worked for me too.
Tuesday, August 27, 2013 2:08 PM -
Thanks mate... Can you imagine your this little post is still helping someone after a period of more than seven and half years! (Y)Monday, October 21, 2013 11:09 AM
-
Closing and restarting Management Studio did the trick. Thanks a lot.Wednesday, February 19, 2014 1:08 PM
-
Bizzare... I am stuck here. I tried restarting SSMS but ain't working. Could someone please assist.
Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.
Thursday, April 3, 2014 12:26 PM -
Thanks, stop and start SQL server service worked.
Thank you
Monday, May 23, 2016 1:44 AM -
I tried disconnecting from the instance but it did not work for me. What I did is to detach and re-attach the database and it worked perfectly.
I did not need to restart the service which will make all other databases on the instance unavailable.
- Edited by Woeka Monday, September 18, 2017 2:39 PM
Monday, September 18, 2017 2:01 PM