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.
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:
- 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.
- 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.
- 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.
- 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.