Backups and shrinking the log file 2

[JoeAtWork]

There have been a number of times I have come across SQL Server databases where the transaction log files have become enormous (many times the size of the actual MDF file). The last one I dealt with grew to 50GB.

I have been reading a number of articles about truncating and shrinking, but I'm still not clear on why this happens.

I fully expect the log file to grow proportionately as the MDF file gets bigger, but what I see is it growing many times larger than the MDF.

Is there a certain maintenance command that should be applied to the database to prevent this bloating of the transaction log file? At one time I thought that running a BACKUP had the effect of truncating the log, but this doesn't seem to be the case with these particular databases.

The articles I read indicate the bloating happens because of poor code, such as when transactions are not closed. My impression is that if the database and application are well designed this bloating does not happen. Is this correct?

 

[mrdenny]

I fully expect the log file to grow proportionately as the MDF file gets bigger, but what I see is it growing many times larger than the MDF.

Log file growth doesn't have anything to do with the growth of the data file. Each update to a record will add data to the transaction log without increasing the size of the data file.

At one time I thought that running a BACKUP had the effect of truncating the log, but this doesn't seem to be the case with these particular databases.

The BACKUP DATABASE command doesn't truncate the log. The BACKUP LOG command does.

If you don't need to have the ability to do point in time restores then change the recovery model of the database from FULL to SIMPLE. If you do need the ability to do point in time restores then backup the log regularly.

When database maintenance is run the transaction log will grow large. If it happens regulary then don't bother to shrink the file. Shrinking the file just adds un-needed load on the disk for no reason.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JoeAtWork]

So let's see if I understand the behaviour now.

If no BACKUP is ever done, the MDF would remain the same size while the LDF keeps growing? I always thought that at some point, like when a transaction is completed, the data would get transferred from the log file to the data file. I thought this occurred whether or not a BACKUP occurred.

If I change to Simple recovery mode (for most of my applications a daily "snapshot" of the database is good enough for backup purposes), will space from the log file be reclaimed when a database BACKUP occurs, or do I have to still do a log file BACKUP as well?

 

[SQLSister]

The log file will grow until it is backed up. That is a separate backup from the database backup. Changing to simple recovery mode will slow the grwoth of the transaction log, but it still won't clean it up.

search for:
transaction logs, truncating
transaction logs, backing up
shrinking transaction logs

in Books Online. you really need to read these to understand what is happening.

"NOTHING is more important in a database than integrity." ESquared

 

[JoeAtWork]

Ah, thank you. I have been reading BOL, but it doesn't describe the link between backing up and the physical growth of the log file. Or maybe it does with "truncate" but I wasn't sure if that meant that the space would be reclaimed.