I run a nightly reindex/rebuild script taken from Lara Rubbelke's Blog. It works well and only reindexes/rebuilds when the indexes hit a certain threshold of fragmentation or density:
http://blogs.digineer.com/blogs/larar/archive/2006/08/16/smart-index-defrag-reindex-for-a-consolidated-sql-server-2005-environment.aspx

It's a small database and only generates (based on my thresholds of 10% and 80% for fragmentation and density respectively) around a 1 GB log file nightly on a database with about 5GB to 6GB of actual data.

While it probably doesn't matter, I should mention this database is in a log shipping environment with another local server that takes these transaction log and restores them. Other factors: The DB is in full recovery mode - even during reindexing/building. The database as a 4GB log file and is rarely used past 20-40% during normal operations.

Problem:
Occasionally the process of reindexing generates HUGE transaction logs relative to the size of the database and never seems to recover. What I mean by this is that one day it will generate a 3.5 GB transaction log backup and it keeps repeating the process every 15 minutes like it is stuck in the loop. If I don't respond to the alerts I have in place it will generate 60 GB of log backups. This presents a real issue for us as we ultimately send these log backups over a wan for remote backup. The only way I've found to fix the situation is to a) stop log shipping, put the db in simple recovery and re-establish log shipping or b) reboot/restart the sql server.

I imagine if a large table(s) needs to be reindexed it might cause a large transaction log backup, but why won't it just generate a large backup and then move on? It seems to get "stuck" and the next time a trans log is cut (every 15 minutes) another 3.5GB file is generated until I intervene.

I've tried to look into the log with 3rdparty tools, but it's a bit overwhelming with a 3.5GB trans log.

Does anyone have any ideas or has anyone experiences this before? How can I narrow down what might be happening?

Thanks in advance,

Hutch

Have you tried using BULK LOGGED mode during the reindexing?

 

thomas:

Have you tried using BULK LOGGED mode during the reindexing?

I have, but only during testing. Now that you mention it, I'm going to have the database set to "bulk logged" before the indexing and then back to "full" after the indexing is complete. I'll let it go for a while. Unfortunately it can often run for a number of days/week before the issue recurrs.

Thanks,

Hutch