2

Does Microsoft SQL Server's log shipping feature remove the backup files automatically? I am concerned that the secondary server's hard drive will reach capacity unless the previously restored files get removed.

I have not been able to find a reliable answer to this anywhere.

  • i think it does but if not, you can schedule a job to do the deletion yourself as soon as the restore on the secondary server is done. – jyao Jan 10 at 4:04
  • I cant see why it should remove them. Log shipping has nothing to do with backup. – Mr Zach Jan 10 at 6:59
3

Log Shipping utilises an executable called SqlLogShip.exe. As per the documentation, when it runs as part of the LSBackup job, it removes old backup files outside the defined retention period on the primary:

The backup operation creates the log backup in the backup directory. The sqllogship application then cleans out any old backup files, based on the file retention period. Next, the application logs history for the backup operation on the primary server and the monitor server. Finally, the application runs sp_cleanup_log_shipping_history, which cleans out old history information, based on the retention period.

When it runs as part of the LSRestore job, it removes old backup files outside the defined retention period on the secondary server:

Any backup files in the destination directory that were created after the most recent restore point are restored to the secondary database, or databases. The sqllogship application then cleans out any old backup files, based on the file retention period. Next, the application logs history for the restore operation on the secondary server and the monitor server. Finally, the application runs sp_cleanup_log_shipping_history, which cleans out old history information, based on the retention period.

Provided your retention periods are set and SqlLogShip.exe is executing correctly, it should maintain the log files on both the primary and secondary locations.

1

Yes and No. It wont remove as soon as they are restored but it will remove after a set retention period that you can define.

The stored procedure sp_change_log_shipping_secondary_primary allows you to set this and documentation for it is on Microsoft Docs

You can also on your secondary set it directly in the following table msdb.dbo.log_shipping_secondary in the field file_retention_period which is stored in minutes.

UPDATE msdb.dbo.log_shipping_secondary  
SET file_retention_period = 4320 --3 Days
WHERE secondary_id = 'ID_OF_YOUR_SECONDARY'

Lookup the secondary_id by first doing a select on that table to find the one you want to change. After this it will purge files old than the retention in the Copy job.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.