Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

im trying to connect to a sql server database i get this error

Database 'XXX' is in transition. Try the statement later.

i cancelled a long query earlier today but for some reason i can't get the database to get back up. Is there anything i can do?

share|improve this question
4  
With a question such as this you really should be specific about what SQL server and its version. I'm guessing you mean one of the Microsoft SQL servers but we shouldn't have to guess. –  John Gardeniers Jan 8 '10 at 6:36
add comment

3 Answers

up vote 20 down vote accepted

This can happen sometimes if you try to take a DB offline or perform certain other operations and they fail. Sometimes the lock can be cleared if you close the SSMS instance that attempted the operation, then reopen it. Close and reopen any SSMS instances attached to the server.

It can also occur if you try to take the DB offline while a long query is running. Check the activity monitor and try killing any long-running queries, if applicable and safe.

If neither of the above works, close all SSMS instances, then restart SQL through the SQL Server Configuration Manager. Usually that will cure it, although the DB may be in recovery mode at first.

share|improve this answer
    
Thanks a million it has solved my issue as well :) –  M.Ali Jan 23 at 10:14
add comment

Strangely enough, I fixed this problem by simply closing SSMS and re-opening.

share|improve this answer
add comment

While closing SSMS worked for me too, depending on the problem, you could try this:

ALTER DATABASE [Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Name] SET MULTI_USER

This idea came from this question (and personal experience): http://stackoverflow.com/questions/4230290/sql-server-2008-how-do-i-disconnect-everyone-from-my-db

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.