Shrinking Log Files -Best Practice

Question

Shrinking Log Files -Best Practice

MTY-1082557

SSCarpal Tunnel

Points: 4540
More actions
May 18, 2010 at 8:21 am

#144712

I would like to know the best practice for Shrinking Log files, including temp database.
I have 2 databases in one Server (SQL Server Enterprise Edition). These databases are only for loading data and Reporting. The Recovery Model is set as “simple” since I only need to do a full back up every week and a differential every night.
I do understand that the backups should “clean” the logs, but they grow pretty fast. I have to truncate them and shrink them once in awhile (maybe once every 3 months). Also, I’ve a process that runs every hour to load data into these databases, so I do the shrinks when the server is not busy at all.
I just want to find the best practice for this behavior. Should I set a different task for better “cleaning”. I see my tempdb growing too.
Thank you

Viewing 7 posts - 1 through 7 (of 7 total)

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

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 1

Well....

If you are in simple mode the log is generally that big because that is how big it needs to be to complete all of the open transactions at that time. My suggestion would be to set up a job to look how much space is in use during a normal day and use that number as a baseline.

Also I might try shrinking the log and then using performance monitor (or write a script) and watch growth for a while, see if it grows at a specific time of day or day of the week/month.

The size you shrink to is going to have as much as an impact on performance as when you shrink.

Steve Jones - SSC Editor SSC Guru Points: 738663 More actions · Answer 2

I agree with Henry. The log in simple mode grows to accomodate the largest transaction that is open. Then once the transaction commits, that space gets re-used. If the log is regularly growing, either you are having larger loads, or it's not in simple mode.

The log size has nothing to do with the data size. It has to do with the transaction load, and in full mode, the frequency of log backups.

You shouldn't need to shrink the log. Set it to the size that it's needed for the transaction load and then leave it. Monitor periodically, but if you are using that space, you're not helping matters by shrinking it.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Please read through this - Managing Transaction Logs[/url]

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

MTY-1082557 SSCarpal Tunnel Points: 4540 More actions · Answer 4

Henry and Steve

The databases are on Simple mode def. and Yes my loads are every hour and. I load between 1 and 3 millions of records (and it will grow in few months). Since the size of the load data isn’t the same I don’t know what is the exact size I sd set for the log. I just set auto growth in 10MG.

Gil

I read your article, thank you, it’s very clear.

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 5

Why do you have to shrink them? Are you running out of space? Is it a charge back thing?

If you are in simple mode, if anything I would say if you have to shrink them, shrink them completely, you may end up with less fragmentation that way.

homebrew01 SSC Guru Points: 55553 More actions · Answer 6

How about swtiching to Full recovery and taking regular T-log backups ? You may not need the recoverability, but it might manage your t-logs better.