Every consulting engagement presents unique challenges and opportunities for learning. In this blog post, I would like to share an interesting scenario that I encountered during a recent Comprehensive Database Performance Health Check.
During the health check, we needed to create a test database in our environment. However, once the test was complete, we wanted to take the database offline so that we could either drop it or bring it back online if necessary. Unfortunately, when we attempted to take the database offline, we encountered an error related to the inability to place a lock on the database.
If you have ever encountered a similar error or if you are interested in learning how to take a database offline and online, I recommend reading my earlier blog post titled “SQL SERVER – T-SQL Script to Take Database Offline – Take Database Online”. This post provides step-by-step instructions on how to perform these actions.
If you encounter the following error message when attempting to take your database offline:
Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'database name'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
There is a simple solution or workaround that you can try. First, execute the following command to find your database in the list:
EXEC sp_who2
This command will display all active sessions and connections to the database. It is possible that your database connection is still active and has not been terminated for some reason.
If you are comfortable doing so, you can easily kill the SPID (Server Process ID) associated with the active session by running the following command:
KILL (SPID)
Once you have killed all the active sessions, you can then run the command specified in the earlier blog post to take your database offline using the ALTER DATABASE command.
By following these steps, you should be able to successfully take your database offline and perform any necessary maintenance tasks.
Thank you for reading this blog post. I hope you found it helpful in understanding how to take a database offline and online in SQL Server. If you have any questions or would like to share your own experiences, please feel free to leave a comment below.