locked
Taking database offline takes long time... RRS feed

  • Question

  • In SQL Server Management Studio  when I try to take a database offline,  why does it take so long?

    Do I need to put the Database into restricted user mode first before attempting to take it offline?

    Monday, June 15, 2020 8:57 PM

Answers

  • In SQL Server Management Studio  when I try to take a database offline,  why does it take so long?

    Most likely there are other processes active in the database. You can check this with sp_who2 to see if these processes are doing something important.

    Do I need to put the Database into restricted user mode first before attempting to take it offline?

    Or you can be brutal and say
    ALTER DATABASE db SET OFFLINE WITH ROLLBACK IMMEDIATE


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, June 15, 2020 9:14 PM
  • Hi Veena,

    If many people or process are using the databases, some of the background process will cause the delay in Taking it offline. It is better to close all the connections and then take the database offline. Below is the command:

    ALTER DATABASE <Database Name> SET OFFLINE WITH ROLLBACK IMMIDIATE

    This will close all the Active connections and set the database offline quickly.

    Hope this answer your query.

    Thanks
    Atul

    Monday, June 15, 2020 11:27 PM
  • Hi Veena,

    It seems that some transactions are still running when you try to alter the database SET OFFLINE.

    Run sp_who2 while your command is executing to see if you're being blocked by another process.

    In addition to waiting, you can use the WITH ROLLBACK IMMEDIATE option: All incomplete transactions will be rolled back and any other connections to the database will be immediately disconnected.

    >>>So if there is an active process currently running in the database will the database go offline once the process completes?
    Background processes that are running against SQL Server may cause the ALTER DATABASE database-name SET OFFLINE command to be indefinitely blocked, as can internal processes. Please refer to this article.

    Best Regarads,
    Cris

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 16, 2020 3:47 AM
  • Correct. Or, to be more specific, it is enough to have a connection in the database to block the OFFLINE setting. As soon as there are zero connections in the database, it can go offline. 

    From a technical viewpoint, when you have a connection in a database, you have a certain type of lock at the database. This lock is there to prevent some other operations, like setting it offline, detach, etc.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, June 16, 2020 8:23 AM

All replies

  • In SQL Server Management Studio  when I try to take a database offline,  why does it take so long?

    Most likely there are other processes active in the database. You can check this with sp_who2 to see if these processes are doing something important.

    Do I need to put the Database into restricted user mode first before attempting to take it offline?

    Or you can be brutal and say
    ALTER DATABASE db SET OFFLINE WITH ROLLBACK IMMEDIATE


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, June 15, 2020 9:14 PM
  • So if there is an active process currently running in the database will the database go offline once the process completes?


    Monday, June 15, 2020 9:23 PM
  • Hi Veena,

    If many people or process are using the databases, some of the background process will cause the delay in Taking it offline. It is better to close all the connections and then take the database offline. Below is the command:

    ALTER DATABASE <Database Name> SET OFFLINE WITH ROLLBACK IMMIDIATE

    This will close all the Active connections and set the database offline quickly.

    Hope this answer your query.

    Thanks
    Atul

    Monday, June 15, 2020 11:27 PM
  • Hi Veena,

    It seems that some transactions are still running when you try to alter the database SET OFFLINE.

    Run sp_who2 while your command is executing to see if you're being blocked by another process.

    In addition to waiting, you can use the WITH ROLLBACK IMMEDIATE option: All incomplete transactions will be rolled back and any other connections to the database will be immediately disconnected.

    >>>So if there is an active process currently running in the database will the database go offline once the process completes?
    Background processes that are running against SQL Server may cause the ALTER DATABASE database-name SET OFFLINE command to be indefinitely blocked, as can internal processes. Please refer to this article.

    Best Regarads,
    Cris

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 16, 2020 3:47 AM
  • Correct. Or, to be more specific, it is enough to have a connection in the database to block the OFFLINE setting. As soon as there are zero connections in the database, it can go offline. 

    From a technical viewpoint, when you have a connection in a database, you have a certain type of lock at the database. This lock is there to prevent some other operations, like setting it offline, detach, etc.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, June 16, 2020 8:23 AM