SQl Server Log Files, what do they do?

OK, so this might be a dumb question, but I’m wondering what the purpose of the log files associated with a SQL Server database are used for.

Now, I know that you can initiate a transaction with checkpoints from which you can rollback. I also know that any modification happens through the log so that it can automatically rollback should the process fail to complete.

But, unless you adjust the settings to truncate the log periodically, it seems to keep growing and growing. Why is this? It’s not as if you can recover from mistakes on occasions when you don’t open a transaction, and once something is committed it’s pretty much done. Why does the log maintain all that data, and considering that it does…why can’t you rollback from it even without a open transaction? Am I missing something?

It shouldn’t keep growing forever. As transactions get flushed to the disk, the log segments become inactive and then written over as new transactions are written.

The only time that the T-log will get larger and larger without truncating is when there isn’t an opportunity for the lazy writer to write the transactions to the disk, such as when you are performing one massive transaction.

Zev Steinhardt

I realized I might not have explained fully enough.

In SQL Server, when transactions are committed, they are not written directly to the disk. They are stored in the log.

There is a process in SQL Server called the Lazy Writer. (Try running this: Select * from master…sysprocesses where status = ‘runnable’ On occasion you’ll see a process called Lazy Writer"

What the LW does is, when the server is not busy, writes transactions from the log to the disk. Once all the transactions in a log segment are written to the disk, the segment is marked as inactive and is eligible for overwriting.

Zev Steinhardt

I’m not sure about that answer - I deal regularly with a database that is about 500M (compressable to 250). When I first started working on it, the log file associated to it used to take up 10G. After some unrelated speed issues, this size was discovered and we started trying to cut it back. We’re not super-experts on MS-SQL DB administration, but after scouring the help file and the internet - shrinking didn’t work, detatching and bringing back online, etc. Eventually we discovered how to truncate the log, and brought it down to a much more acceptable 20M, from which it does grow slowly.

This DB is an over-qualified quad box and doesn’t often run over 10% on any CPU, so… perhaps there’s more to say?

Once a log file grows to a certain size, it won’t shrink again because the transactions have been flushed to the disk. It will just be marked as such and eligible for overwriting. In order for the file to shrink, you must actively do that. (I think there is a DBCC SHRINKFILE command, but I don’t remember off the top of my head).

So, if you’re constantly writing new transactions to the log when the server doesn’t have a chance to write them to the disk, then the log file will grow.

One solution is to use the CHECKPOINT command, which tells the server to actually write the transactions to the disk.

BTW, you don’t want to arbitrarily truncate the log if you want to be able to guarantee a recovery to the point of disaster.

Zev Steinhardt

Yeah, that shrink command didn’t work for use.

BTW, recovery was part of the OP - how does the transaction log file help you ‘recover’ anyway? If I back up the DB and truncate the log file, it would seem I’m covered for any conceivable disaster, no? Is there some other way of fixing a DB using the transaction log file?

If you do a full backup, then you’re OK up to the point of the backup.

Let’s take an example.

Suppose you did a full backup at midnight. The log is truncated and everyone is happy. Users begin using the system.

Now suppose at 12:30, you see that the log is getting full and decide to dump it. Then at 1:00, your system crashes. At this point, your only option is to restore the backup from midnight. But that means that you’ve lost an hour’s worth of transactions.

Now, suppose you didn’t truncate the log. What would happen is that when the server comes back up, it will look to the transaction log and start applying (roll forward) all the completed transactions in the log until it gets to the point where the log ends (the point of disaster). It will then roll back any uncompleted transactions. All this is done automatically. You shouldn’t need to do anything with the log file (except maybe back it up once in a while).

Zev Steinhardt

So, if you execute a command without opening a transaction explicitly is there a way to undo it using the transaction log?

No.

Whenver you issue a request against the server, it’s a transaction whether or not you explicitly declare it. The only thing that explicitly declaring a transaction does is ensures that a group of statements will succeed or fail together. A statement issued without a transaction is implicitly a transaction by itself.

There isn’t anything that you can do directly with the transaction log. It’s strictly used by the server.

Zev Steinhardt

The logfiles depend on the “recovery mode” of the database.

The options are Simple, Full and Bulk Logged.

Consult the SQL server documentation for more information on these modes but, in short:

A database with Simple recovery mode will tend to maintain smaller logfiles since unneeded space is reclaimed. The log is reset at the point the database is backed up, meaning that any database that is backed up daily (like any production database should be at a minimum) should never have a very big log. The disadvantage of this is that you can only roll back transactions to the point of the last backup.

The Full model preserves a record of all transactions that occur in the database. This means that, so long as the log is never truncated, it is possible to roll the state of the database back to any point in time since it was created.

The choice of recovery model depends on how important the data passing through the database is. If it is vital that no transactions be lost, even in the case of the file becoming corrupted, then Full is the only option. If it’s not quite so vital then Simple is the better choice since it’s a lot less hassle.

Search for the article “Selecting a recovery model” in the SQL Server Books Online collection as a starting point for more information.