This isn't a major issue just something I've been researching. Here's some background on my situation.
I have an admin database that I use to track performance and other items of interest. There is one table of that tracks memory usage and has 6 fields. I am using 2 fields to make up a clustered index with approximately 310,000 records. The data file is 500mb and the log file is 279mb. The log usage is normally around 1% (3mb). My issue arises when indexes are evaluated on weekends and are rebuilt/reorganized accordingly. When the index is rebuilt, the log spikes to 99% and grows if necessary.
After doing some research, I found out why it happens. (not the best explanation)When a clustered index is rebuilt it is broken down, sorted, and rebuilt as a second index. Only then is the original index dropped. This operation is fully logged thus blowing up the transaction log. I've come across a few solutions for this issue.
-First is obviously to leave the log as big as it is (or as big as it needs to be). This is the easiest but makes me feel the log is just wasted space for the other 98% of the time. I'm not too interested in shrinking the log every week.
-Second is to use the SORT_IN_TEMPDB ON option during the rebuild operation. This is what interests me and mainly why I am writing this post. As far as I know, the sorting would take place in the tempdb database and be logged in its transaction log, relieving the admin database of such duties. If all conditions were met, this is ideal for performance but my main concern is not letting disk space get out of hand. The downside is the transference of used space from the admin database to tempdb therefore not really reducing the overall footprint of the instance.
-Third is to change the recovery model to bulk logged just before the rebuild operation and back to full afterwards. This will minimally log the rebuild operation thus reducing the amount of log growth.
Does anyone have any real world experience or opinions on these matters?
Transaction Log Disk Space for Index Operationshttp://msdn.microsoft.com/en-us/library/ms184246.aspx
Disk Space Requirements for Index DDL Operations
Choosing a Recovery Model for Index Operations
______________________________________________________________________________________________Forum posting etiquette.
Get your answers faster.