Sql-server – Set database offline with rollback immediate takes an hour to complete

backupsql serversql-server-2012

Every night we take our production database offline, detach it, and then re-attach it to sever any active connections before 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 earlier to complete which was acceptable. From 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 changed any settings that would cause this. My understanding was WITH ROLLBACK IMMEDIATE 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 MSSQL Server 2012(Standard Edition) in simple recovery mode with no mirroring. The database is about 300 GB in size.

Any help is appreciated, thank you!

Best Answer

I believe what you are seeing is completely normal.

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

Why do you do this ?. This is not the correct thing to do always and this is precisely why you are facing the issue. You can take backup of the database when it is online and this is how you should backup SQL Server database. The full backup which you will take when database is online would include all the committed changes done to database when backup finishes. Keeping in single_user and restricted_user mode before the activity may not solve your problem completely.

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.

You also said that there is no long running query, for that I would say triple check. Alter database requires exclusive access on the database and it may be blocked by some other query, have you checked that.

Did yo checked for any transaction running with begin transaction and may be have missed commit. Run DBCC OPENTRAN() to see for any open transactions. Errorlog would have more information on this

The other thing I see is sometimes when you run alter command there are hardly any queries running which needs rollback and so the command finishes quickly while other days there are some command running which needs to be completely rolled back before database can be made offline. Depending on amount of rollback that needs to be done the time varies.

I would not suggest you to put database in offline mode, I do not see any use in this.

Related Question