(Ola Hallengren) Deleting transaction log backups

Question

We're using Ola's script to run weekly full, nightly diff and hourly transaction logs. All the jobs are correctly creating their backups, and the full and diff jobs are cleaning up after a week, transaction logs are set to clean up after 48 hours (we backup the NAS that the files are stored on nightly so these files are still accessible if we need them, they're just out of the way).

I'm finding transaction logs on certain databases aren't cleaning up, ie when I look at the file-system I can find transaction logs from a month ago dating right forward to today. The only reason I could think for this is if a more recent diff backup doesn't exists, but they do, and they've not been moved to a different directory. None of the jobs are recording any any kind of failure triggered manually or running from the schedule. The other jobs are running and cleaning up just fine, ruling out permission issues (I've also checked on the NAS, the SQL server agent has Full Control).

Current step being run by the log backup job:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\server\folder\', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 48, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Here's the Diff job that cleans up just fine for comparison

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\servername\folder', @BackupType = 'DIFF', @Verify = 'Y', @CleanupTime = 192, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Does anyone know more about how the job checks for the presence of the a full or diff backup? Or any other reason this could be happening?

Thanks

Answer 1

I just noticed that you are only providing information on the differential and transaction log backup steps of the backup procedure. What is with the FULL backup?

Ola has implemented a fail-safe mechanism which will delete transaction log backups only if a FULL and/or DIFF backup is available.

DatabaseBackup has a check to verify that transaction log backups that are newer than the most recent full or differential backup are not deleted.

_{Reference: SQL Server Backup (from ola.hallengren.com)}

This means that if you don't have a recent FULL or DIFF backup, then the @CleanupTime parameter will have no effect. All the Transaction Log backups will be kept on disk, even though the CleanupTime has passed.

You might want to check if your FULL, DIFF and TLOG backups are consistent and available on disk. It is important to have all files available. sequence available.

Example of Files on Disk

Assuming it is Friday 3.30pm (what a coincidence) you should find the following files in your backup folder if everything is set up correctly:
_{(I have put a separator between the FULL and DIFF backups)}

SA | 1900 | FULL
----------------
SU | 1800 | DIFF
----------------
MO | 1800 | DIFF
----------------
TU | 1800 | DIFF
----------------
WE | 1800 | DIFF 
----------------
WE | 1800 | TLOG
WE | 1900 | TLOG
WE | 2000 | TLOG
WE | 2100 | TLOG
WE | 2200 | TLOG
WE | 2300 | TLOG
WE | 2400 | TLOG
TH | 0100 | TLOG
TH | 0200 | TLOG
TH | 0300 | TLOG
TH | 0400 | TLOG
TH | 0500 | TLOG
TH | 0600 | TLOG
TH | 0700 | TLOG
TH | 0800 | TLOG
TH | 0900 | TLOG
TH | 1000 | TLOG
TH | 1100 | TLOG
TH | 1200 | TLOG
TH | 1300 | TLOG
TH | 1400 | TLOG
TH | 1500 | TLOG
TH | 1600 | TLOG
TH | 1700 | TLOG
----------------
TH | 1800 | DIFF
----------------
TH | 1800 | TLOG
TH | 1900 | TLOG
TH | 2000 | TLOG
TH | 2100 | TLOG
TH | 2200 | TLOG
TH | 2300 | TLOG
TH | 2400 | TLOG
FR | 0100 | TLOG
FR | 0200 | TLOG
FR | 0300 | TLOG
FR | 0400 | TLOG
FR | 0500 | TLOG
FR | 0600 | TLOG
FR | 0700 | TLOG
FR | 0800 | TLOG
FR | 0900 | TLOG
FR | 1000 | TLOG
FR | 1100 | TLOG
FR | 1200 | TLOG
FR | 1300 | TLOG
FR | 1400 | TLOG
FR | 1500 | TLOG

Possible Reasons TLOG Files Aren't Deleted

Ola's Failsafe Mechanism

If a DIFF backup is missing (for example the DIFF from WE and TH) then no TLOG backups will be deleted from disk up until the last DIFF or FULL backup. In this example you would have TLOG backup files up until the DIFF backup on TUesday at 6pm (1800).

Solution

1. Ensure you have FULL and DIFF backups on disk.

"SQL Server Agent" Service Account Insufficient Privileges

If the SQL Server Agent (INSTANCE) Service Account doesn't have enough privileges on the directory it is deleting files from, then it will be unable to delete the TLOG backup files.

Solution

1. Ensure the Windows Account of the SQL Server Agent Service has access to the network drive.

2. Check that the Run As setting in the job step is set to the SQL Server Agent Service Account.

SQL Server Agent Jobs Not Being Logged in Msdb

There are cases then the job steps of jobs executing are unable to enter the details into the msdb job tables due to contention (locks, blocks, long running transactions). The tables required by the backup job are being locked by another process (another maintenance plan / other 3-rd party tools / cleanup jobs / a long running transaction) and might be determined as the victim of a deadlock and rolled back. The data is missing in the msdb database and because no FULL or DIFF backups exist, nothing gets deleted.

Ola checks this with the following part in the DatabaseBackup stored procedures:

    IF @CurrentBackupType = 'LOG' AND (@CleanupTime IS NOT NULL OR @MirrorCleanupTime IS NOT NULL)
    BEGIN
      SELECT @CurrentLatestBackup = MAX(backup_finish_date)
      FROM msdb.dbo.backupset
      WHERE ([type] IN('D','I')
      OR database_backup_lsn < @CurrentDifferentialBaseLSN)
      AND is_damaged = 0
      AND database_name = @CurrentDatabaseName
    END

This means that if the record for the previous DIFF or FULL backup was unable to be recorded in the msdb database, then the TLOG files will never be deleted.

Special Solution

1. Check that the FULL and/or DIFF backups of the database in question are recorded in the msdb database and verify that you don't have any long running transactions on the msdb database.

2. Reschedule (the/other) jobs to run at a slightly different times if you have multiple jobs running at the same time.

Good luck.

Answer 2

A few things could be going on here.

The files are in the wrong place, or have an unexpected extension

This one seems unlikely, since you've probably been using Ola's scripts for all your backups, but just in case you've changed a setting (like the directory location, or the log file extension).

The "cleanup" part of Ola's scripts are looking for backups in this location (starting with the server / folder you specified in the @Directory parameter). In your question you used @Directory = N'\\server\folder\' so I'll continue with that:

\\server\folder\{ServerName}${InstanceName}\{DatabaseName}\LOG

If this is a default (not named) instance, you can ignore the $InstanceName part.

If the files you want to be deleted are not in this folder, and don't have a .trn extension, then they won't be deleted by the cleanup step.

Something is "touching" your log backup files

The xp_delete_file extended stored procedure date parameter deletes based on the "modified date" metadata. If some other application has "touched" these files, causing Windows to update the modified date to a time inside the cutoff, they won't be deleted.

There is a permissions issue

The account that's running SQL Server Agent Services should have full control on the folder where backups are to be deleted. Make sure this hasn't been changed.

Validation checks are failing or other problems occurred

I noticed you have @LogToTable = 'Y' - you should check to confirm there are no error messages being reported during these times.

Answer 3

I downloaded and ran the latest version of Ola's scripts and I'm no longer having the issue. Thanks to all responders! :)