Skip to main content
corrected introduction
Source Link
John K. N.
  • 18.3k
  • 14
  • 54
  • 115

If you omit the FULL backup step from your backup procedure, then the DIFF and TLOG backups won't have a base on which to determine if the TLOG and DIFF backups can be deleted.

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

If you omit the FULL backup step from your backup procedure, then the DIFF and TLOG backups won't have a base on which to determine if the TLOG and DIFF backups can be deleted.

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

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

Source Link
John K. N.
  • 18.3k
  • 14
  • 54
  • 115

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?

If you omit the FULL backup step from your backup procedure, then the DIFF and TLOG backups won't have a base on which to determine if the TLOG and DIFF backups can be deleted.

Ola has implemented a fail-safe mechanism which will only delete transaction log backups only if a FULL 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.