Admin: Sizing your transaction log

You will often hear DBA folk telling you how you need to size your transaction log appropriately and, like data files, avoid shrinking it. Why is this?

Well, lets try using a finance theme with a Credit Card analogy which records all your purchases but it also has a limit. When you fill your credit card and reach your limit you either need to pay off the balance or extend the limit by asking/begging your issuer for a bigger limit. Its possible of course (and will probably be successful) but will take up some of your time and may involve the provider asking some awkward questions. Of course, there may be times when you need to extend your limit, perhaps when you're moving house but there is a cost to it and its something you want to avoid doing too frequently. Hopefully when you set up your credit card, you do so with an appropriate limit based upon knowledge of your spending habits.

All of this is the same with a transaction log. It too has a limit which is its size and when it gets full it will either stop taking transactions or ask the operating system for more space (if set to Autogrow). Think of auto grow as akin to asking your credit card provider for that extension which will most probably granted but not without effort, here the time it takes to provision the file growth. While the file is growing, transactions will be queued up meaning your application can't process its requests and things will start to get ugly. Alternatively, rather than extend the limit of your log, you can perform a transaction log backup and "clear the balance" or if your database is in SIMPLE recovery mode, this behaviour is done on checkpoint.

Your ideal scenario is to have auto grow set on your log but with a maximum size set on the file (you don't want this credit card brining down the whole of the bank!!) and ideally it will be sized at a level which won't require too many file growth operations. You want to avoid shrinking the physical file as you don't want to repeatedly go through the "limit extension" process as its just unecessary cost. If you know your app needs 10GB, then just set it at that.