Wednesday, October 10, 2012

Error 952 Database is in transition

Hello and welcome back...

So today I came across an issue with one of my SQL Server 2005 databases.  I started receiving automated emails stating that one of the reporting databases was set to 'Offline'.  I logged into the server and opened up SSMS and the database appeared to be online however, when I tried to open the database I received the following error message:
      Database 'SalesHistory' is in transition. Try the statement later.
      Error: 952, Severity: 16, State: 1

I attempted to execute the ALTER DATABASE statement to bring the database online however, I received an error stating the database was not available.  So I did some research and there is a bug in SQL Server 2005 SSMS that puts an exclusive lock on the database from the last spid connecting to the database.  This bug prevents any other connections from accessing the database and makes it appear to SQL Server the database is offline.  From the research I did, the solution was to find the host name that had the SSMS connection open and close it.

To do this I ran a sp_who2 active and noticed spid 72 with the command 'ALTER DATABASE' and I executed dbcc inputbuffer(72) to view the full statement
       Alter Database SalesHistory Set Offline
       With Rollback Immediate

With this information I was able to track down the client computer and I had the user close their SSMS and the SalesHistory database was now available.

I hope this helps!...



  © Blogger template 'A Click Apart' by 2008

Back to TOP