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 .
MD SHAMIMAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
In prod  full backup starts at 12.02 and completes at 4.00am
That's only for one database? How big is the database?

but the transaction log doesn't get truncated by backups
Truncated or shrunk? Transaction log backups truncates the tlog files (zeroed the file) but if what you want is really to shrink the file then you'll need to explicitly run a DBCC SHRINKFILE command after the backup.

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
And what if for some reason you'll need to restore to that point in time? How you'll do it without having the necessary backup files?
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MD SHAMIMAuthor 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 .
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
yeah only for one database .Database size is about 700 GB .
I can't imagine in these days a backup of 700GB database taking 4h to complete. Is this an old system? If not, it's something running at the same time that may slow down the backup time? I would expect something like less than one hour to complete a full backup for that database.

for now we are having space issue .cant keep solid two full backups
How much space to you have for backups? How your backup plan looks alike? You may need to review the backup plan to something better.

For temporary solution i thought good idea is to delete t-logs files after full backup completes
Why not add more disk or use some network share for backups?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

MD SHAMIMAuthor 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 .
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
yeah its pretty old server .SQL server 2008R2 .we do not use any third party software
I managed for several years a SQL Server 2005 instance with a 11TB database and full backup ran around 12 hours, so almost 1TB per hour. The network and storage speeds are also important.

drive is 2.18TB .problem is T-log and full backups are using same drive
Can't see big problem on that. Would be a problem if you're backing up to the same drive where database data and log files were stored.

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
Was taking the same time but how about the space? Was less? And if you have a SQL Server 2008R2 you should be able to perform compress backups. In average will reduce the backup size and time to 3 times less.

By the way, you should assure that during the Full Backup period, no transaction log backup is launched, i.e. transaction log backups should run from 05:00AM to 11:00PM, leaving the remaining period to Full backup.
1
Vitor MontalvãoMSSQL Senior EngineerCommented:
MD SHAMIM, a feedback will be appreciated.
Cheers
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comments as solutions.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.