0

I get an email today stating that we have a log file that's grown to 278GB, and asking if anyone is maintaining it. I already know the answer to that is no because we don't have a dba (other than myself. I know a fair amount about sql, but I'm a developer).

Our database is moderately large; some tables having 100s of millions of records. This does seems to be pretty big for the log file though and I'd like some direction in figuring out whether this is abnormal, whether we could configure something better, or whether the best solution is simply to accept that we need to feed the beast more hd space.

If there is any more information I could post up here that would help get me a better answer please let me know what.

Brandon Moore
  • 456
  • 2
  • 6
  • 17

2 Answers2

3

It sounds like the database is set to FULL recovery mode and there are no transaction log backups being run. This is a very common problem.

If you don't want to do point-in-time restores, log shipping or anything along those lines and you are comfortable with only being able to restore to the point where the last full backup finished, set your database to SIMPLE recovery mode and shrink your log file to something sane.

If you are doing log backups, ensure that they are running without error and think about increasing their frequency.

It is also possible that something started a transaction hours/days/weeks/months ago and never committed it. Active portions of the log cannot be truncated, and those portions will not become inactive until the transaction commits or rolls back. Use DBCC OPENTRAN (MSDN is currently down, so I can't provide a link now) to troubleshoot that, it will show you information about the longest-running transaction in a database. If you find that you have a long-running transaction, you might have a hard time getting the application to commit. Of course, you can kill that connection but you will lose any database modifications that it has made in those hours/days/weeks/months. You may need to fix some code somewhere in the app.

Again, after you have resolved the problem, you will want to shrink your log file to something sane. You can use Performance Monitor to see how much space actually gets used over time. As a total shoot-from-the-hip number, I'd aim for a size that is 1.25 times your largest table (measured in GB, not in rows) and leave the file configured to grow.

Darin Strait
  • 2,012
  • 12
  • 6
  • From our network admin: "Full backups run every Saturday morning and the log run after the data run on Saturday then written to tape upon completion. Differential backups are ran as well during the week." I just emailed to ask him if he's verified that recent log backups have been successful but I'm pretty sure he's already checked that. – Brandon Moore May 24 '12 at 17:05
  • Maybe it's been that way for a while & was noticed lately? At this point, especially if the log file is larger than the data file(s),I'd leave the recovery set to FULL, monitor the used portion of the log file and use shrinkfile to lop off a couple of GB of log file at a time, over several days or a week or two. Also, it seems odd to backup the log once a week and do differentials several times a week. Normally, log backups are run very frequently, perhaps one an hour or once every few minutes. I don't know that this is necessarily a cause of your problem, but I wanted to mention it. – Darin Strait May 25 '12 at 17:28
  • Possibly so. The database stores a lot of pos data, however it's not realtime. We receive files from various retailers and load a days worth of data at a time for each retail chain. So I wonder if doing log backups more frequently would make a difference with that in mind? – Brandon Moore May 30 '12 at 01:09
  • Isn't much point to running 1 tlog backup between full backups, ++ with those diffs. I'd switch to SIMPLE recovery, stop tlog backup and run a diff in it's place (1 full + 6 diffs a week) or leave db in FULL recovery mode, stop all the diffs and run tlog backups more often, anywhere from 1/day to 12/hour, depending on rate of data change & SLA. – Darin Strait May 31 '12 at 15:56
  • At this point, if the log hasn't grown and DBCC OPENTRAN() doesn't say that a transaction has been in-flight for days/weeks/months, I'd say that you are left with slowly shrinking the log down to something sane, watching for growth & thinking about backup/restore strategy. – Darin Strait May 31 '12 at 15:56
2

It sounds like you're not taking any log backups. A log backup truncates the log file, so if it's growing out of control, it seems like a log backup hasn't been run.

edit I had the backup type wrong, corrected thanks to darin.

growse
  • 8,020
  • 13
  • 74
  • 115
  • "You should also be aware that taking a log backup will truncate the log, but taking a FULL database backup will not truncate the log." http://sqlblog.com/blogs/kalen_delaney/archive/2008/11/30/when-is-full-recovery-not-really-full-recovery.aspx – Darin Strait May 24 '12 at 15:36
  • This was my first thought too, but then I found out we are actually doing full backups and backing up the log weekly. – Brandon Moore May 24 '12 at 17:09