Transaction logs arent being truncated after backups

Question

Transaction logs arent being truncated after backups

hurricaneDBA

SSCertifiable

Points: 5027
More actions
October 5, 2016 at 6:02 am

#331190

Dear DBAs
I am running the following TSQL to backup my transaction logs once a day
Yes i know you are to run them every 30 mins to 1 hour but if i run them once a day using the TSQL below wont the log get emptied out of the transactions?
I checked the file size using
SELECT name, size FROM sys.database_files;
GO
Size - 141072
BACKUP LOG [PTW3] TO DISK = N'G:\TL\PTW3.TRN' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
I checked the file size using
SELECT name, size FROM sys.database_files;
GO
Size - 141072
The size is the same before and after the log backup
What am i missing? Do i need to add another option?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Log backups truncate the log, as in mark the log space as reusable. The do not reduce the size of the file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 2

Thanks for the reply Gail

I have 3 txn logs which have reached 3 GB.

What's the recommended route?

I know shrinking isn't advisable but in scenarios like this shall I shrink them and reorganize the table / indexes?

Kal

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

hurricaneDBA (10/5/2016)
Thanks for the reply Gail
I have 3 txn logs which have reached 3 GB.
What's the recommended route?

Leave them alone is the usual recommendation for log files

I know shrinking isn't advisable but in scenarios like this shall I shrink them and reorganize the table / indexes?

Why?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

And why are you only backing the logs up once a day? The reason that logs get backed up is for recovery purposes, so that the database can be restored to point of failure. With backing the log up once a day, you're exposed to up to 24 hours of data loss.

Is 24 hours of data loss acceptable?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 5

It's done once a day due to space limitations at work

Ill discuss it with management to run them every hour.

If I setup the backups to run every hour then I'll have to keep all 5 log files for PITR correct?

Having files of 2-3GB will give me 20gb for file and that's not acceptable

To answer your question NO it's not acceptable to have them run once day

Thanks

Kal

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

hurricaneDBA (10/5/2016)
It's done once a day due to space limitations at work

Doesn't make sense. The total size of the log backups will be about the same whether they're backed up hourly or daily, the volume written to the transaction log by SQL doesn't change, and so the total size of the backups combined will be about the same (each just contains the log records written since the previous.

If I setup the backups to run every hour then I'll have to keep all 5 log files for PITR correct?

5??

Having files of 2-3GB will give me 20gb for file and that's not acceptable

20GB for what?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 7

Last question

When backing up TLs every say hour is it best practice to append the files into one TL file or keep them as separate files?

Kal

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Separate files.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 9

hurricaneDBA (10/5/2016)
It's done once a day due to space limitations at work
Ill discuss it with management to run them every hour.
If I setup the backups to run every hour then I'll have to keep all 5 log files for PITR correct?
Having files of 2-3GB will give me 20gb for file and that's not acceptable
To answer your question NO it's not acceptable to have them run once day
Thanks
Kal

You need to talk to the business. They have to decide, it's not a technical decision, how much data are they willing to lose. 24 hours? Great, just use regular full backups, set your recovery model to simple and toss the log backups. 1 hour, or less? Cool. They need to buy you a hard drive to store the stuff on. These are not technical issues, and you should not be making this decision in a vacuum. If you get a failure, and they do happen, you will be held to account for why they can't recover the amount of data they expected to recover. I strongly recommend you have a serious discussion with them so that they understand what decisions you're making on your own. I wrote this blog post [/url]on working with the business to set RPO and RTO for your service level agreements.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning