October 3, 2013 at 7:37 am
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;
October 3, 2013 at 7:39 am
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
October 3, 2013 at 7:44 am
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;
October 3, 2013 at 7:47 am
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
October 3, 2013 at 7:57 am
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
October 3, 2013 at 7:58 am
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;
October 3, 2013 at 8:02 am
October 3, 2013 at 8:03 am
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
October 3, 2013 at 8:03 am
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
October 3, 2013 at 8:14 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply