Header

Friday, July 13, 2007

Help my log file is growing huge !

Scenario:
Database file size (.mdf): 250 MB
Log file size (.ldf): 10 GIGABYTES

Quick solution
Make a transaction log backup of your database. Shrink the log file. Make a new transaction log backup.

See BOL for the How to on your version of the SQL Server.


Reason
Many administrators mistakenly think that using the full recovery model (which is default) and then making a daily full backup is sufficient maintenance.

The problem is that the full backup does not flush the inactive transactions from the transaction log when the full backup finishes - this is the case both for SQL 2000 and SQL 2005.

There are different ways to solve the problem - the right one in my opinion being regularly to back up the log file - which flushes the log - and then solving the problem in the long run.

Long term solution
Schedule at least one daily transaction log backup - I would recommend in the middle of the work-day if only 1. Eg. Full backup at 23:00, T-Log at 12:00. If the database has any important activity during the day (and most does) then you properbly want to take transaction log backup every 2 or 3 or 4 hours during the day. Worst case - you can go back to the time of the last t-log backup. If you only take one full backup a day - worst case you need to go 24 hours back......

No comments: