Truncate Log Files

[mattjurado]

Okay, using Backup Exec SQL Agent to backup a database. Noticed the log file had grown to 1.3gb. I did some research, and I set the database recovery model to simple, hoping this would truncate the log file. I ran the backup again, and the log file is the same size. Is this something that will happen at a later interval?

I backup nightly, and am comfortable with losing a day's worth of data if something happens, not that much is input every day. Just looking to keep the log file down.

Matt J.

Please always take the time to backup any and all data before performing any actions suggested for ANY problem, regardless of how minor a change it might seem. Also test the backup to make sure it is intact.

 

[paul172]

You'll need to shrink the file, as the physical size of the ldf does not change with a truncate.

DBCC SHRINKFILE(dbname_log,yyy)

With y being the size in MB you want the ldf to be.

 

[mattjurado]

Define shrink the file, what exactly happens to the log file, and will it grow again?

Matt J.

Please always take the time to backup any and all data before performing any actions suggested for ANY problem, regardless of how minor a change it might seem. Also test the backup to make sure it is intact.

 

[paul172]

You need to look at the log file in both it's logical and physical size. Setting the recovery model to 'Simple' will reclaim 'logical' space from the log file, and thus the 'logical' size shouldn't grow uncontrollably. However you are still left with the physical size of the ldf to deal with, as this will never decrease without using the above shrink command.

If you open up Enterprise Manager and select the database you use, go to 'view', 'taskpad', this will give you a clearer picture and allow you to see how much logical space your log file is taking up. It will also give you an indication on how much to set your shrink value to!

 

[mattjurado]

Okay, I'll look at that, didn't older versions get truncated automatically if you used backup exec, the physical size i mean?

Matt J.

Please always take the time to backup any and all data before performing any actions suggested for ANY problem, regardless of how minor a change it might seem. Also test the backup to make sure it is intact.