We help IT Professionals succeed at work.

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

67 Views
Last Modified: 2018-08-14
In prod  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 .
Comment
Watch Question

IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
yeah only for one database .Database size is about 700 GB .

i meant  Truncated .log files are still big while full backup is running .


for now we are having space issue .cant keep solid two full backups .full backup job is failing 3/4 times in week .most of the time we are cleaning T-log files manually after full backup completed .

For temporary solution i thought good idea is to delete t-logs files after full backup completes .so the way we gonna have full backup .
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
yeah its pretty old server .SQL server 2008R2 .we do not use any third party software .


drive is 2.18TB .problem is T-log and full backups are using same drive . our full day T-log backup size is almost 765GB .backup plan is full backup everyday and t-log every 24 minutes .do not do diff backup anymore because they told me it was taking same time as full backup and do not have much space to do .

i offered to manager to add more space .all i know its coming but do not know when .
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
MD SHAMIM, a feedback will be appreciated.
Cheers
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Recommendation to close this question by accepting the above comments as solutions.