Red Gate forums :: View topic - Auto-retry when DISCONNECT_EXISTING fails?
Return to RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Auto-retry when DISCONNECT_EXISTING fails?

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message

Joined: 14 Feb 2011
Posts: 18

PostPosted: Wed Aug 08, 2012 5:14 pm    Post subject: Auto-retry when DISCONNECT_EXISTING fails? Reply with quote

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.
Back to top
View user's profile Send private message
eddie davis

Joined: 14 Jun 2006
Posts: 940
Location: Red Gate Software

PostPosted: Thu Aug 09, 2012 7:29 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail

Joined: 24 Apr 2005
Posts: 2306

PostPosted: Thu Aug 09, 2012 11:16 pm    Post subject: Reply with quote

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 7
Back to top
View user's profile Send private message Send e-mail

Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri Aug 10, 2012 2:58 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group