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.
- Back it up
- Attempt to shrink it
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.
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.
NB: Prior to SQL 2008 you could reduce with the tranaction log with
BACKUP LOG db WITH TRUNCATE_ONLY
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:
Post a Comment