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

MD SHAMIM
MD SHAMIM used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Engineer
Distinguished Expert 2017
Commented:
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?

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
Distinguished Expert 2017
Commented:
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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
Distinguished Expert 2017
Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

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

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial