Comprehensive data protection for all workloads
Post Reply
georgep
Novice
Posts: 4
Liked: never
Joined: Mar 18, 2011 12:58 pm
Full Name: George pop
Contact:

SQL Backup Transactional Logs not Truncating.

Post by 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
SVP, Product Management
Posts: 26700
Liked: 4275 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Backup Transactional Logs not Truncating.

Post by 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
Novice
Posts: 4
Liked: never
Joined: Mar 18, 2011 12:58 pm
Full Name: George pop
Contact:

Re: SQL Backup Transactional Logs not Truncating.

Post by georgep »

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

Vitaliy S.
Product Manager
Posts: 24249
Liked: 1860 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Backup Transactional Logs not Truncating.

Post by 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
SVP, Product Management
Posts: 26700
Liked: 4275 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Backup Transactional Logs not Truncating.

Post by Gostev »

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

georgep
Novice
Posts: 4
Liked: never
Joined: Mar 18, 2011 12:58 pm
Full Name: George pop
Contact:

Re: SQL Backup Transactional Logs not Truncating.

Post by 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.
Product Manager
Posts: 24249
Liked: 1860 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Backup Transactional Logs not Truncating.

Post by Vitaliy S. »

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

georgep
Novice
Posts: 4
Liked: never
Joined: Mar 18, 2011 12:58 pm
Full Name: George pop
Contact:

Re: SQL Backup Transactional Logs not Truncating.

Post by 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
SVP, Product Management
Posts: 26700
Liked: 4275 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Backup Transactional Logs not Truncating.

Post by 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.

Post Reply

Who is online

Users browsing this forum: Baidu [Spider], Google [Bot], mrmccoy007 and 44 guests