Saturday, February 2, 2013

Revision : Log file control

Got a Large Log File (LDF) ?

Hopefully your maintenance plans have this all under control but on occasion there might be reason to manually intervene , e,g an unexpected growth in the database or a failure of a FULL backup.
  1. Back it up
  2. Attempt to shrink it
For example :


USE db;
BACKUP LOG db TO db_log_backup;
DBCC SHRINKFILE (db_log);

Hopefully the system isn't so busy that the log gets written to between the BACKUP and SHRINKFILE commands.

NB: Prior to SQL 2008 you could reduce with the tranaction log with
BACKUP LOG db WITH TRUNCATE_ONLY
Others may advocate changing the recovery model to SIMPLE (the transaction log truncted on checkpoint) but this will break the backup chain and you will have to perform a FULL backup again when you return it to the FULL recovery mode.

DBCC SHRINKFILE Gotchas -

TRUNCATEONLY - Removes free space at end of data file

NOTRUNCATE - Moves data within data file, frees pages at end. Does not shrink data file itself.

NB: TRUNCATEONLY & NOTRUNCATE Only work on data files.

Your success in shrinking log files relies on logs being written to the front portion of the log file immediately after a backup (and the rate at which that occurs).

DBCC SHRINKFILE
Database Checkpoints
What Happens to Your Transaction Log in SIMPLE Recovery Model?

No comments: