#StackBounty: #sql-server-2012 Set database offline with rollback immediate takes an hour to complete

Bounty: 100

Every night we take our production database offline, detach it, and then re-create it to sever any connections as the start of our backup procedures. The first command is:

USE master
ALTER DATABASE thedb SET OFFLINE WITH ROLLBACK IMMEDIATE

This command used to take 20 seconds to two minutes to complete which was acceptable. For the past few weeks it has been taking 15 minutes to over an hour to complete. There hasn’t been any change in database usage that would cause this problem, and it only happens about 3/4 of the time.

As far as I know, we have not settings that would cause this. My understanding was WITH ROLLBACK IMMEDAITE severed all connections and the database goes offline immediately. I have tried investigating the following:

  • recovery interval is set to 0 so checkpoints happen every few minutes and only take a second to complete
  • Statistics do not update asynchronously
  • no large queries are running just before the database goes offline
  • checking sp_who2 while the database going offline shows no users connected to it
  • the database’s log file is very small, ~30 MB

We are currently running SQL Standard 2012 in simple recovery mode with no mirroring. The database is about 300 GB in size.

Any help is appreciated, thank you!


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.