Restrict log file growth

Question

Restrict log file growth

Mau-660849

Old Hand

Points: 311
More actions
June 20, 2008 at 9:12 am

#94210

Good morning everybody...
I have a database in size around 50GB, the transaction log sometimes increases in several GB, sometimes until 30 GB specially in weekends because this database stores everyday all the transactions of the day before.
The log file has enabled in the option MAXIMUM FILE SIZE, "UNRESTRICTED FILE GROWTH", my questions is, if I change this option to "RESTRICTED FILE GROWTH", I could change it for example to 5000 MB to restrict the file growth to 5GB? what happens inside the log if I make many transactions? internally the log when gets fulls drops the older transactions in the log if I have it now restricted?
Thanks a lot for your attention and any comments, 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

You haven't supplied a critical piece of info to fully answer your question (your recovery model). that being said, given your scenario, it sounds like your transaction log needs to grow up to 30GB at times, so restricting the log size is going to be a bad thing. If you do so, and the DB runs outs of log space, processing will essentially stop until you give it some more room to work in.

The bigger question might be - why are you shrinking the logs at all? Forcing the logs to grow from 5GB to 30GB in multiple increments is a costly operation, and will slow things down during the times when the growth is occurring. Much better would be to leave it at 30GB, and not shrink it (I'd actually bring it to 35GB depending on how full the 30GB transaction file ends up being.

It's better to make that transaction file big, and give it plenty of room than to make it shrink and grow (which promotes fragmentation anyway, never a good thing).

On the other hand - it sounds like you're not backing up your transaction logs enough (assuming you're on a FULL recovery model). Backing them up more than just once a day would make for substantially smaller chunks getting backed up, and ultimately a somewhat smaller transaction file being required.

In a full recovery scenario, committed transactions "recycle" only once they've been backed up and have successfully been replicated (if that's relevant). So - the frequency of your log backups is directly related to how big the log file ends up having to be.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Mau-660849 Old Hand Points: 311 More actions · Answer 2

Thanks a lot for your answer,

The recovery model is the FULL one, and you are right I should think about changing the backup planning, so I am going to do it more often, everyday, and I am going to consider not restricting the log growth and checking the LUN size, maybe I didn't know that if I restrict the growth could I get into those kinds of problems.

Thanks a lot for your suggestions, 🙂

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 3

I agree with Matt. Set the logs at their peak + pad, and leave them. There's no reason to shrink them. As you back them up (or checkpoint in simple mode), the log space is reused.

You want to set your data and log space to a size it will need for the next xx months. Could be 3, 6, or 12, depends on the system, but then manually grow things as needed.

Mau-660849 Old Hand Points: 311 More actions · Answer 4

OK,

Thanks a lot for your comments,

I was not sure if restricting growth is a good idea so now you give me good suggestions, thanks!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Mauri (6/20/2008)
Thanks a lot for your answer,
The recovery model is the FULL one, and you are right I should think about changing the backup planning, so I am going to do it more often, everyday, and I am going to consider not restricting the log growth and checking the LUN size, maybe I didn't know that if I restrict the growth could I get into those kinds of problems.
Thanks a lot for your suggestions, 🙂

Just remember that we're focusing on the LOG backups. It's very common to keep the database backups on a daily schedule, but run the log backups much more often, sometimes as often as 15 minutes.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?