Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Custom Log Shipping Failing Expand / Collapse
Author
Message
Posted Tuesday, May 25, 2010 2:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 16, 2011 8:22 AM
Points: 378, Visits: 353
Hi,

Perhaps someone can suggest a tweak, change or alternative to the custom log shipping process I have setup. A custom solution was needed because we only have Standard Edition that does not have backup compression and we need to move quite a large amount of data to a remove site.

One of the issues is that when I try to restore the full database from primary to secondary (if the log shipping process has been interrupted for some reason), the secondary is often stuck with the message 'restoring' and I cannot apply log backups after that.

The current process using SSIS is:

Backup primary
Zip backup with 7z
Copy zip to secondary
Unzip
Restore backup to secondary

For a database that is in the region of 120 gb (compresses to just a few gb) this process takes about 10.5 hours, and most often gets the database stuck with restoring message mentioned earlier.

When restoring a transaction log the size runs at about 1 - 4 gb depending on the time of day and often fails.

The whole process if not at all fault tolerant and 'falls over' with the slightest problem.

Is there anything else I should take into account, or does log shipping generally require a fair amount of manual intervention?

Any suggestion and info from the experts out there would be great!

Thanks,

Jinx.


Post #927290
Posted Wednesday, May 26, 2010 2:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 16, 2011 8:22 AM
Points: 378, Visits: 353
For future reference - I have solved the biggest problem I was having with the secondary database being stuck in a restoring state for large databases. If I start the job (from remote session), and my session is disconnected it causes the database to be in this stuck restoring state.
Best plan is then to not 'kick start' the process myself but rather let a schedule take care of it.
Post #928019
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse