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 |
|
tom.depoorter
Starting Member
3 Posts |
 Posted - 2013-12-18 : 12:58:58
|
| HeyWe've never rebuilt the indexes in our db.We've set up a maintenance plan that does it now on a regular base.The logfile grows a lot.I'm wondering of it will grow less the second time (if you've shrunked it)and if the size will keep on increasing each time since less "rebuilding" has to be done?Best regardsTom |
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-18 : 13:52:07
|
| The maintenance plan tends to be a very "blunt instrument". It will probably rebuild every index, every time; whether it needs it or not. So ... your LOG file size will continue to be a problem - it will need the same amount of space each time.If you want to stick with that solution you could set up a Log Backup Job that runs, say, every 2 minutes for however long you find, in practice, that the Index Rebuild takes. That will still have a lot of Log File to backup (but it will be in lots of small files, rather than one big file) but the physical LDF log file won't be extended to some crazy-big size because every 2 minutes the "work done" will be backed up, and cleared, from the LDF log file.Alternative is to look at some tools that will only rebuild the indexes on tables that are fragmented (according to whatever fragmentation percentage you set). You could also use Index Defragmentation, rather than Index Rebuild, which uses a lot less disk space (Index Rebuild just copies the whole shebang to a new area on the disk (i.e. within the physical MDF file) which obviously is like a sledgehammer cracking a nut. Lovely neat table, all unnecessary space removed ... but Index Defragmentation would probably get you 95% of the benefit for 10-40% of the "effort").You could also have such a routine, that checks fragmentation level first, which work on "most fragmented files first" and only runs for, say, 1 hour each night, so you would only get 1 hour's worth of Log Transactions, but over time during the week anything that most needed rebuilding would get done.Tara has some useful routines to do stuff like this in her blog, but its not quite as simple as just using the Maintenance Plans. Thus depends a bit what you needs / objectives / skill levels are.http://weblogs.sqlteam.com/tarad/default.aspx |
 |
|
|
|
|
|
|
|
rebuild index: will logfile be smaller next time