Myth : I don't need point in time recovery so I don't need the transaction log

Wrong, wrong, wrong.

This post was in response to a post in the forums "How do I delete the log file?"

The transaction log for a SQL Server database is not a log like the ones many applications produce that is just history of the stuff you've done in the application, say for debugging. Whilst it is a record of the changes you make to your data it is a critical element in how data is kept consistent in your database database, i.e. not corrupt.

Lets start by going over a few basics. A SQL Server is made of tables, the data for a table is stored on a page. These pages are stored in the data files (mdf/ndf ).

When you make changes to rows in a table, the pages containing those rows are read from disk and put into memory. The changes are recorded in the transaction log and are then the pages changed to reflect the chanegs you've made. The pages are only change IN MEMORY, not in the data files. If at this point your server dies, your changes that are currently only in memory and not in the data files will have been lost. However they are in the transaction log. So when SQL Server starts a recovery process is run. This can be seen in the errorlog. This process looks for transactions that are in the transaction log that didn't get harded to the data file and it makes the changes to the data file. So you don't loose your data.

There is an additional process that runs called the lazy writer, what this does is free pages from cache when under memory pressure. This means that the changes you have made to pages in memory may be hardened to the data files. Great you might think thats what I want. However this can also happen in the MIDDLE of a transaction. This means that you could have changes in your data files that have yet to be committed. In this case if SQL Server crashes mid transaction, when the recovery process runs it looks for changes that were made and recorded in the transaction log and hardened to disk but for which the transaction never completed. These changes are then rolled back thus leaving your data files in a consistent state.

So even if you don't need point in time recovery you still need your transaction log.

The key thing is that if you don't need point in time recovery then you should set your recovery model to simple. What this does is frees the space in the transaction log of committed transactions and so your transaction log shouldn't grow.

One note: If you have a single transaction that requires 10Gb of transaction log space then using simple recovery won't help. For example, updating every row in a very large table in one statement, this is one transaction and so the transaction log needs to be big enough to hold the changes. If space on the drive for the transaction log space is problem then you need to look at batching up your changes into smaller transactions, that once committed can be truncated from the transaction log. See my posts about DELETE and UPDATE with TOP

Published Wednesday, May 27, 2009 11:38 PM by simonsabin


No Comments