Home Forums SQL Server 7,2000 Backups Cannot shrink log file 2 (LOG FILE) because all logical log files are in use. RE: Cannot shrink log file 2 (LOG FILE) because all logical log files are in use.

  • Ol'SureHand (1/15/2009)


    Nevertheless, if databases are small enough to back up in full and we don't have to offer point-in-time data recovery, the log can be truncated every night as it is useless after a successful full DB backup and integrity check.

    If the DB is in simple recovery, there's absolutely no need to explicitly truncate the logs. That occurs at regular intervals. In simple, the only reason for the log to grow would be faulty replication or long lasting transactions. Index rebuilds fall into the latter.

    As for shrinking it, only shrink a log if you know that it won't reach that size again. log grows are expensive and slow the DB down for the duration. Plus repeated shrink/grow causes fragmentation at the file level, which is hard to fix.

    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