locked
Error 952 Database is in Transition RRS feed

  • 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 lot
    Wednesday, 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 ! Smile

    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 too

     

    Tuesday, April 8, 2008 2:26 PM
  • It's 4am and I was about to start panicing   killing management studio did the trick.


    Thanks

    Thursday, 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 master
    GO

    ALTER 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 OFFLINE WITH ROLLBACK IMMEDIATE

    ...

    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.net
    Wednesday, 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.M
    Friday, 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

    This worked out great for me. Using sp_who, sp_who2 and other methods failed with an error about the database being in a transition, but the above ran, and allowed us to identify and kill the offending ALTER DATABASE 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!


    Paulino
    Wednesday, 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 me
    Friday, 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
  • Thanks
    Friday, 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

    www.high-flying.co.uk

    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