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:
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!