question

PaulKraemer-7547 avatar image
0 Votes"
PaulKraemer-7547 asked ErlandSommarskog commented

take database offline hangs

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered pituach edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PaulKraemer-7547 avatar image
0 Votes"
PaulKraemer-7547 answered ErlandSommarskog commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The reason that SSMS was probably that you had not check "Drop All Active Connections".

![145311-image.png][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.

0 Votes 0 ·
image.png (7.4 KiB)