Database ‘testDB’ is in Transition. Try the Statement later. Error:952

Have you ever been in a situation where your database is stuck in “Transition” yelling at you saying..

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

A Junior DBA’s first thought would be “Heard about ONLINE and OFFLINE, what is this TRANSIENT Status?? I’m having this Issue for the first time…OMG! Did i lost my Database?” Basically he starts pressing “PANIC BUTTON” on his/her Keyboard! which we never should as a DBA.(Trust me Guys, you’ll pay the penalty if you PANIC. Our Role as a DBA is to turn the PANIC Situation to a pleasant situation, not to supply oxygen to fire ;p

So..what does it really mean? Simple and straight forward thought which should strike on your mind is,
your Database is transitioning from Online State to Offline State, but for some reason it got stuck in middle. So SQL is saying that it’s trying to go Offline but it’s not able to!

Let’s simulate this scenario:
Step 1:
I’ve created a test database to play with and let’s call it as “transient_test”. I’ve created a test table to insert few records in an explicit transaction.
Table Structure:

/****** Object:  Table [dbo].[test]    Script Date: 07/14/2011 21:51:43 ******/
CREATE TABLE [dbo].[test](
[id] [int] NOT NULL,
[name] [char](10) NOT NULL,
[add] [nchar](10) NOT NULL
) ON [PRIMARY]
GO
Step 2:
Begin an explicit transaction as shown below in the screenshot

Step3:
In another Session,try to take the Database offline. It won’t go Offline and it will remain there sitting “in Progress” for ever as shown below, because of the Open Transaction.(We’ve to either commit/Rollback and Close that session to take database offline successfully).

Step 4: Now let’s try to access the Database from either Object Explorer or try to Query anything in that database. It’s screaming saying as shown below

Volaaaaaa….That’s it Guys to simulate this. Pretty Simple huh!!

Now let’s assume that’s you are not aware of what happened and you got a call from Application team for immediate assistance.
Pretty Simple to investigate…Just issue “select * from sys.dm_exec_requests” to see what exactly are running currently or just legacy SP_WHO2 and investigate further.
Note: I’m showing a very old school technique using INPUTBUFFER and Ad-HOC individual DMV’s since i know there is no activity going on this server since it’s my test lab. In a real environment i suggest you to make use of combination of DMV’s at a single stretch which does all the magic for you. Search for “performance tuning SQL Server 2005/2008 Whitepaper from Microsoft”.  It has all kinds of DMV Queries which will help you out.

Ha Ha…now you can see “SREE\Sreekanth”(the Bad guy) is the who is the Root cause for all this mess. Take a hammer and walk to his desk(if he’s nearby to you) Bangggggg on his head ;p (Don’t do it!!).
Once I commit/Rollback and Terminate the session, Database went Offline immediately and  you can bring DB Online back. Or you can just kill that Session which is trying to ALTER the Database without taking the database Offline.