August 19, 2013 at 2:42 am
Thanks for that Erland but I do have a question.
Rebuilding an index will of course remove fragmentation and allow a database to be shrunk. That much is clear.
But if I have 200GB of data that has been removed from a database and that 200GB is spread over multiple tables in such a way that no table is fragmented more than 30% and is containing > 1000 pages then surely the reorganisation is the most helpful. It will remove the free space that is found within the data portion of the table and move it to the end so that when the SHRINKFILE with TRUNCATEONLY is performed the total size can be significantly reduced.
I still can't help but feel that a REBUILD is not necessarily the answer.
Regards,
Kev
August 19, 2013 at 5:58 am
REORGANIZE essentially performs a bubble-sort of the index. The pages are not changed, so if a page is only 50 % full, it will remain 50% full. Possibly, REORGANIZE could shuffle pages around so that if there are two extents with four pages in each, it could place the eight pages in a single extent and thus free up that extent. But I don't think that happens, though. Paul Randall's blog should have that information.
However, if you specify the option LOB_COMPACTION, you can regain space for LOB data, so under these circumstances, REORGANIZE can reclaim space.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2013 at 6:39 am
Erland Sommarskog (8/19/2013)
REORGANIZE essentially performs a bubble-sort of the index. The pages are not changed, so if a page is only 50 % full, it will remain 50% full.
I'm pretty sure that's not true. From BOL:
Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space. Compaction is based on the fill factor value in the sys.indexes catalog view.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2013 at 6:43 am
I was beginning to think I had to rethink everything I thought I knew about indexes.......
😀
August 19, 2013 at 7:41 am
Can't believe no one has pointed out that is not a good idea to shrink Production DBs.
August 19, 2013 at 7:41 am
Can't believe no one has pointed out that is not a good idea to shrink Production DBs.
August 19, 2013 at 7:54 am
Talib123 (8/19/2013)
Can't believe no one has pointed out that is not a good idea to shrink Production DBs.
It is not good to shrink without rebuild.
If you shrink and rebuild after then it's ok.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
August 19, 2013 at 8:12 am
There is a lot more work needed if this is to be done properly.
Shrinking the data file is simply a bad idea and a simple index rebuild is not sufficient.
http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
from Paul Randall.
August 19, 2013 at 8:22 am
IgorMi (8/19/2013)
It is not good to shrink without rebuild.If you shrink and rebuild after then it's ok.
No, it may be OK. In this case, the jfgrocha removed 200 GB of data, and if he believes that in the future they will have a archiving process running regularly, shrinking the database is OK. But this is just a one-off, the next archiving will happen in two years, shrinking is useless. And in any case, any shrink should leave headroom for spaces needed until next archiving operation.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2013 at 10:23 am
Erland Sommarskog (8/19/2013)
IgorMi (8/19/2013)
It is not good to shrink without rebuild.If you shrink and rebuild after then it's ok.
No, it may be OK. In this case, the jfgrocha removed 200 GB of data, and if he believes that in the future they will have a archiving process running regularly, shrinking the database is OK. But this is just a one-off, the next archiving will happen in two years, shrinking is useless. And in any case, any shrink should leave headroom for spaces needed until next archiving operation.
Yes, and I read the Paul's blog from above.
It sometimes could cause still problems even if you'd rebuild the indexes.
Thanks, this thread is good
IgorMi
Igor Micev,My blog: www.igormicev.com
August 19, 2013 at 10:25 am
Talib123 (8/19/2013)
Can't believe no one has pointed out that is not a good idea to shrink Production DBs.
I am not sure it can be said it isn't a good idea.....but I think most could agree it is (most of the time) pointless. The tables and datafiles are that big for a reason!
But as Long as the SHRINK is done correctly (meaning performing the REBUILD afterwards) then there shouldn't be a Problem.
August 19, 2013 at 11:28 am
Sometimes a database file shrink is indeed needed.
Shrink does not automatically fragment every index. Absolutely you should test for fragmentation after the shrink, and rebuild accordingly, i.e., basically run your normal rebuild process. [A reorg won't help a hugely fragmented index nearly as much as a full rebuild.]
The SORT_IN_TEMPDB rebuild option seems to me to be overlooked in this discussion. I would strongly recommend it in this situation, since you're trying to reduce the size of the main database. Of course you first have to be sure that the free tempdb data space is large enough to hold the index you're rebuilding. In fact, if necessary, add disk to the tempdb as needed to handle the rebuilds, then remove it when the rebuilds are done (hopefully there's a LUN(s) that can be "borrowed" temporarily for this purpose if required).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 19, 2013 at 11:35 am
Data compression should also be considered, if on Enterprise Edition. [My company has some extremely large audit/logging tables that are rarely read. After compressing them, I often shrink out the freed space, then rebuild all tables except the huge compressed ones. Since they are very rarely read, (extent) fragmentation is not a significant concern for those tables.]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 19, 2013 at 4:01 pm
Jeff Moden (8/19/2013)
I'm pretty sure that's not true. From BOL:Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space. Compaction is based on the fill factor value in the sys.indexes catalog view.
I read about it Kalen Delaney's SQL Server Internals 2008, and as I understand it, some compaction can occur, but only if can be performed over adjacent pages. You certainly get a better effect with REBUILD.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply