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?" http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/69c8ecc6-6414-43b1-8c08-21fdf2ca9c54
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 http://sqlblogcasts.com/blogs/simons/archive/2009/05/22/UPDATE-and-DELETE-TOP-and-ORDER-BY---Part2.aspx
-