Home Forums SQL Server 2005 Backups DBCC SHRINKFILE to clear the log file RE: DBCC SHRINKFILE to clear the log file

  • If you're in simple recovery mode the log automatically truncates at checkpoints. There's nothing you need to do.

    The log file contains inside it a number of log segments. These store the log records for SQL.

    When a log truncation occurs (either due to a checkpoint in simple recovery or a log backup in full/bulk logged) any log segment that does not contain active log records (records for data changes not committed to disk or needed by replication or database mirroring) is emptied and that space within the log file is marked for reuse. Truncation does not change the size of the log file on disk.

    When the log file is shrunk, the size of the log file is reduced to the size requested, or the minimum size to contain all in use log segments in the log

    Make sense?

    If there's nothing preventing log reuse, then the growth of the log file could be due to large transactions, eg index rebuilds or bulk loads. However it shouldn't keep on growing if you're in smple recovery, unless there's an open transaction or transactional replication.

    Check the log reuse after the data load on thursday morning please, and again before and after the backup on thursday evening.

    You can also use DBCC SQLPERF(LogSpace) to see what % of the log file is in use.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass