July 23, 2012 at 6:34 am
My DBA brought me a problem that was very surprising. We had a log shipping secondary down for maintenance, and while it was down the retention period of logs for log shipping on the primary was exceeded. These had not been copied to the secondary, but were deleted from the primary when the LSBackup job ran.
They were deleted even though they were not copied.
I had just assumed that the system was smart enough not to create gaps by deleting files before they were copied, even if the retention period expired. But it appears not to be true.
This is out of the box log shipping on 2008R2 enterprise.
Are we missing a parameter or setup or something, or does it really work that way? Does anyone else think this a bit odd?
Any ideas for a safety net, other than having the alerts be much shorter than the retention (they are, and we did know it was down, but we KNEW it was down and just assumed the files would build up and transfer later)?
Haven't tested the other direction, if a restore (but not copy) is deferred beyond the secondary retention, will it delete files not restored as well?
PS. I realize there is a cross-server communication issue required to know if they are copied, but there is also a monitoring server involved. I would think if it could not tell, it would assume the worst (not copied).
July 23, 2012 at 6:47 am
Yeah thats by design, as the retention period has expired and therefore SQL will delete the file even if it hasn't been copied to the secondary.
Typically in this situation where the secondary is going offline for a period of time I usually tend to ensure that I have a simple robocopy job setup to transfer the TRN's to a network share or another drive outside of what SQL will clean up, so that I can manually copy them to the secondary and restore when it is operational again.
As for if its copied and not restored, then no it wont delete the files as the restore job is the one which controls deleting the files
July 23, 2012 at 7:16 am
Ferguson (7/23/2012)
My DBA brought me a problem that was very surprising. We had a log shipping secondary down for maintenance, and while it was down the retention period of logs for log shipping on the primary was exceeded. These had not been copied to the secondary, but were deleted from the primary when the LSBackup job ran.They were deleted even though they were not copied.
I had just assumed that the system was smart enough not to create gaps by deleting files before they were copied, even if the retention period expired. But it appears not to be true.
This is out of the box log shipping on 2008R2 enterprise.
Are we missing a parameter or setup or something, or does it really work that way? Does anyone else think this a bit odd?
The primary and secondary instances each have a file retention period, that's why it's important to set up LS correctly
Ferguson (7/23/2012)
Any ideas for a safety net, other than having the alerts be much shorter than the retention (they are, and we did know it was down, but we KNEW it was down and just assumed the files would build up and transfer later)?
change the retention on the primary to cover the downtime period or as someone else pointed out a robocopy job.
Ferguson (7/23/2012)
Haven't tested the other direction, if a restore (but not copy) is deferred beyond the secondary retention, will it delete files not restored as well?
Again the secondary has a file retention period and the restore job has a run schedule, ensure the 2 are in tune with each other
To resume your log ship scenario, take a differential backup on the primary and apply to the secondary then resume your LS jobs.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply