Everyone in PASS and everyone following the #sqlhelp hashtag on twitter was a SQL n00b at one point. That includes me. So when I started compiling my SQL101 material into a book, it dawned on me. I should include those things I wish I’d known sooner about SQL.
So I asked anyone who would answer. What would you want to see in a SQL 101 book when you first started? What do you wish you had known sooner. Those answers have grown into another series here on the site.
Today’s topic was suggested by Melissa Coates (twitter| blog). “I wish I’d known why my transaction logs filled up, once I switched my database from simple to full restore mode.”
A few weeks back I covered the different recovery models in preparing for the 70-432. But what I didn’t cover is what happens with your logs in these different recovery modes.
In Simple Recovery Mode…
Each transaction is written to the log file and the log file will grow to record that transaction. But as soon as you commit that transaction (or rollback). That space is freed. There are a couple reasons why the size of your log file wouldn’t fall back to nothing.
1 You pre-sized your log file. I have set some of my log files to a specific size, say 5GB. The reason for this is every time we run a large load, it would grow to almost 5GB, and a lot of the waits in this process were waits due to having to enlarge the log file. Not to mention it was creating a bunch of Virtual Log Files (VLFs) which create other issues I’ll cover in a future post.
2. You have open transactions. The space cannot be recovered if you have a transaction that has not yet committed. You might want to check to see if you have open transactions using DBCC OPENTRAN.
Otherwise when using Simple mode, you don’t have to worry too much about log file space, unless you have wild fluctuations in the amount of data you’re pulling in and moving around in transactions.
In Full Recovery Mode…
And to a lesser extent the Bulk logged mode, your log file will continue to grow, since it doesn’t free up space after each transaction. That’s because your server is waiting on you to backup the transaction log. It’s saving it for you… just waiting for you to off-load it to somewhere other than the server itself.
That’s because you’ve told it you want to be able to return to a specific point in time. In order to do that, it’s got to keep track of all your changes, so you can undo them and return to that point in the transaction log. (I am simplifying here.)
The reason the logs appear to run amok is they get changed from Simple mode to Full Recovery mode, and you forget to set up a backup strategy for your transaction logs. In a future post, I’ll cover how to create a maintenance plan to backup your transaction logs on a schedule. For now, just know if you’re going to set your database to full recovery mode, you’re going to want to set up a backup strategy if not before changing recovery modes, then immediately after you make the change.
That’s it!
That’s why your logs are growing wildly. If you have any problems, let me know. I’m here to help. And if you have any other topics you’d like to see added to the “I wish I knew about X sooner”, send those in too! I’m always happy to share!