stuck taking database offline

Ameen Khan 6 Reputation points
2022-08-23T08:15:34.1+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,108 questions
{count} vote

3 answers

Sort by: Most helpful
  1. CathyJi-MSFT 21,111 Reputation points Microsoft Vendor
    2022-08-24T06:19:12.6+00:00

    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".

    2 people found this answer helpful.
    0 comments No comments

  2. SQLZealots 276 Reputation points
    2022-08-23T08:32:02.77+00:00

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

    0 comments No comments

  3. Sreeju Nair 12,026 Reputation points
    2022-08-23T08:33:21.663+00:00

    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

    0 comments No comments