my DBA obviously thinks that it is best practice to use shrinkdb / shrinkfile all the time some bigger table is truncated / deleted just a little bit.

I tried to talk sense to him and explain him how it works but he won't listen. So I end up with a database / database files with messed up pages.

Is there any way that i can fix that for certain tables. Does an Index and Table Rebuild also reorders / reallocate the data or does it just logically rebuild it?

In the end i want new fresh data pages because the old ones might be copied to the front of the data file due to shrink usage.


Does your DBA get paid by how little disk space is used or something?

This is not only useless, but is an actively harmful practice.

Make one final attempt to convince your DBA (or their manager) with the following article, written by someone with unimpeachable credentials on this topic (Paul Randal, who was, for a while, in charge of development on the SQL database engine at Microsoft):

Why you should not shrink your data files:

A data file shrink operation works on a single file at a time, and uses the GAM bitmaps to find the highest page allocated in the file. It then moves it as far towards the front of the file as it can, and so on, and so on. In the case above, it completely reversed the order of the clustered index, taking it from perfectly defragmented to perfectly fragmented.

The same code is used for DBCC SHRINKFILE, DBCC SHRINKDATABASE, and auto-shrink – they’re equally as bad. As well as introducing index fragmentation, data file shrink also generates a lot of I/O, uses a lot of CPU, and generates loads of transaction log – as everything it does is fully logged.

Data file shrink should never be part of regular maintenance, and you should NEVER, NEVER have auto-shrink enabled.

(emphasis mine)

In my experience, 15-20% internal free space within databases is normal and expected. Either way it is going to be free space, there is literally no good reason to have that disk space sitting outside the file as opposed to inside the file.

If your DBA can't be educated (or replaced, and you can't appeal over their head), the only thing I can possibly suggest is to see how much you can reduce table fragmentation, so your DBA hardly ever finds anything to shrink:

  1. First, make sure that primary keys are set so that newly inserted rows always appear at the end in your current data order, so tables always grow in a contiguous manner.
  2. Make sure your tables and indexes have a smaller "fill factor" (which leaves more free space within each data page), so that updates/inserts are less likely to create page splits and fragmentation.
  3. Finally, rebuild your tables and indexes into a brand new filegroup using CREATE INDEX … WITH (DROP_EXISTING = ON) ON, as discussed near the end of Paul's article. This will make them contiguous from the very beginning of the file.
  4. When small fragmentation does starts to occur, defragment the file, which slowly moves pages in-place, instead of rebuilding, which more often than not tries to move the table or index to a different area.

This won't keep them contiguous forever, especially if someone deletes a big range from the beginning or middle of a table, but it might help.

  • Without knowing what the data type is for the primary key there is no guarantee that it will be put at the end on inserts. Uniqueidentifiers as a primary key, for example, will begin to fragment the data pretty quickly after just a few inserts. Setting the fill factor low can cause some major performance problems when it does run out of free space as it has to build itself back up to that amount of free space. And again it won't help with fragmentation on data types with a uniqueidentifier as the PK. The best solution for the OP's problem is education of the "dba". – Aaron Apr 5 '17 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.