Tweaking log shipping restore

Log shipping has been part of SQL Server for many years. Its setup and configuration are straightforward and generally things work well. However, you may notice that you don’t have a lot of flexibility for tweaking log shipping. If you attempt editing the backup or restore jobs created by the wizard, you’ll quickly realize that log shipping is bit of a blackbox. The backup job simply contains the server name and primary id. The restore job contains the server name and the secondary id.

In some cases you may want to adjust the RESTORE LOG statement issued by log shipping executable to speed up the restore and ensure your primary and secondary databases stay in sync. This can be accomplished by updating msdb..log_shipping_secondary_databases table. Relevant columns are block_size, buffer_count and max_transfer_size. Changes are effective during next execution of the log shipping restore job.

Yes, this is a system table, but you’re allowed to update it. Clearly, you should know what you’re doing and test your changes in development environment prior to applying the same changes in production.

You may also be wandering if there is a way to tweak the BACKUP LOG command too. Sadly, the answer is no.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s