question

shamim007 avatar image
shamim007 asked

cleaning up T Log backups at the end of a full backup

Hello Experts , our full backup starts at 12.02 and completes at 4.00am .our full backup have all data in it and also getting t-log backup files same time .but the transaction log doesn't get truncated by backups .we can delete all t-log files from 12.02am -11.30 pm .or 11 pm . for cleaning up T Log backups at the end of a full backup, what would the TSQL look like? My full backup job query is : DECLARE @fname NVARCHAR(100) = N'V:\Backup\MYDB_backup_' + CONVERT(NVARCHAR(10), SYSDATETIME(), 112 ) + N'.bak'; BACKUP DATABASE [MYDB] TO DISK = @fname WITH COMPRESSION, STATS = 10 DECLARE @backupSetId AS INT SELECT @backupSetId = position FROM msdb..backupset WHERE database_name=N'MYDB' AND backup_set_id = ( SELECT MAX( backup_set_id ) FROM msdb..backupset WHERE database_name=N'MYDB' ); IF @backupSetId IS NULL BEGIN RAISERROR( N'Verify failed. Backup information for database ''MYDB'' not found.', 16, 1 ) END RESTORE VERIFYONLY FROM DISK = @fname WITH FILE = @backupSetId how do i add cleaning up T-log backups at the end of a full backup ? we are getting t-log backups every 24 minutes and getting full backup at night .
sql-server-2008-r2backup
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
JohnM avatar image
JohnM answered
Are you deleting the transaction log backups for the previous day after the most recent full? If so, I'd be cautious in doing that as if you needed to restore to a point in time you won't be able to. Most shops keep things around for a period of time (a week, month, etc) before getting rid of them. I would suggest looking at Ola Hallengren's solution to help facilitate things. No need to re-invent the wheel when someone already has a tried and true method to do it for you. Ola's stuff is rock solid and many organizations use it for managing backups and history retention. https://ola.hallengren.com/sql-server-backup.html Hope that helps! Hope that helps!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.