How to avoid using shrink in SQL Server 2005?

How to avoid using shrink in SQL Server 2005?

  • Comments 4

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:

  1. Deleting a lot of data and then taking a backup – how to make the backup smaller without running shrink?
  2. Emptying a file before removing it
  3. Moving a file/filegroup/database to read-only
  4. Running ALTER INDEX … REBUILD and then running shrink to reclaim the space used for the rebuild.

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.

 

Clustered Indexes

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.

 

Heaps

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:

  1. Run shrink with EMPTYFILE into a new filegroup and then recreate the non-clustered indexes; or
  2. Shrink the existing filegroup with NOTRUNCATE (to create a bunch of contiguous free space in the database files), then recreate the non-clustered indexes in the contiguous space, and then, if you’re making the filegroup or database read-only, run shrink with TRUNCATEONLY (to give back extra space to the operating system)

Summary

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.

Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
  • One evening last week I sat down with Kimberly for 5 minutes to come up with a top-of-our heads list

  • Hi, Paul

    "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?

    Thank you

  • 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维护建议清单。 虽然主题是维护,但相信理解这些对于从事数据库开发的人员来说也有一定价值。

Page 1 of 1 (4 items)