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!

Answer 1

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

Answer 2

Thanks mate!

Answer 3

Thanks folks! It saved my day.

Answer 4

I simply stop-start SQL Server Service using Mgmt Studio and it worked for me!

-P

Answer 5

one more time, thanks a lot

Answer 6

It worked for me too, closing a forgotten QueryAnalayzer solve the problem on a sql2000 database.

Thanks a lot again.

Answer 7

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.

Answer 8

I agree on the other comments: it worked out very fine with no server stop.
Thanks for your post.

Answer 9

I got DB in transition message too, and restarting Management Studio resolved the issue. Thanks for the tip !

Answer 10

Yes, practical approach, strange error with SQL-2005 Management Studio.

Answer 11

Yes.. it work on my problem too

 

Answer 12

It's 4am and I was about to start panicing   killing management studio did the trick.


Thanks

Answer 13

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.

Answer 14

 

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.

Answer 15

Yes.It is working Good.Just close Management Studio on client Machine that can enough

 

Thanks

sat

BI Architect & DB Designer

Answer 16

Huge thanks for this info! Helped me out greatly.

 

And shame on MS for not providing it "officially".

Answer 17

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

Answer 18

 

simple... but also very efficient.

 

Tks

Answer 19

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.

Answer 20

Good lord, what a bug! Thanks all for this thread.

Answer 21

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.

Answer 22

Thanks for the posting... saved me valuable hours.

Answer 23

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

Answer 24

Oh, my.
Thank you, man!

Answer 25

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

Answer 26

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!

Answer 27

You are a super star....!!! Worked like a miracle to me!

Answer 28

Thank you very much. This tip really helps me !!! :)

Answer 29

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

Answer 30

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.

Answer 31

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.

Answer 32

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!

Answer 33

Four years since the original post, and the kudos just keep on comin'... Thanks for this, easiest fix I've had all week!

Answer 34

Thanks was a painful memory from the past, Enterprise Manager had some of the same issues.

 

Answer 35

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.

 

Answer 36

I could not believe it...but It worked for me!

Thanks man!

---

Paulino

Answer 37

5 years and still going strong. Cheers, I too was beginning to panic. I'm off to grab a backup of my dev database!

Answer 38

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. 

Answer 39

Thanks a lot. That solve lot of frustration for me

Answer 40

Thanks you for this help.

Answer 41

5 years later.

 

Still happening.

 

Thanks for the tip

Answer 42

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!

Answer 43

This post was really helpfull.

Even after 5 years its worthy.

Thanks a lot for participating in improving this post.

Answer 44

Thanks

Answer 45

I made the DB to single user mode and then made to multi user... it worked for me...

Answer 46

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

Answer 47

Hi there,

Thankssssss ... Restarting the MSSQL solved my issue with this error.

Answer 48

Check following link

http://sqlsolace.blogspot.com/2011/06/sql-2005-database-is-in-transitionerror.html

It says that

The solution. RESTART MANAGEMENT STUDIO.

Answer 49

RESTART MANAGEMENT STUDIO, and that's it!

Answer 50

Thanks!  Yes restarting SSMS fixe the issue, the DB returns available for access

Jeremy

www.high-flying.co.uk

Answer 51

Thanks for posting..this helped me as well.

Answer 52

Thanks for the answer...It worked absolutely fine...:))))

Answer 53

worked like a charm! =)