Today I was trying to restore a database over an already existing database, I simply right clicked the database in SSMS --> Tasks --> Take Offline so I could restore the database.
A small pop up window appeared and showed Query Executing..... for sometime and then threw an error saying Database is in use cannot take it offline. From which I gathered there are some active connections to that database so I tried to execute the following query
USE master
GO
ALTER DATABASE My_DatabaseName
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
Again at this point the SSMS showed Query Executing..... for a sometime and then threw the following error:
Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'My_DatabaseName'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
After this I could not connect to the database through SSMS. and when I tried to Take it offline using SSMS it threw an error saying:
Database is in Transition. Try later .....
At this point I simply could'nt touch the database anything I tried it returned the same error message Database is in Transition.
I got on google read some questions where people had faced similar issue and they recommended to close the SSMS and open it again, So did I and Since it was only a dev server I just deleted the database using SSMS and restored on a new database.
My question is what could have possibly caused this ?? and how I can Avoid this to happen in future and if I ever end up in the same situation in future is there any other way of fixing it other then deleting the whole database ???
Thank you
Check out this article.
http://oostdam.info/index.php/sectie-blog/289-sql-error-952-8ways-to-solve-it
I use TSQL most of the time, so I have not run into this issue yet.
What version is the SQL Server database and at what patch level?
Next time, do a usp_who2 to see what threads are running.
http://craftydba.com/wp-content/uploads/2011/09/usp-who2.txt
Since the output is in a table, you can search by database.
Kill all threads using the database before the trying the ALTER statement.
A night about 6 months ago, I had a terrible time getting a 2000 database offline due to an application constantly hitting it. I eventually disabled the user account so I would not get any more logins.