Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
monty
Posting Yak Master
130 Posts |
 Posted - 2007-03-15 : 00:42:06
|
| Hi Gurus,my question is regarding rebuild index, i have learnt that if the recovery model is in full and rebuild index job as a part of maintainnence task is run then log size increases considerably, so just beforethe rebuild index is done if we switch to bulk recovery model then the log size wouldnt grow as much like in case of full recovery model.. to be prcise my doubts are:1)did any one try this option of switching recovery models to control the log size growth(keeping aside poit-in-time recovery)2)will this process have any benefits?3)should i test this process on a database with full recovery mode, and renaming the same database and changing the recovery mode to bulk.. my intension is to run the rebuild index on both the databases simuntanesouly to check log growth.iam i missing anything or do u ppl have any further suggestions.. keeping log backups being taken frequently as exceptionits me monty |
|
|
dewacorp.alliances
452 Posts |
Posted - 2007-03-15 : 07:20:05
|
| This might not answer your question but to handle the log size when doing rebuilding index:1. Try to make the backup TLOG occuring every frequently (15 minutes or so).2. The Rebuild Index is an offline step which I tend to avoid and use index defrag instead.Hope this help |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-15 : 10:28:36
|
| 1) No - it would break the recovery chain.2) Well, it makes your TLog smaller! You could just backup the Tlog more frequently (particularly during index rebuilds). You will still have a lot of TLog backup files, but your LDF file won't grow too huge.3) Yup, you need to test it!"iam i missing anything"Have you looked at using DBCC INDEXDEFRAG in conjunction with SHOWCONTIG (to work out which indexes are sufficiently fragmented to make it worthwhile doing any rebuild at all?). Note that DEFRAG does not implicitly of Update Statistics, so you'll need to do that as a separate step. Also, I recommend that you REINDEX "small tables" - anything with less and a few thousand rows.Kristen |
|
|
|
|
|
|
Rebuild Index