SQL Backup Transactional Logs not Truncating.

[georgep]

Hey guys,

Our 3 SQL servers were going nuts on transactional logs. They were never truyncating so I had to put it to Simple mode and Shrink logs manually. Now I want to prevent that. We are running a nightly veeam backup job on these SQL servers.

What's the requirements for veeam to truncate them ? Does the database has to be in Full Recovery mode or Simple ?
We are running a reverse incremental job on these SQL servers

Thanks a lot.

[Gostev]

Make sure you are running the latest version, have application-aware processing enabled in backup job options, and the option to truncate logs on successful backup in the advanced settings on the same wizard step is enabled. Then it will work fine. If not contact support for further troubleshooting.

Log truncation is only needed with Full Recovery mode.

[georgep]

so if I leave the Recovery option to Simple I don`t have to shrink logs ? They don`t grow ?

[Vitaliy S.]

They grow, but with simple recovery mode SQL Server maintains only a minimal amount of information in the transaction log. And it truncates the transaction logs each time the database reaches a transaction checkpoint.

[Gostev]

Simple is actually the way to go if you do not do anything with the logs.

[georgep]

thanks a lot guys.

so when does SQL reach "the database reaches a transaction checkpoint. ? " because i think we had some simple and they still grew a lot.

[Vitaliy S.]

Take a look at this Microsoft's article for the answer: http://msdn.microsoft.com/en-us/library ... l.90).aspx

[georgep]

wow i hate these logs. My DBA inserted lots of rows and the log grew more than 25GB.. Is there a way to make the log no more than 1 GB cuz i really don`t care about them...

like limiting their max size stuff ?

[Gostev]

Sure, but it is not recommended by Microsoft AFAIK. All this will do is decrease SQL performance, and nothing on the positive side except of a few GB saved.