1

I have the following setup, all servers are SQL 2014 Enterprise:

  • 2 node AlwaysOn availability group;
  • third server for off-site DR.

Log backups are being taken on the primary node, copied to the secondary node (restore job is disabled of course) and copied and restored to the DR server.

Everything is working great, except the fact that the log backup files are not being deleted on the secondary node. I noticed it because the folder kept growing, when I opened it I saw all the logs are there since day 1 when log shipping was set up. All jobs are executed without errors and the interval to keep the log backups is set. How can I troubleshoot this?


UPDATE: Apparently, the process of deleting the old files is actually part of the RESTORE job and hot the COPY job.

Now the question is, can I safely run the RESTORE job on a secondary AlwaysOn node as well?

  • Are any of the agent jobs logging errors when trying to cleanup this folder? – James Anderson Sep 11 '15 at 14:11
  • No, but I just noticed that on the DR node, old backups are being deleted as part of the RESTORE job and not the COPY job. Since I have my RESTORE job disabled on the secondary node, I guess this is why the backups are not being deleted. – nulldotzero Sep 11 '15 at 14:20
  • Yep sounds like those steps need to be split into their own agent jobs. – James Anderson Sep 11 '15 at 14:30
  • Yep, I edited the question to reflect these findings. – nulldotzero Sep 11 '15 at 15:02
0

Wouldn't you want to ensure the secondary logs are restored to the secondary server at certain intervals? With log shipping, I thought you ship the logs over to your secondary DB and then restore from those to keep the secondary up-to-date as close as possible to your primary.

Do you see any reason why you would not want to automate the log shipping restore on your secondary DB in the log shipping configuration?

Once those log files are applied to secondary, then the job will purge the logs that are not needed any longer since those are reflected in the secondary DB at that time.

Test it out and see how it goes.

EDIT: @nulldotzero

Okay, since you cannot run restore operations on primary or secondary in an AlwaysOn configuration, and because you are also copying the TRN log files to the seconary as well as the DR for redundancy, then you could just setup a SQL Agent job on the secondary instance to do the below for whatever hours you feel need to be purged.

--This will set the date time stamp to pass onto the Stored Proc for 72 hours from the current date when it runs
--The stored proc will recursively delete all TRN files from the parent level specified all the way down
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'S:\MSSQL\Backup\TransactionLog\',N'trn', @DeleteDate,1

Since the log backup files are purged from the locations which the LSRestore for DR runs and grabs those, then that job will take care of those once the restore job completes successfully.

  • I guess you missed the part where my secondary node is part of AlwaysOn Availability Group, so it's already replaying all the logs - no need to restore. – nulldotzero Sep 14 '15 at 15:58
  • Yes I use log shipping for DR purposes, but in the secondary AlwaysOn AG node I use it to automatically copy the logs locally on the server, so in case that node 1 goes missing I can restore everything from node 2 (if necessary). – nulldotzero Sep 18 '15 at 15:54
  • Asynchronous-commit and backups are taken on primary node. – nulldotzero Sep 21 '15 at 15:35
  • Thank you @PJ for the article, I know it very well and this was the base to what I have implemented. The difference is I opted for local copies since I didn't want to have only one copy on a network share (single point of failure). I guess I'll just have to try and see what happens if I enable the restore jobs on the secondary. Thanks for you dedication! – nulldotzero Sep 23 '15 at 7:43
-2

look in the msdb log shipping tables, or run select * from msdb.dbo.log_shipping_monitor_secondary

and history retention period usually 5760, by adjusting this value you can have the transaction logs that are restored to be purged.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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