stuck taking database offline

Question

On my SQL 2019 server, I right-clicked the database and pick detatch, checked the box drop all active connections, and clicked OK. It's been sitting there for a while now and hasn't gone offline yet.

Looking at the process list, I see sa has a wait of PWAIT_DIRECTLOGCONSUMER_GETNEXT and a cmd of XTP_OFFLINE_CKPT. All of the other processes are normal app processes.

Is that sa process the one preventing the database from going offline? How can I force this to go offline?

Answer 1

Hi @Ameen Khan ,

Agree with others. Suggest you using T-SQL instead of SSMS UI. Please follow below steps to take database offline.

1.Using below T-SQL to take database offline.

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE  

2.If it is not work, there is most likely a connection to the DB from somewhere.

To find connections, use sys.sysprocesses

USE master  
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('MyDB')  

To force disconnections, use ROLLBACK IMMEDIATE

USE master  
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE  

Or you can run the following command to find out who is keeping a lock on your database:

EXEC sp_who2  

And use whatever SPID you find in the following command:

KILL <SPID>  

Then set the database offline.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

if your environment is not production, try the blow:
USE master
ALTER DATABASE thedb SET OFFLINE WITH ROLLBACK IMMEDIATE

Answer 3

Try to execute the following SQL command

USE master
ALTER DATABASE <<Your Database Name>> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Replace <<Your Database Name>> with your database name.

If you would like to see who is blocking, try the following commands.

SELECT * FROM sys.sysprocesses where dbid = DB_ID('Master')

replace Master with your database name

or try to use the stored procedure sp_who2

e.g. EXEC sp_who2

Hope this helps