take database offline hangs

Question

take database offline hangs

Paul Kraemer 276

Hi,

I have a database in SQL Server 2012 on which I right-clicked and selected Tasks | Take Offline. This opened a dialog box that says "Take database offline - MYSERVERNAME" in the title bar. In the "Details" section of this dialog box, a single action is listed as "Take database MYDATBASENAME offline" with status "In Progress". This has been going on for about a half hour. The dialog box only has a single button labeled "Close". Clicking this button does nothing.

Now, the database is not responding to clients (as if it is in fact offline, even though my request action still indicates that it is in progress.

I would like to cancel my request, so that the database will be online again and able to serve clients.

If anyone can tell me how to do this, I would greatly appreciate it.

Thanks and best regards,
Paul

Accepted answer

1 additional answer

Your answer

Answer 1

Hi,

First step: check the status of the database

USE master
GO
SELECT state_desc,* FROM sys.databases 
where [name] = 'Database_name_come_here'
GO

If the database is OFFLINE then you can bring it ONLINE using direct queries AND NOT GUI (I don't say the GUI will not work but that you should avoid using it)

USE [master]
GO
ALTER DATABASE [Database_name_come_here] SET  ONLINE
GO

Check the status again. If this is ONLINE the try to use the database and forget about the GUI

If there is issue then you need to confirm if the action of taking the database OFFLINE is actually running or this is an issue with the client side

-- this will return all the queries which are running now
SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_time 
FROM sys.dm_exec_requests req 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
GO

If the query is actually running then the next step is something I cannot recommend you to do, and if you do it then it is on your responsibility, but I would probably simply kill the running query, using the session_id from the above query

KILL [session_id]

Close the SSMS and re-open it and go back to work

Check the status and if needed bring database ONLINE

charlie arehart 40 Reputation points

2024-04-12T16:31:54.4833333+00:00

I'll add that with Ronin's helpful answer, I was able to see two things.

First, it was then clear (from his code) that I had a running request/process hanging (for several minutes) which was simply doing the "alter database" to take it offline.

But second, by using Activity Monitor (in SSMS) I could see that it was blocked by another request/process. In my case, it was an open connection from an application server talking to my DB, and I was able to kill that spid (or I could have restarted the app server or caused it to drop its connection). As soon as I did that, the alter database ran immediately.

(And yes, some may want to suggest how there are better tools to see what requests are running or being blocked, whether additional DMVs or reports, and including sp_whoisactive and more. Ronin shared something easily used, and so have I. The problem and solution are what matter most, though there may be many ways to get to the resolution.)

Answer 2

Paul Kraemer 276

Hi Pituach,

Thank you for your detailed response.

When I ran the script you suggested to check if the database is offline (or online), it reported that it was online, even though GUI dialog box was still open (and seemingly hung).

While this GUI dialog box was open, I had been periodically trying to connect to the database using my (Microsoft Access) client application. I was not able to connect for quite some time. Anyway, after the script reported that the database was online, I shutdown and restarted SSMS. After doing this, the dialog box was no longer active and I was able to connect with my MS Access client.

It's not clear to me whether it was simply time that allowed this issue to correct itself, or if restarting the SSMS (or running the script you suggested) had something to do with it. Anyway, I got it working yesterday shortly after reading your response.

I appreciate your help!

Best regards,
Paul

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-10-31T19:35:48.177+00:00
The reason that SSMS was probably that you had not check "Drop All Active Connections".

]1

If you don't do that, SQL Server will wait until there are no connections to the database. And SSMS, which only submits an SQL command, will wait for SQL Server. Note that if you have a query window open to the database, this counts a second connection.

You should know that all that SSMS does is to create SQL statements for you - it is not using any secret API. You can always get to this command clicking the Script button before you run the command.

In this case the command was

ALTER DATABASE db SET OFFLINE

For many of the operations that require exclusive access to the database, you need to in practice add the option WITH ROLLBACK IMMEDIATE to kill other processes. The only reason to not use that clause is that you don't want to perform the operation when there are other processes connected.

Share via

take database offline hangs

1 additional answer

Your answer