SQL Transaction Log

Question

SQL Transaction Log

jason123

SSC-Addicted

Points: 455
More actions
October 3, 2013 at 7:37 am

#395601

Hi everyone,
My transaction log file is bigger and bigger every day, can I truncate the file? If it can, does it affect any thing such as performance?
Best regards;

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

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

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 1

What is the recovery model of the database? If it is FULL then consider scheduling a transaction log backup to manage the transaction log.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

jason123 SSC-Addicted Points: 455 More actions · Answer 2

Hi,

Thank you, it is the full recovery mode. I already configured the schedule to back up transaction log. Could you please clarify why do we need to configure the schedule for this backup?

Best regards;

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 3

How often do you back up your transaction log? I have t-log backups that runs on frequencies of from 15 minutes to 1 hour.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

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

Please take a read through this: http://www.sqlservercentral.com/articles/Administration/64582/

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

jason123 SSC-Addicted Points: 455 More actions · Answer 5

Hi,

Thank you, it is configured for every 3 hours. Sorry could you please explain what are differences between configuring the schedule to back up transaction log and truncate t-log?

Best regards;

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 6

You probably don't have time right this second, but this book (SQL Server Backup & Restore)[/url] is very good, and the pdf version can be downloaded for free.

The reason people are asking how frequently you back up the log file is that backing it up helps to control the growth.

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

jason123 (10/3/2013)
Sorry could you please explain what are differences between configuring the schedule to back up transaction log and truncate t-log?

That's in the article I referenced.

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 8

jason123 (10/3/2013)
Hi everyone,
My transaction log file is bigger and bigger every day, can I truncate the file? If it can, does it affect any thing such as performance?
Best regards;

Hi,

You should do backups of your Transaction log on time intervals usually somewhere between 10-60 minutes. This depends on other factors as well. Additionally your TLog autogrowth is important for the virtual log files inside, which number is given with the following:

Growth No. VLFs

Chunks <= 64MB 4

Chunks in range (64MB - 1GB] 8

Chunks > 1GB 16

DBCC LOGINFO(<DB_NAME>) will give you the number of VLFs. You usually keep this number as possible as lower (again depends on the system).

For detailed analysis of the TLog just search this site, or google and you'll find a lot of stuff.

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 9

GilaMonster (10/3/2013)
Please take a read through this: http://www.sqlservercentral.com/articles/Administration/64582/

I agree, Gail's article has all of the details you need to know about managing transaction logs. Now if you are experiencing an unusual growth or rapid growth in your transaction logs, if I were you, look to see what has been added or is new.

If an increased level of transactions are hitting your database then you may want to rethink your transaction log backup strategy so you have not only better management of the transaction log but also have a more granular recovery model in the event something were to go belly up.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman