3

Possible Duplicate:
Why Does the Transaction Log Keep Growing or Run Out of Space?

My client is running SQL Server 2005 x64 Standard on a beefy server with 16 GB of RAM, and separate drives for data, log and temp. Daily backups include log backups.

Their accounting system (Accpac) database is around 45 GB and has been running on this server for the past 3 years with no problem.

About 3 or 4 months ago, during weekly maintenance plans that do an integrity check, index reorganizations, and update statistics, the log file ballooned to twice the size of the database and the job failed due to a disk full error.

At first, I thought the backups were not running so I dumped the log. I know I should not do this, but I didn’t really have any choice. Then, I began monitoring the nightly backups. All was good until the next index reorganization occurred. The same problem occurred with the log running out of space and I realized it was happening during this task.

I moved the log files to the db disk and the db files to the log disk since the db disk was larger. I wanted to see if I could at least get the job to finish by giving it more room. Now it ballooned to over 3 times the size of the database and again filled up the disk.

For now, I have just turned off the weekly index reorganize task which is not really a good long term solution.

I'm just using the reorg index task that is in the maintenance plan designer GUI. I was going to take a look at Ola Hallengren's scripts and some other stuff but wanted to see if there was possibly a simple explanation for this.

Also, I do have "compact large objects" checked but assuming this is for blob data? I don’t believe there are any BLOBs in this database.

Oh, I'm a developer wearing a DBA hat, so be gentle with me.

I forgot to mention recovery model is "bulk" vs. "full" - I believe this was set by the package vendor.

Improve this question
3
  • 1
    Good link: support.microsoft.com/kb/317375 Note list of possible root causes, and use of log_reuse_wait and log_reuse_wait_desc to troubleshoot the problem. My best guess is that you'll probably end up whacking some app developer over the head ;) Hopefully it's not you ;)!
    – paulsm4
    Sep 24, 2012 at 22:51
  • Thanks. The transaction log is truncating and space is re-used normally with the backups. Currently the log file is only around 1 gb and there are lots of daily transactions, so I know we're good as long as I don't do the index reorg. I probably need to try another index reorganize again (since I dumped the log a couple weeks ago) then run these stored procs as that is what appears to trigger the problem, assuming I can get through the whole reorg process at least once before the disk fills up.
    – rjean99
    Sep 25, 2012 at 14:01
  • Switching to Ola Hallengren's SQL maintenance scripts seems to have done the trick. I will double check the recovery model although I think it might already be bulk logged since these legacy packages seem to prefer that model. Thanks for everyone's input!
    – rjean99
    Jun 15, 2013 at 19:32

2 Answers 2

Reset to default
2

One thing to consider is to do more frequent transaction log backups. When you do a transaction log backup the log is truncated and and the space can be reused.

Improve this answer
2
  • From the OP's comments, it appears the problem occurs during index reorganization. This is a known issue; I cited links to several remedies. In this case, I don't think more frequent tranlog backups would help. IMHO...
    – paulsm4
    Sep 25, 2012 at 21:05
  • The way I read the question, there is only 1 log backup per day. If the log is filling up when re-indexing all of the tables, the OP will want to run one or more log backups while that re-indexing is in progress. That way, the log will be cleared stuff that was logged for tables that have been completed, creating space that can be used for the tables that are yet to be started. I think that is what Ola was getting at. I think that this is worth suggesting because I come across people who are surprised at the idea that log backups might be twice a day, let alone several times an hour.
    – Darin Strait
    Sep 28, 2012 at 14:09
1

Based on your comments, it sounds like the log is getting full doing an "alter index .. reorganize". That can happen :)

Try substituting "REBUILD".

Here's a good link on "indexing" procedures:

See also this link:

Improve this answer
3
  • Thanks all for your suggestions. I will try some of these out this weekend and report back.
    – rjean99
    Sep 26, 2012 at 17:34
  • Rebuild seemed to work fine with no adverse affects that I can tell. Transaction log file did not grow after this task. Again, I will take a closer look at some of the DBA scripts vs. using the simple maintenance plan tasks which should be more efficient for the size of database this is. Thanks for all your replies and comments!
    – rjean99
    Oct 1, 2012 at 15:34
  • Can you not change to BULK_LOGGED recovery model and use rebuilds instead of reorg's in order to benefit from minimal logging of rebuilds during your maintenance window(s)?
    – ooutwire
    May 20, 2013 at 14:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.