Microsoft SQL Server is a great product to work with. Especially from the release of version 2005 and now 2008. For me, it is the standard for all kinds of databases. The Object explorer, Tuning Advisor, the Query Analyser and T-SQL statements work great, and are a joy to work with. When it goes wrong somewhere, it is mostly a syntax error, program(bug) error or a user error that causes the problem. Stable as it is, I can recomment the use of it to anyone. In this articles I like to share with you, some errors and "best practices" that came up during my work on a number of occasions. So, initially set up was for my own references, now I share it with you. Enjoy these articles!! Due to the fact that a big percentage of visitors of this page are international based, this article is in the english language.
Error message 952 DB blocked :
This error can occur if a database has been taken offline and another statement of uery is run against it. This is a reasonable common bug, in SQL 2005 and in the earliest versions of SQL 2008. I must say, I hav'nt seen it in SQL 2008 R2 yet. Hope it stays that way. Now lets start with the cause of this error. On purpose, or through lots of other reasons, this can happen with databases. There are allways reasons for taking a database offline, mostly before you can attach another copy, but when it becomes inaccessible then, you can get in serious troubles... The transition state normally takes not more then 20 seconds for a database. But this error is always the result of using the SSMS itself. Setting a database offline requires absolute exclusive access to the Database and if any connection is this open, this error will occur. But do not worry! Now I'm certainly not a database administrator, but I picked up a few possibilities on the way which I state here below, keeping the SQL instance as long as possible online. But first to the actual error message. Most of the times it will be something like the picture here on the right en the text will be:
Cannot show requested dialog. the usual "Failed to retrieve data for this request", An exception,... etc etc.... and finally the error Database 'DBNAME' is in transition. Try the statement later. (Microsoft SQL Server, Error: 952) Database is in 'transition' state.
After this message (in the GUI); the only possible thing left to do is to click the OK button. There are a number of options to resove this error. I placed them in favorite order, with the lowest change of losing data and to avoid downtime. Suite yourself ofcourse, but this became a nice collection.
Close/stop and re-open/start the SQL Server Management Studio. This can often be the solution, especially in SQL 2005 instances with a service pack before version 4. Check the open sessions for other people that might have had the same idea and log off their session. Closing the SSMS often releases all left open connections, and that is the main reason to try this first. No harm done, simple and easy.
If you are getting this error from within the Object Explorer, please try to disconnect it and connect it again. You might be the victim of a short deadlock on the database and everything might respond fine after connecting again. Please check all your open connection for the database it concerns.
I assume you have access to the sys.database. please check the state of the database you are experiencing troubles with. Most of the times it will show "Online". Now check the sysprocesses and look for any "waitsource" that has your database ID, as well as a locked process ( SPID number ) This will be the process causing all the trouble and responable for the Database lock. In this case, you simply kill the blocking process and the database will resume the earlier started operation and go offline successfully.
If you are a litte more experienced, the stored procedure sp_who2 is usefull and easy for solving your issue. You will find at least one blocked process which is usually the management Studio and then you can see that it is blocked by a particular Database connection. If not,.... look for any connection that is in a "suspended" state. Terminate that particular session with the KILL <spid> command.
A little more drastic now, create a new query... Something like the lines below,.... Replace the YOURDBNAME with the correct name of your troubling SQL-database.
select * from sys.databases where name = <DBNAME>
--if the DB status is not zero, than reset the DB status
exec sp_resetstatus <your db name>
--look for any blocking SPID or any IF Wait. Resource column has that has the DBID
select * from sys.sysprocesses
--Look into the waitresource column what is blocking on the DBID
select * from sys.dm_exec_requests
--kill the Spid or the Sessionid which is the blocking process, OR try to kill the session/process which is waiting on the resources.
kill <blocking prosess/SPID/session ID>
-- immediatly execute put db offline with rolback
ALTER DATABASE <DBNAME> SET OFFLINE WITH ROLLBACK IMMEDIATE
This is almost everywhere the same for SQL 2008, but if it changes in next versions,.. I think you'll figure it out yourself.
If you have any questions about this article, or have an addition to it, do not hesitate to contact me.
Have fun using SQL server !
Ben Oostdam has been working with Windows systems since 1993. Worked for several companies as a system administrator, and is currently a Senior Support Engineer for Qurius Managed Services in the Netherlands specialized in System Center Solutions.
Disclaimer: The information contained in this website/article is for general information purposes only. The information is provided as is, by Ben Oostdam and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk. In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website. Through this website you are sometimes able to link to other websites which are not under my control. I have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them. Every effort is made to keep the website up and running smoothly. However, I take no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control. All entries in these articles, are my individual opinion, or from co-writers and they don't necessary reflect the opinion of my employer.