Late night blog post to round out spring-break vacation...
A number of customers I’ve spoken to in the last few weeks have been making use of database or file shrink in situations where they don’t really need to. There are few recurring scenarios I’ve seen:
The last scenario is particularly nasty because shrink effectively reverses the effect of an index rebuild or reorganize (see this post for more on the shrink algorithm).
So, with these scenarios demanding some way to reduce space usage, how can you do it without using shrink? The answer depends on whether your base tables are clustered indexes or heaps.
The trick here is to rebuild the clustered indexes into new filegroups, using the CREATE INDEX … WITH DROP_EXISTING statement. This does the same as a regular index rebuild, but you can alter the location of the index to a new filegroup (or to start using a partitioning scheme that defines a filegroup layout) and you can do it online (in Enterprise Edition).
If you’re moving the filegroup or database to be read-only, then you should set the FILLFACTOR to be 100% when you rebuild the indexes.
If you’re not changing the index schema, you don’t need to worry about the non-clustered indexes, as they will not be automatically rebuilt. You will want to manually rebuild them into the new filegroup(s) in the same, or just drop and recreate them (which could be tricky if they’re enforcing constraints).
Once you’ve moved everything out of the old filegroup, you can just remove it, and you’re done without having to execute shrink at all.
The first thing to preferably do is drop all the non-clustered indexes, or at least those that aren’t enforcing constraints that would be in danger of being violated while an enforcing non-clustered index was dropped. This is because when shrink moves a heap page, all related non-clustered index rows have to be updated; because heaps have physical row identifiers (see my previous post on shrink for more details).
Once you’re rid of the non-clustered indexes, you’re free to run shrink. Heaps are not ordered, so cannot become logically fragmented – so the problem of shrink causing fragmentation doesn’t affect heaps. Then you have two choices:
With a little thought, shrink can be avoided. I know of several customers who have tried this approach successfully, and saved themselves lots of resource usage in the process.
Let me know how you get on.
One evening last week I sat down with Kimberly for 5 minutes to come up with a top-of-our heads list
"If you’re not changing the index schema, you don’t need to worry about the non-clustered indexes, as they will not be automatically rebuilt. You will want to manually rebuild them into the new filegroup(s) in the same, or "
You mean using CREATE INDEX … WITH DROP_EXISTING to manually rebuilt them non-clustered indexes.., correct?
"Once you’ve moved everything out of the old filegroup, you can just remove it, and you’re done without having to execute shrink at all."
you mean alter database to remove the old filegroup? what if i not move everything (clustered indexes) out of the old filegroup?
Yes for both questions.
Well, obviously if you don't empty the filegroup then you're not going to be able to remove it, right?
转载、解释一下来自SQL Server Storage Team的VLDB维护建议清单。 虽然主题是维护，但相信理解这些对于从事数据库开发的人员来说也有一定价值。