January 11, 2010 at 4:58 am
Rebuilding an index is a logged task. So can anyone clarify that if a shrinkfile is done after rebuilding an index, does this task still have an effect or not?
January 11, 2010 at 5:28 am
Shrink file has an effect of causing fragmentation, and is not recommended except special situations.
From Books Online
"A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database."
January 11, 2010 at 5:32 am
Vivek29 (1/11/2010)
Rebuilding an index is a logged task. So can anyone clarify that if a shrinkfile is done after rebuilding an index, does this task still have an effect or not?
Which file r u referring?
Answer is s,if u shrink the data-file.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 11, 2010 at 5:41 am
Thanks for the reply. But what would be the better option to rebuild an index without increasing log file if it is necessary? Changing from the Full recovery model is feasible for this task and then revert it back?
January 11, 2010 at 6:00 am
1. Switch the database to BULK_LOGGED mode
2. Rebuild the index(es)
3. Switch back to FULL recovery
4. Immediately backup the log
Rebuilding indexes in this way will ensure that the operation is minimally logged (unless a concurrent database backup is running).
Note that the log backup in stage 4 will include all pages which were modified in step 2 and will therefore be larger than usual.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2010 at 7:22 am
Vivek
How u r rebuild the indexes through M-P.
IMO:
Don't shrink any of files because it ll grow again and again & cause the physical fragmentation.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 11, 2010 at 9:36 am
Not to join in the flogging of the 'don't shrink' idea but in general it is a bad idea to shrink the log file.
You may want to look at doing an index reorganize instead of a full rebuild. Also if you don't have logic in that looks at fragmentation to see if an index needs to be rebuilt, you may want to add that to your script.
January 11, 2010 at 8:37 pm
Henry Treftz (1/11/2010)
You may want to look at doing an index reorganize instead of a full rebuild. Also if you don't have logic in that looks at fragmentation to see if an index needs to be rebuilt, you may want to add that to your script.
True, but bear in mind that reorganize is always fully logged.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 12, 2010 at 1:57 am
Thanks guys.I am talking about shrinking log file after rebuilding indexes.And sometimes we require rebuilding of some indexes daily in production environment.So, is it worth changing recovery model to bulk logged and then revert it back everyday?Any other option we can opt?
January 12, 2010 at 2:14 am
Vivek29 (1/12/2010)
Thanks guys.I am talking about shrinking log file after rebuilding indexes.And sometimes we require rebuilding of some indexes daily in production environment.So, is it worth changing recovery model to bulk logged and then revert it back everyday?Any other option we can opt?
It's up to you to evaluate the pros and cons of switching to bulk logged mode and back again. Personally, I generally do switch, but your priorities may differ from mine. Please see my previous post for details.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 13, 2010 at 6:45 pm
Switching between recovery models in Production environment may not be possible and re-commended for some customers. Also you could do a smart re-index, where in you rebuild index where fragmentation is > 30 and reorganize if the fragmentation level is between 5 to 30. Also check the number of pages. If the pages are less than 1000 then its too little to defragment anything. In other words the defragmentation doesn't have any effect.
But why do you want to do shrink file?
Thanks !
Amol Naik
January 14, 2010 at 5:07 am
After rebuilding indexes, log file size increases due to fragmented space released.Its not possible to keep bigger log file size if you rebuild indexes frequently.Thats why I am looking for shrinking, but it undoes the rebuild work.Any suggestion for the same will be appreciated.
January 14, 2010 at 5:18 am
Vivek29 (1/14/2010)
After rebuilding indexes, log file size increases due to fragmented space released.Its not possible to keep bigger log file size if you rebuild indexes frequently.Thats why I am looking for shrinking, but it undoes the rebuild work.Any suggestion for the same will be appreciated.
Either:
1. Switch recovery models as I described previously; or
2. Maintain a larger log file. There is no point shrinking it if it is just going to grow again next time you rebuild or reorganize indexes.
3. If for some strange reason you must shrink the log file, use DBCC SHRINKFILE rather than DBCC SHRINKDATABASE (from T-SQL) or use SSMS to shrink just the log file, not the database as a whole. If you just shrink the log file, the good work on the index structures is not 'undone'.
Options 1 and 2 are recommended, 3 would be quite an odd choice.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 14, 2010 at 5:33 am
Options 1 & 2 are sometimes not possible to do on Production environment.If option 3 is true,then its fine.
January 14, 2010 at 9:41 am
Vivek29 (1/14/2010)
Options 1 & 2 are sometimes not possible to do on Production environment.
Why?
Vivek29 (1/14/2010)
If option 3 is true,then its fine.
No, it's a bit silly really. Surely you're not that short of disk space that you have to rob a log file that's just going to grow again?!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply