Click here to Skip to main content
Click here to Skip to main content
Alternative Tip

Clearing Transaction Logs

, 23 Jan 2011
Rate this:
Please Sign up or sign in to vote.
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

sx2008
Software Developer (Senior)
Germany Germany
No Biography provided

Comments and Discussions

 
GeneralI also believe that we dont require full recoovery as we alw... PinmemberAnil Soni 26-Jan-11 4:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 23 Jan 2011
Article Copyright 2011 by sx2008
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid