cleaning up T Log backups at the end of a full backup
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 .
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 .
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 .
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 .
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 .
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 .