Last week, I ran into two separate instances where I tried to take a SQL database offline, and the process got stuck. Turns out the resolution is quite simple, so I thought I would share the steps in hopes it would help someone else stuck in a similar predicament. It took me a quite a while to figure this out, so I wanted to post these steps for future reference.
The problem started when I simply tried to take a database offline, prior to removing it from the server altogether. We typically do this for a period of time, to make sure there are no existing processes or users who may be calling procedures or objects in the respective database.
When you select the “Take Offline” command (Right-Click on database, got to Tasks, then select Take Offline), as illustrated below, the database typically goes offline immediately.
However, when I tried this simple action, the following window remained on the screen, stuck in the “Take Offline” process:
To fix this issue, run the following steps.
First, in a new query window, execute the stored procedure below: EXEC sp_who
This system stored procedure provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.
In my case, I’m interested to see if anything pertains to the dbname “WebScrape”, which is the database I’m trying to take offline. Sure enough, there is one process:
For display purposes, I re-executed the stored procedure, to highlight the loginname causing the issue: EXEC sp_who ‘TestID’
Because loginname “TestID” has an open connection to dbname “WebScrape”, I am unable to take the database offline.
To fix this, I need to KILL the connection. This is accomplished by executing the KILL statement for “spid” 54
The SQL KILL Command:
Terminates a user process that is based on the session ID or unit of work (UOW). If the specified session ID or UOW has a lot of work to undo, the KILL statement may take some time to complete, particularly when it involves rolling back a long transaction.
KILL can be used to terminate a normal connection, which internally terminates the transactions that are associated with the specified session ID. The statement can also be used to terminate orphaned and in-doubt distributed transactions when Microsoft Distributed Transaction Coordinator (MS DTC) is in use.
Now, when I go back and check the status of the “Take Offline” process for database “WebScrape”, we have success!
And the database will look like this in SQL Server Management Studio:
I hope this helps someone who is experiencing the same situation!