Link to home
Start Free TrialLog in
Avatar of MD SHAMIM
MD SHAMIM

asked on

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 .
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MD SHAMIM
MD SHAMIM

ASKER

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 .
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 .
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
MD SHAMIM, a feedback will be appreciated.
Cheers
Recommendation to close this question by accepting the above comments as solutions.