6

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!

9
  • 1
    What does the session say it is waiting on?
    – dwjv
    Mar 9, 2016 at 13:18
  • 3
    Does the messages say that some transactions were rolled back when it completes? What is the reason you need to take the database offline to back it up? If you are looking just to kick the users off during the backup you could use set the access to restricted, as long as the users you are trying to boot aren't in the sysadmin role, they will be blocked from logging in until you set the database back to multi_user. alter database set restricted_user with rollback immediate do your backups then alter database set multi_user with rollback immediate
    – Aaron
    Mar 9, 2016 at 14:51
  • 1
    @Aaron It shows the following messages: Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. Thank you for the restricted_user suggestion, I will attempt that tonight. It isn't necessary to take the database offline to back up, we just wanted to make sure any transactions and connections are cleared out as we had trouble with uncommitted transactions.
    – GUIs
    Mar 9, 2016 at 15:26
  • 1
    @GUIs you can't get the wait type from sp_who2 - you can query sys.dm_exec_requests (wait type) or check the Activity Monitor.
    – dwjv
    Mar 11, 2016 at 9:04
  • 1
    So the real problem you have is "we had trouble with uncommitted transactions." Can you describe that problem more? Bringing the database offline daily is likely using a sledgehammer to swat away a fly. It works, but it's overkill.
    – AMtwo
    Mar 7, 2019 at 11:41

2 Answers 2

5
+100

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.

2
  • "Alter database requires exclusive access on the database and it may be blocked" Even with ROLLBACK IMMEDIATE? Isn't that what ROLLBACK IMMEDIATE is for? To rollback the blockers? Mar 7, 2019 at 14:38
  • Yes you are correct, that is what it is used for. I wanted to emphasize on fact that it may be blocked, may be a rollback of other process is blocked.
    – Shanky
    Mar 7, 2019 at 16:39
-1

First of all I don't understand why database is kept in Simple recovery mode, if your database is production database then it is highly recommended to keep database in full recovery mode and take differential as well as log backup based on the volume of transaction as well as based on business requirement of your SLA for losing data or recovery point. You can understand more here:

https://www.sqlshack.com/backup-and-restore-sql-server-disaster-recovery/

In case, you are currently taking only full backup in the night and no other backup(Differential backup, as Log backup can't be taken considering recovery mode is Simple), if your database crashes during the day - you will lose all changes made to your database after last successful full backup. So, my first advice would be to change recovery mode and take Differential as well as log backup.

On your point of severing connections before taking backup - its not required at all, you can very much take hot backup and this can be taken when database is in multi-user mode and users are actively working(although backup is advised to run in less traffic hour). And if its hard and fast to close all the connection before backup then you should put your database in single user mode before turning it offline using provided command.

In order to check any active transaction, you may use very famous stored procedure sp_whoisactive from Mr. Adam Machanic, this would give you all the information about running session and many other details which is not feasible to get using sp_who or sp_who2. You can schedule this procedure just before your backup and redirect the output to a table for referring it later.You may also used DBCC OPENTRAN() to check any active transaction as suggested in above answer.

Overall the issue seems to be here with the process which is being followed for taking the backup. Please let us know if this helped.

2
  • It may be perfectly fine to have prod database with simple recovery. It all depends on RPO and RTO. You still have luxury of taking differential backups.
    – Shanky
    Mar 7, 2019 at 16:40
  • @Shanky Yes, I have also mentioned about it. Since, he hasn't mentioned differential backup, I am putting more emphasis in backup procedure and agreed SLA with business. In other words, RPO and RTO. Mar 8, 2019 at 6:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.