65.9K
CodeProject is changing. Read more.
Home

Clearing Transaction Logs

starIconstarIconstarIconstarIconstarIcon

5.00/5 (2 votes)

Jan 23, 2011

CPOL

1 min read

viewsIcon

7620

The root cause why the transaction log file grows so big is the wrong recovery model.The recovery model is set to full by default.99 percent of all SQL Servers never make use of this model because there is no backup of the transaction log file.So the great majority of all databases should...

The root cause why the transaction log file grows so big is the wrong recovery model. The recovery model is set to full by default. 99 percent of all SQL Servers never make use of this model because there is no backup of the transaction log file. So the great majority of all databases should run with recovery model set to simple. Many database admins think that the full recovery model is better then the simple recovery model. But that's not true; Microsoft has just choosen the wrong default value for the recovery model. If you change the recovery model to "simple", the transaction log file won't grow to gigabytes anymore and DBCC SHRINKDB will do the job and shrink the transaction log file to 1 MB. Use the full recovery model only if you have the following backup strategy: * full backup every day * differential backup every 4 hours * transaction log backup every 20 minutes In the case of a database crash, you have to restore the latest full backup followed by restoring the latest differential back. And then restore every transaction log backup. This means you have to restore up to 4h/20min=12 backup files. Without this sophisticated backup strategy, you don't need the full recovery model. Quite the opposite if you use the full recovery model the transaction log file grows without limits and gets filled with garbage. If you follow my advice, you will understand that all tricks to shrink the transaction log are only bad tweaks.