May 15, 2018 at 3:32 am
Hello,
I'm using Ola Hallengran scripts for maintenance.
I have a SQL SERVER 2016 Enterprise edition and this DB is under always on.
i'm execute the job with this command:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBAdb -Q "EXECUTE [dbo].[DatabaseBackup] @databases = '[DBName]', @Directory = N'E:\SQL_Backup\LSBackup', @BackupType = 'LOG', @verify = 'Y', @CleanupTime = 24, @checksum = 'Y', @LogToTable = 'Y'" -b
for back log very long time.
Yesterday i discovered that it backup the log but didn't delete old trn files.
This is the output that i found at the sql server server agent log:
"
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0,
N'E:\SQL_Backup\LSBackup\XX_Cluster$XYZ_AG\DBName\LOG', 'trn', '2017-11-26T10:41:12' IF @ReturnCode <> 0
RAISERROR('Error deleting files.', 16, 1) Outcome: Succeeded Duration: 00:00:00 Date and time: 2018-05-15 12:08:43
Date and time: 2018-05-15 12:08:43.
Process Exit Code 0.
The step succeeded.
"
Can you please advise?
Thanks in advance!
May 15, 2018 at 4:36 am
Hi John,
I have added an output file please review the text below and let me konw if it's help.
"
Date and time: 2018-05-15 13:29:14
Server: *************
Version: 13.0.4001.0
Edition: Enterprise Edition: Core-based
Procedure: [DBAdb].[dbo].[DatabaseBackup]
Parameters: @databases = '[*****]', @Directory = 'E:\SQL_Backup\LSBackup', @BackupType = 'LOG', @verify = 'Y', @CleanupTime = 10, @Compress = NULL, @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @checksum = 'Y', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @LogToTable = 'Y', @Execute = 'Y'
Source: http://ola.hallengren.com
Date and time: 2018-05-15 13:29:14
Database: [*****]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: FULL
Availability group: *****_AG
Availability group role: PRIMARY
Availability group backup preference: PRIMARY
Is preferred backup replica: Yes
Differential base LSN: 51000000096100245
Differential base is snapshot: No
Last log backup LSN: 15707000020240600001
Date and time: 2018-05-15 13:29:14
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'E:\SQL_Backup\LSBackup\*****_Cluster$I*****_AG\*****\LOG' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2018-05-15 13:29:14
Date and time: 2018-05-15 13:29:14
Command: BACKUP LOG [*****] TO DISK = N'E:\SQL_Backup\LSBackup\*****_Cluster$*****_AG\*****\LOG\*****_Cluster$*****AG_*****_LOG_20180515_132914.trn' WITH CHECKSUM, COMPRESSION
Processed 1 pages for database '*****', file '*****_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.811 seconds (0.005 MB/sec).
Outcome: Succeeded
Duration: 00:00:06
Date and time: 2018-05-15 13:29:20
Date and time: 2018-05-15 13:29:20
Command: RESTORE VERIFYONLY FROM DISK = N'E:\SQL_Backup\LSBackup\*****_Cluster$*****_AG\*****\LOG\*****_Cluster$*****_AG_*****_LOG_20180515_132914.trn' WITH CHECKSUM
The backup set on file 1 is valid.
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2018-05-15 13:29:20
Date and time: 2018-05-15 13:29:20
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'E:\SQL_Backup\LSBackup\*****_Cluster$*****_AG\*****\LOG', 'trn', '2017-11-26T10:41:12' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2018-05-15 13:29:20
Date and time: 2018-05-15 13:29:20
May 15, 2018 at 4:52 am
So you're only keeping 10 hours' worth of log backups?! When was your last full (or differential) backup made - 2017-11-26T10:41:12?
CleanupTime
Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.DatabaseBackup has a check to verify that transaction log backups that are newer than the most recent full or differential backup are not deleted.
John
May 15, 2018 at 5:12 am
89netanel - Tuesday, May 15, 2018 4:36 AMHi John,
I have added an output file please review the text below and let me konw if it's help."
Date and time: 2018-05-15 13:29:14
Server: *************
Version: 13.0.4001.0
Edition: Enterprise Edition: Core-based
Procedure: [DBAdb].[dbo].[DatabaseBackup]
Parameters: @databases = '[*****]', @Directory = 'E:\SQL_Backup\LSBackup', @BackupType = 'LOG', @verify = 'Y', @CleanupTime = 10, @Compress = NULL, @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @checksum = 'Y', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @LogToTable = 'Y', @Execute = 'Y'
Source: http://ola.hallengren.comDate and time: 2018-05-15 13:29:14
Database: [*****]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: FULL
Availability group: *****_AG
Availability group role: PRIMARY
Availability group backup preference: PRIMARY
Is preferred backup replica: Yes
Differential base LSN: 51000000096100245
Differential base is snapshot: No
Last log backup LSN: 15707000020240600001Date and time: 2018-05-15 13:29:14
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'E:\SQL_Backup\LSBackup\*****_Cluster$I*****_AG\*****\LOG' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2018-05-15 13:29:14Date and time: 2018-05-15 13:29:14
Command: BACKUP LOG [*****] TO DISK = N'E:\SQL_Backup\LSBackup\*****_Cluster$*****_AG\*****\LOG\*****_Cluster$*****AG_*****_LOG_20180515_132914.trn' WITH CHECKSUM, COMPRESSION
Processed 1 pages for database '*****', file '*****_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.811 seconds (0.005 MB/sec).
Outcome: Succeeded
Duration: 00:00:06
Date and time: 2018-05-15 13:29:20Date and time: 2018-05-15 13:29:20
Command: RESTORE VERIFYONLY FROM DISK = N'E:\SQL_Backup\LSBackup\*****_Cluster$*****_AG\*****\LOG\*****_Cluster$*****_AG_*****_LOG_20180515_132914.trn' WITH CHECKSUM
The backup set on file 1 is valid.
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2018-05-15 13:29:20Date and time: 2018-05-15 13:29:20
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'E:\SQL_Backup\LSBackup\*****_Cluster$*****_AG\*****\LOG', 'trn', '2017-11-26T10:41:12' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2018-05-15 13:29:20Date and time: 2018-05-15 13:29:20
"
There are no errors here. Are you confusing the printing of the command with an error? In each of these, it says Outcome: Succeeded. Are the files actually being deleted?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 17, 2018 at 4:59 am
HI All,
I figured it out.
This db has always-on.
The Full backup job happened on the Secondary server.
The Log backup happened on the primary server.
I found this on ola hallengren site and now it's make sense for me:
DatabaseBackup has been designed not to delete transaction log backups that are newer than the most recent full or differential backup.
This could explain why transaction log backups are not being deleted.
The log files will be on the primary server till i will take a full backup on the primary server.
Thank you all!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply