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 .
Microsoft SQL ServerStorage Software

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
MD SHAMIM

ASKER
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
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

MD SHAMIM, a feedback will be appreciated.
Cheers
Your help has saved me hundreds of hours of internet surfing.
fblack61
Vitor Montalvão

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