Auto-retry when DISCONNECT_EXISTING fails?

Compresses, encrypts, secures and monitors SQL Server backups.

Auto-retry when DISCONNECT_EXISTING fails?

Postby NeM » Wed Aug 08, 2012 4:14 pm

I am using SQL Backup 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.
Posts: 18
Joined: Mon Feb 14, 2011 3:55 pm

Postby eddie davis » Thu Aug 09, 2012 6:29 pm

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 Davis
Technical Support Engineer
Red Gate Software Ltd
eddie davis
Posts: 1100
Joined: Wed Jun 14, 2006 2:47 pm
Location: Red Gate Software

Postby petey » Thu Aug 09, 2012 10:16 pm

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?
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Posts: 2339
Joined: Sun Apr 24, 2005 11:34 am

Postby ChrisAVWood » Fri Aug 10, 2012 1:58 pm

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.

English DBA living in CANADA
Posts: 308
Joined: Tue Dec 18, 2007 5:18 pm
Location: Edmonton, Alberta, CANADA

Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests