May 23, 2013

The Phantom SPID: SQL Error 952 – Database Is In Transition

Recently I encountered a database issue on a Microsoft SQL 2008 R2 server that can only be described as a case of a phantom SPID (server process ID). What I mean by phantom is that this SPID was showing a date of 1900-01-01. All external connections to this database had been severed, but every attempt to drop the database or take it offline resulted in “database in use” errors.

Then there were periods of time where I couldn’t even get into the Properties of the database in SQL Management Studio. The following error was returned:

Cannot show requested dialog.

  • Additional Information:
    • Cannot show requested dialog. (SqlMgmt)
      • Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
        • An exception occurred while executing a Transact-SQL statement or batch.
          (Microsoft.SqlServer.ConnectionInfo)

          • Database ‘xxxxx’ is in transition. Try the statement later. (Microsoft SQL Server, Error: 952)

Oh no! Mr. Bill! - photo by phobus

Oh no!

I “think” I successfully traced this specific “transition” error to a disconnected remote desktop session that had SQL Management Studio still open. Once that session was cleared I was able to get into the Properties of the database. But I cannot say for sure, as this was the least of my worries at the time. What I can say is this: Double and triple check that there are no connections to a database before proceeding to take a database offline, “drop” it or before running any commands against it like “ALTER DATABASE.” You should run the “EXEC sp_who2” query to see all active SPIDs. While SQL should warn you about active connections, particularly when trying to take the database offline, it’s just a good practice to make sure nothing is trying to bang away on a database before changing it’s state.

So after I cleared the disconnected remote desktop session, I could get into the Properties of the database. I could even change permissions on the database. But I still could not take it offline or drop it. It said the “Database was in use.” If you do some Google searches for this problem, the below SQL query is a common result:

USE MASTER
GO
ALTER DATABASE [YOURDATABASE]
SET OFFLINE WITH ROLLBACK IMMEDIATE

This is essentially supposed to force the database offline. There are some variations, like adding “SET SINGLE_USER” which should stop any outside resources from opening a connection to the database:

ALTER DATABASE [YOURDATABASE]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

I can’t vouch for this myself. I’m just providing it here if it helps someone.

Some additional resources if you encounter a database that just will not go offline:

Fix: Cannot detach the database ‘DBName’ because it is currently in use. (Microsoft SQL Server, Error: 3703)

How to take database offline in Sql Server

SQL SERVER – Quickest Way to – Kill All Threads – Kill All User Session – Kill All Processes

The Case of the Phantom Spid: Troubleshooting and Resolving an Orphaned DTC Transaction

 

That last one sounded really promising considering I was already calling this a “phantom SPID” before turning up that search result. Alas it was not to be.

Unfortunately nothing I tried would free the database. The phantom SPID still remained. It still said it was “in use.” Luckily for my situation a new database was able to be created (restored from a backup) and the application was reconfigured to connect to this new database and it was back up and running. Not so lucky was that the old database (with the phantom SPID) was causing other issues on the SQL server. The SQL Server Agent was “kinda” running, but not really. Maintenance plans would not execute. Other databases could be taken offline but could not be brought back online. Yikes!

So what finally got it working again? I’m afraid the answer is not so sexy. Once a window of opportunity became available I stopped the SQL Server service. I then manually moved the database and transaction log of the “problem” database to another folder, essentially severing the ability for SQL to open this database when the SQL service was started again. I then restarted the SQL server and waited for it to boot up, hoping I didn’t somehow create a bigger problem. But I was fairly confident this would work.

Sure enough, once the server was up and running, I loaded up SQL Management Studio and I was able to simply delete the “problem” database. Obviously the database was no longer functional since the actual database files were not in their proper location, but the SQL Management Studio didn’t complain, it acted like it was still there. I confirmed the SQL Server Agent was running properly and maintenance jobs could now be run successfully. Databases could be taken offline and brought back online at will.

Obviously this is a pretty severe solution, but in this situation I was able to get away with it because I no longer needed the problem database.

photo by phobus

Please share your thoughts