31

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?

2
  • 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. Jan 8, 2010 at 6:36
  • Check out this. This worked for me. stackoverflow.com/a/27036481/4273753
    – user255086
    Nov 21, 2014 at 7:31

5 Answers 5

42

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.

1
  • It's so strange. Do you know why it's happening?
    – Krismorte
    May 20, 2017 at 20:18
11

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

2
  • too easy. worked for me.
    – greg121
    Mar 24, 2016 at 8:40
  • Closing SSMS and reopening worked for me as well. Dec 3, 2018 at 18:31
3

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): https://stackoverflow.com/questions/4230290/sql-server-2008-how-do-i-disconnect-everyone-from-my-db

1

There is an option available in SQL Server Management Studio for taking a database online/offline. But it needs administrative permission.

To access it, right click database --> Tasks--> Bring Online.

0

I know it's already answered, but just to add; if the situation is such that you were trying to take it offline, and then it failed, you could try killing the SPID that is trying to change the database state.

Do sp_who or sp_who2 and find an SPID that's spinning on the DB trying to take the database offline. KILL that SPID, and you are left with either online or offline DB that is NOT in the transitional state.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .