• Log truncation does not shrink the file size, it just marks the parts of the log file free for reuse. You have to explicitly shrink the log file. Don't shrink to a size that is smaller than usual log size for that DB (e.g. 1 GB), because you will waist IO by needless shrinking and growing back to normal size.

    In simple recovery model there are two conditions that transaction log (VLF = virtual log file, a logical unit of a transaction log that can be marked for reuse) is truncated:

    1) there are no active (uncommitted) transactions in that VLF

    2) CHECKPOINT has occurred

    In full recovery model there is one more condition:

    3) log backup has occurred

    In simple recovery model your log will grow e.g. if you have a very long transaction that heavily modified the db.

    HTH,

    Vedran

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths