SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

huge log file

huge log file

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (509K reputation)SSC Guru (509K reputation)SSC Guru (509K reputation)SSC Guru (509K reputation)SSC Guru (509K reputation)SSC Guru (509K reputation)SSC Guru (509K reputation)SSC Guru (509K reputation)

Group: General Forum Members
Points: 509771 Visits: 44288
There are a few more things other than just size to consider about the log file. I've been through this with every new company that I've worked for (quarter tera-byte log file blew up on my second day at one company) and I just went through all of this about 10 minutes ago on a similar thread.

In the area of performance, the initial settings are horrible. It'll grow the log file in a very fragmented way which can have a great impact on performance. It'll also allocate some pretty small "VLF's" or "Virtual Log Files". Think of it kind of like having a disk with a super small sector size. Not very effective. For more on how to "right size" the underlying VLF's in the Sql Server log file, please see the following URL.

That brings us to the next point. How big is your database and how big is the largest table in the database? There are a few more aspects to database maintenance that use the log file and that you must consider when trying to right size the log file. Index maintenance and the rebuilding of statistics. Both are extremely import to performance especially when you have nightly jobs that affect or read from many rows and both are logged in the log file when they occur just in case something goes wrong. The link to Gail's article that you were previously provided will help there quite a bit. I use the rule of thumb that the log file should be about 1.5 times the size of the largest table to start with and then should be managed for growth to control the size of the VLFs that I previously spoke of. Log file growth should never be a surprise (should never happen automatically) in my humble opinion. Neither should the data file growth.

Another thing to consider is how important your data is. Way too many people will set a database to "Simple" recovery mode because it's a small database and they do nightly full backups and think that's enough. If the data is important, nothing could be further from the truth. Ask yourself how many minutes or hours of data your company can afford to lose and then setup log backups to run in no more than that amount of time. Personally, I won't tolerate the loss of any data so I do "Point-in-Time" backups on my production databases every 15 minutes. That also helps keep the log size down on busy systems. "Point-in-Time" backups require that only the FULL recovery mode be used. If you go to the Bulk Logged mode, then every backup taken while you're in that mode must be used in it's entirety or not at all during a restore... and you can't skip logs. That's why they call it a "log chain".

Getting back to your original problem, step 1 is to visit the articles I recommended above and figure out what the correct size log file should actually be. Even if you decide it's too small and needs to grow, if the initial settings were left at the default, consider blowing away the log and starting over.

Step 1 would be to take a full backup.
Step 2 would be to take a log file backup.
Step 3 would be to change the recovery model to the SIMPLE mode when not much is happening so that if something does go wrong during this timeframe, you won't lose much.
Step 4 is to shrink the log file to 0 bytes. Don't shrink the database. Shrink only the log file. This will clear out all the "bad stuff" with the incorrect sized VLFs.
Step 5 is to change the recovery model back to FULL.
Step 6 is to change the initial size of the log file to the size you planned on like I recommmended earlier. Also change the growth setting to grow in MB rather than percent just in case there's acccidental growth. The number of MBs should be with what size you want the VLF's to be if growth occurs.
Step 7 would be to take either a differential backup (smaller and quicker than a full backup) or another full backup to restablish the log chain.
Step 8 would be to turn on a maintenance plan or custom code to do log file backups every x number of minutes as previously discussed.
Step 9 would be to ensure that regular tape backups are being taken of all of your SQL Server backups.
Step 10 would be to setup a plan to delete the backups from disk after they've been backed up to tape and after some period of time has elapsed. I keep 2 to 3 days of backups on disk for quick recoveries if anything goes wrong and it has saved my bacon more than once (especially when you have developers and other folks that have too many privs in prod).

To emphasize, the only time that I'd ever set a database to SIMPLE recovery on a permannent basis is when I truly didn't give a damn what was in it. Those could be staging databases, scratchpad databases (similar to Temp DB), and maybe even experimental "sandbox" databases. I would never permanently set a production database to anything other than the FULL recovery mode.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum