Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Sep 27 '12 at 23:44

This question came from our site for professional and enthusiast programmers.

marked as duplicate by jcolebrand Dec 10 '12 at 14:56

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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 '12 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 '12 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 '13 at 19:32

2 Answers 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.

share|improve this answer
    
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 '12 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 '12 at 14:09

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:

share|improve this answer
    
Thanks all for your suggestions. I will try some of these out this weekend and report back. –  rjean99 Sep 26 '12 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 '12 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 '13 at 14:55

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