I got a call the other day from a night shift DBA with an issue. Seems he tried to contact the primary DBA for the server but they didn’t answer their phone and I was the unlucky knucklehead to answer mine. As it turns out, it wasn’t a big deal and gave me a good topic for a post.
Issue
The DBA in question had an Incident ticket he needed to work on for a Project Manager a little heated due to overtime being scheduled and they were behind in getting things ready for the work. We needed to restore a database from the 3 AM backup over the database they were going to work on. One thing to note, our evening and night shifts work both Oracle and SQL. I did that in my past life and remember it is not always easy to switch modes from one to the other especially when a manager is constantly calling you to see if you were done with the task.
Error
The DBA who called explained he was getting an error when trying to restore a database. I asked if he was first putting it in single user mode. His response, “No”. I explained he needed exclusive access to the database to restore it. I then walked him through navigating to the database Properties window by right clicking on the database name.
He then said he was getting an error when trying to open the properties window. The error was:
“Database in Transition” Error 952.
After logging on myself and looking in the logs I found this.
It seems when he tried to restore the first time and was unsuccessful he tried to take the database offline.
I then explained we needed to use a query to make sure nothing was blocking us.
This is a very basic script to find the blocking SPID.
After tracking down the SPID causing the issue ( and killing it) we were able to open the properties window, script out the following command(s) for future reference/use and restore the database without issues.
If you are not familiar with how to script out the above T-SQL code you can follow these basic steps to do so.
In the Object Explorer expand the server and expand the database folder. Then find the database you want to use and right click the database. A menu opens, select Properties at the bottom. Once the database properties window opens, navigate to the Options page and scroll to the bottom. Under State group there is a setting called “Restrict Access” change that to SINGLE_USER and script out to a new window by clicking the “Script” button at the top of the window next to “Help“.
There are a few comments on forums that mentioned restarting the SQL Server service. While that will work (since it kills all sessions) it may not be the best solution especially if you are on a production server and can’t restart the service. Blindly restarting a server is not usually the best option.
Conclusion
There are (almost) always multiple ways to solve an issue. Coming up with the best solution sometimes comes with experience or finding the correct blog post/forum entry that works best for your situation.
Cheers!