Auto-retry when DISCONNECT_EXISTING fails?

NeM

I am using SQL Backup 7.1.0.72 and I was trying to restore a database with the "DISCONNECT_EXISTING" parameter but the restore failed with the following in the log:

8/8/2012 10:18:05 AM: Failed to disconnect existing connections to database.
8/8/2012 10:18:05 AM:
8/8/2012 10:18:06 AM: Memory profile
8/8/2012 10:18:06 AM: Type Maximum Minimum Average Blk count Total
8/8/2012 10:18:06 AM:

---

---

---

---

---

---

8/8/2012 10:18:06 AM: Commit 359735296 4096 171063 54454 9315065856
8/8/2012 10:18:06 AM: Reserve 1694433280 4096 206409 51940 10720886784
8/8/2012 10:18:06 AM: Free 6863335718912 4096 34825623031 252 8776057004032
8/8/2012 10:18:06 AM: Private 1694433280 4096 188119 105552 19856396288
8/8/2012 10:18:06 AM: Mapped 2945024 4096 176009 69 12144640
8/8/2012 10:18:06 AM: Image 48140288 4096 216574 773 167411712
8/8/2012 10:18:06 AM:

Is there way in SQL Backup to automatically retry "DISCONNECT_EXISTING" if it fails and if not, can that functionality be added to future versions? This will really be helpful when we do automated restore operations in the middle of the night and not having to find out the restore failed the next morning because it failed to disconnect existing connections to the database.

Eddie D

Thank you for your post into the forum.

To my knowledge there is not retry mechanism for the "DISCONNECT_EXISTING" functionality.

Therefore I submitted a feature request for the development team to condsider adding a retry feature to this area of the product. The reference for this request is SB-5363. I cannot guarantee that this request will be successful, nor if approval is given what future version of SQL Backup it will appear in.

Many Thanks
Eddie

petey

SQL Backup uses the 'ALTER DATABASE [..] SET OFFLINE WITH ROLLBACK IMMEDIATE' command to disconnect any existing users.

Do you know why the DISCONNECT_EXISTING option failed? Was it because the rollback process took a while to complete, or that another process was blocking SQL Server from taking the database offline?

ChrisAVWood

When we restore a database we force the users off with alter database set single user with rollback immed and on SQL2005 there would be problems with orphaned DTC spids so this would go on for ever. This got fixed by later builds of SQL2005. If by chance you have a long running transaction on a database that has multiple CPU's that rollback is single threaded. So if you try and force a transaction that has run for 5 minutes using 8 CPU's it could take 40 minutes to rollback.

Chris