truncate transaction log

Question

truncate transaction log

SGT_squeequal

SSCertifiable

Points: 7167
More actions
February 23, 2016 at 4:36 am

#324446

I have seen may conversations around truncating transaction logs and as a rule i do not do this. I have recently inherited a a product that has a SQL database and the software Vendor has advised that we regularly truncate the transaction log to stop performance issues.
From my understanding of transaction logs, one would never need to truncate a transaction log?
***The first step is always the hardest *******

Viewing 4 posts - 1 through 4 (of 4 total)

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

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

Lol, the vendor is talking garbage.

Log backups truncate transaction logs in full/bulk-logged recovery. Checkpoint truncates the log in simple recovery. If you're in full/bulk-logged and doing regular log backups you don't want to mess with the log because it will limit your ability to recover from a disaster. In simple recovery you don't need to mess with the log, it's automatically made reusable on a regular basis.

In fact, the option to manually truncate the log (Backup log ... truncate only) was removed in SQL 2008.

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: 399278 More actions · Answer 2

Is recovery full? If so, as much as I dislike this, change it to simple. If you're dumping the logs you can't do a full recovery anyway. Might as well eliminate the pain (along with the ability to restore a point in time).

I work for a vendor, so please, keep that in mind when I say this...

Vendors are frightfully ignorant.

"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

Jon.Morisi SSChampion Points: 12846 More actions · Answer 3

A LOT of vendors get really confused about the word "truncate". 1/2 the time when they say truncate they mean, "take a LOG BACKUP". You might just want to verify with them that you're both on the same page with regards to the definition of the word, "truncate".