olahallengren
commented
2 weeks ago The issue here is that some customers are only doing copy-only full backups and log backups.
That is common if you are doing backup on the secondary replicas (as non copy-only full backups are not supported on secondary replicas).
If I add is_copy_only = 0 to the where clause, the log backups will never get cleaned up in this scenario.
Description of the issue We use Veeam backup and replication software to replicate the SQL server with application aware processing on. So to make a replication, veaam takes a is_copy_only backup. So the backup chain of the SQL backup is not affected. Source: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server?view=sql-server-ver16. In the msdb.dbo.backupset a new row is inserted as type 'D' with the value of is_copy_only = 1. The DatabaseBackup SP does not check this value, and so the @CurrentLatestBackup gets a wrong value and LOG files are deleted before a FULL backup has made.
SQL Server version and edition Execute (Build 17763: ) (Hypervisor)
SELECT @@VERSIONMicrosoft SQL Server 2017 (RTM-CU31-GDR) (KB5029376) - 14.0.3465.1 (X64) Jul 30 2023 15:31:58 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0Version of the script Check the header of the stored procedure 2022-12-03 17:23:44
What command are you executing? EXECUTE [master].dbo.DatabaseBackup @Databases = '[600]', @Directory = 'X:\', @BackupType = 'LOG', @CleanupMode = 'AFTERBACKUP', @Compress = 'Y', @DirectoryStructure = '{DatabaseName}{DirectorySeparator}{BackupType}', @FileName = '{DatabaseName}{BackupType}{Year}{Month}{Day}{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}', @CleanupTime = 1, @LogToTable = 'Y', @Execute = 'Y';
What output are you getting? The last FULL backup was on 2024-06-30T23:10:02.
Date and time: 2024-07-01 09:42:52 Server: SQL01 Version: 14.0.3465.1 Edition: Standard Edition (64-bit) Platform: Windows Procedure: [master].[dbo].[DatabaseBackup] Parameters: @Databases = '[600]', @Directory = 'X:\', @BackupType = 'LOG', @Verify = 'N', @CleanupTime = 1, @CleanupMode = 'AFTER_BACKUP', @Compress = 'Y', @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'N', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @MinBackupSizeForMultipleFiles = NULL, @MaxFileSize = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTERBACKUP', @MirrorURL = NULL, @AvailabilityGroups = NULL, @Updateability = 'ALL', @AdaptiveCompression = NULL, @ModificationLevel = NULL, @LogSizeSinceLastLogBackup = NULL, @TimeSinceLastLogBackup = NULL, @DataDomainBoostHost = NULL, @DataDomainBoostUser = NULL, @DataDomainBoostDevicePath = NULL, @DataDomainBoostLockboxPath = NULL, @DirectoryStructure = '{DatabaseName}{DirectorySeparator}{BackupType}', @AvailabilityGroupDirectoryStructure = '{ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}{Partial}{CopyOnly}', @FileName = '{DatabaseName}{BackupType}{Year}{Month}{Day}{Hour}{Minute}{Second}{FileNumber}.{FileExtension}', @AvailabilityGroupFileName = '{ClusterName}${AvailabilityGroupName}{DatabaseName}{BackupType}{Partial}{CopyOnly}{Year}{Month}{Day}{Hour}{Minute}{Second}{FileNumber}.{FileExtension}', @FileExtensionFull = NULL, @FileExtensionDiff = NULL, @FileExtensionLog = NULL, @Init = 'N', @Format = 'N', @ObjectLevelRecoveryMap = 'N', @ExcludeLogShippedFromLogBackup = 'Y', @DirectoryCheck = 'Y', @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @LogToTable = 'Y', @Execute = 'Y' Version: 2022-12-03 17:23:44 Source: https://ola.hallengren.com
Date and time: 2024-07-01 09:42:52 Database: [600] State: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: FULL Encrypted: No Is accessible: Yes Differential base LSN: 588908000000746400226 Last log backup LSN: 589406000010117600001 Last log backup: 2024-07-01 09:30:01 Log size since last log backup (MB): 60.9805
Date and time: 2024-07-01 09:42:52 Database context: [master] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'X:\600\LOG' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) Outcome: Succeeded Duration: 00:00:00 Date and time: 2024-07-01 09:42:52
Date and time: 2024-07-01 09:42:52 Database context: [master] Command: BACKUP LOG [600] TO DISK = N'X:\600\LOG\600_LOG_20240701_094252.trn' WITH NO_CHECKSUM, COMPRESSION Processed 7795 pages for database '600', file '600_log' on file 1. BACKUP LOG successfully processed 7795 pages in 0.350 seconds (173.995 MB/sec). Outcome: Succeeded Duration: 00:00:01 Date and time: 2024-07-01 09:42:53
Date and time: 2024-07-01 09:42:53 Database context: [master] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_delete_file 0, N'X:\600\LOG', 'trn', '2024-06-31T09:00:02' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1) Outcome: Succeeded Duration: 00:00:00 Date and time: 2024-07-01 09:42:53
Date and time: 2024-07-01 09:42:53
Completion time: 2024-07-01T09:42:53.0619822+02:00