Thread: Transaction Log Management
12-13-07, 11:53 #1Registered User
- Join Date
- Nov 2006
Unanswered: Transaction Log Management
I'm new to MS SQL and would like to get your opinion on how it's possible to automatically manage transaction log growth. I've read the following on the topic:
"When Microsoft SQL Server finishes backing up the transaction log, it truncates the inactive portion of the transaction log. This frees up space on the transaction log. SQL Server can reuse this truncated space instead of causing the transaction log to continuously grow and consume more space. The active portion of the transaction log contains transactions that are still running and have not completed yet."
I'm backing up the transaction logs on a daily basis. I believe that all or most of the portions of the log are inactive by the time the backup starts. Still one of the logs grew up to 130Gb even though I have shrunk it in the past. How can I manage the growth automatically without shrinking the log files manually every time they grow beyond a certain threshold? Also, how can I check whether some of the log portions are active or inactive?
Thanks in advance,
12-13-07, 13:50 #29th inning DBA
- Join Date
- Jan 2004
- In a large office with bad lighting
If yoiu shrink a tran log, and it grows again, there is some process that runs that needs the additional space.
I would recommend leaving it at the max growth if you can, since the overhead involved in allocating and preparing new data space (whether it be for the data file(s) or log file(s) will have an adverse affect on running processes ... and the more it has to allocate, the more you will notice it.
-- This is all just a Figment of my Imagination --