(Ola Hallengren) Deleting transaction log backups 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
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  


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  


Ensure the Windows Account of the SQL Server Agent Service has access to the network drive. 
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 &lt; @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  


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.
Reschedule (the/other) jobs to run at a slightly different times if you have multiple jobs running at the same time.


Good luck.