What about the Sql transaction log

Question

i always thought that the sql transaction log keeps track of all the transactions done in the database so it could help recovering the database file in case of a unexpected power down or something like that So then, in normal usage, when the data is committed and written to disk, it is cleared because all the data is nice and safe in the mdf file. Seeing the ldf file grow and reading some i understand that that is not the case, and it will keep growing, until: you shrink the log. Only at that point all the commited transactions are cleared and the log file is shrinked. I found some sp's who should do this, but also found the theory that you first have to backup the database? That last step doesn't make sense to me, so can anyone tell me of that is correct and if so, why that is?

Answer 1

Imagine you (or someone) fires a delete from very_important_table in your production database. Sql Server happily deletes along, commits the transaction and would, as you suggest, "forget" about the transaction.

Later on someone will note the data is gone (after all this was your very_important_table). You would be thankful in this situation Sql Server did not forget about the transaction so you can restore your database to the point in time just before the (unintentional) delete! At least (as mentioned) if you use the "Full Recovery" model.

This is among other things why the transaction log keeps all this information around.

Now, when would it be "safe" to chuck the log? After you made a backup - hahaa! Sql Server assumes that you keep your backup in a safe place and this means it does no longer have to hang on to the log. This is why you have to make a backup before you can shrink your transaction log.

But I believe that shrinking the transaction log should be something for emergencies. In normal operation you should "know" (or guess) how big the transaction log will get in a certain amount of time (read: in the intervals between log backups!) and assign it that much space (plus some headroom might be a good idea). Then you disable auto grow (or tweak it at least) and set an alert for when the transaction grows "too big" (let's say 80% of what you set as size if you gave it 50% or 100% headroom). Then you have time to react if something goes wrong.

Remember (as others wrote): the (log) backup will not "shrink" the log file, it just "empties" it. You will still see the same file size on the file system but there is free space in it (DBCC SQLPERF(logspace) will show you this).

If you have auto grow enabled the log will just fill your disk without you knowing and when the disk is full - bam - nothing you can do (in the worst case not even backup or shrink the log!).

What I am trying to say is the following: this is not an easy topic. Be aware!

Maybe this Microsoft article will provide a starting point: How to Stop the transaction log of a SQL Server database from growing unexpectedly

Edit: Would this question be better suited for serverfault?

Answer 2

FYI: When you shrink the transaction log only frees up space for new transactions, it does not reclaim space on the file system.

Also, as the other post mentions as I type this, recovery model plays a big role because SQL Server will truncate the transaction log at every checkpoint.

Answer 3

you have database state x, then you add transactions y, then you shrink transaction by factor of 2, so you would only have x + (y/2) database state. unless you have a backup, you have no way to account for that missing half of y. in other words, always take a backup. the only real exception is if you are duplicating the database as records are entered, and you can pause such a duplication process while shrinking transaction log.

Answer 4

To restore a database you basically do the following:

• restore your last full backup
• restore incremental backups since last full backup
• restore all transactions recorded since your incremental full back up was taken

for that reason, the transaction log is only needed to hold a record of all transactions spanning the time since you last took a "snapshot" of your data (i.e. last full backup + incrementals since that time), so that together they form an almost complete record to allow a point-in-time recovery, and for that reason you the transaction log entries covering time before you took a backup are not needed.

(That's a bit oversimplified, I know, as you would first backup your transaction log after failure occurred. Plus the fact that your transaction log may well be empty, even though the size of the physical file has not altered).

Answer 5

It depends on the backup recovery model.

Probably everything you want or need to know can be found in this KB article.

By using the simple recovery model, you can recover your database to the most recent backup of your database. By using the full recovery model or the bulk-logged recovery model, you can recover your database to the point when the failure occurred by restoring your database with the transaction log file backups.

Basically what this says is that you either have the simple model where the transaction log is not kept (behaving like you expected it), or the transaction log cumulates all changes since the backup, so that by using the backup plus the log you can reconstruct everything up to the failure point (that is, past the backup moment).