Your basic ITPro blog... What's going on at work, what I'm interested in.

Wednesday, December 10, 2008

SQL Server Log Files Growing... and GROWING!

So, is it odd to have a 600MB database file with an associated 82GB(!) log file? From what I've read, it seems so. But, I'm no DBA...

We are running SQL Server 2005 and I am trying to learn about Recovery Models (Simple vs. Full), log file re-sizing, how BackupExec 11d fits into the mix, and more.

It is proving to be a steep mountain to climb.


Peter Schott said...

First thing I'd check - make sure you're doing transaction log backups. That should be enough to clear this out.

Second thing - change your log "auto-grow" settings from 10% to 100MB or some other fixed number. 10% of 80GB is a pretty significant growth. :)

If you're doing all of that, check for something generating a LOT of activity. I can't think what that would be, but from the description, it sounds like you're not actually doing transaction log backups and as a result your log file will just keep growing. Full Database backups do not clear out the transaction log.

If all you need are full backups, consider switching to Simple recovery mode.

You may want to do that now, issue a CHECKPOINT against the server, then try to shrink the log file to something a little more reasonably sized. Get a full backup. Then either switch to Full again with scheduled transaction log backups or leave it at Simple and get full and differential backups.

Derek Mangrum said...

Thank you for the input!

We did some more research and talking and decided on the following:
- Convert Recovery Models from FULL to SIMPLE
- Run Full Backups every two hours.

When I made these changes, the log files got properly truncated/shrunk. We now have more 'reasonable' file sizes.

Peter Schott said...

You may want to consider differential backups instead of full backups, even though 600 MB isn't a lot to back up at one time. You probably have a pretty small set of changes and that would keep the backup size relatively small. Restore is "restore full", then "restore differential".

I'd consider transactional backups in some environments because it can be really helpful to get back to a time just before you made a pretty big mistake. Not that I know anything about that. :-D

Glad that you've got something working for you, though.

Alexis said...

At work with sql files I usually use-mdf recovery,because it has a lot of features,has free status and moreover can repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension).

Derek Mangrum said...

Thanks for the tip. I will take a look at that tool.

Alex said...

For me sql server is a quite important tool,because I often work with it. But yesterday I had a big unplesant problem. For luck I fast found a next software - recovery mdf file. And it determined my issue quite fast and easy as far as I remember. Moreover it learnt me how repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension).

Additional Info

My photo
email: support (AT) mangrumtech (DOT) com
mobile: 480-270-4332