A transaction log, if sized sensibly, is like a ring buffer. It will stay the same size however much data it holds, up to a point. It is when something goes wrong that the log can turn into a disk-space-devouring monster, and my heart sinks whenever I stumble across a forum or Q&A post advocating some dubious hack that will “fix” the resulting oversized transaction log. The emphasis always seems to be on how to make the issue ‘go away’ as quickly as possible by, for example, detaching the database, renaming the log and re-attaching. Sure, you create a new, empty log but it will inevitably grow once more, eating drive space. Often, it is a desperate measure to keep the show on the road, but it is only hiding the symptom rather than tackling the cause. Also, depending on the exact nature of the hack, it can damage the integrity of your log backup chain or even the log itself.

Often, the advice is accompanied by the disclaimer “of course, I wouldn’t do this in production, but it was only a dev/test instance, and hey, it worked“. However, if you find that in development or test you’re repeatedly using a hack to relieve the symptom of a full log then you can bet that the underlying cause will still be present when the application hits production, only more so.

So why is it happening? It could be some aspect of how you’re writing transactions that regularly prevents SQL Server from reusing existing log space, so that it has to continually grow it. Long transactions or bugs that cause dormant-but-still-open transactions are common causes.

Alternatively, it could be your indexing strategy. Frequently modifies columns that participate in multiple indexes can cause SQL Server to perform a lot of extra logging, as can frequent page splits and the fragmented indexes that often result. Bulk data loads can have a massive impact on the log, without due care. Of course, there are other possible causes too, unrelated to the data access and loading mechanisms, such as problems with replication or log shipping and so on.

Whatever the cause, log growth events aren’t free; they eat CPU that your user transactions need. Excessive logging can cause writing to the log to become a bottleneck and the user transactions you care about will have to join the queue.

My advice is this: if log growth problems occur frequently in development and test, don’t simply relieve the symptoms. Have a word with your DBA and try to diagnose any obvious causes as early as possible, before the application hits production. Handling a bloated log in a busy, shared production environment is a much more delicate affair than it is on a test or development server.