The likely reason you have so much freespace in your files is any index rebuilds that you may do. Of course, it could also be due to deletes. In either case, shrinking your database is going to cause you great pain and agony.
If you're working with 20TB of disk space and, say, 10 TB of data, then you need to do things a little bit differently starting now. And, no... it's not going to be quick and you won't think it easy.
First, you need to identify what you're largest tables are, what they're used for, and how they're used.
Normally, the largest tables in a database are "WORM" tables (Write Once, Read Many) and are in the form of some type of audit table. These tables are temporal in nature and older time periods (say, months) are never updated. What you need to do with those is partition them to a separate database, 1 file group per time period and 1 file per file group. Once partitioned and properly compacted to contain virtually no free space, set the non-current (current and any future month partitions you made) to READ ONLY and back them up. Done correctly, you will never have to defrag them or back them up ever again regardless of whether you use Partitioned Views or Partitioned Tables.
Once successfully partitioned, drop the original table(s) from the original database and replace them with synonyms if DRI isn't required (and it should not be required if they're audit tables).
There's more but I don't know enough about the tables or their usage in your database. You need to identify more information about the largest tables in your database before we can actually help. And when I say "help", I also mean making these files a whole lot more bullet proof in the future.
We'll talk about recovering the unused freespace after we've made it much easier to do such a thing properly.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)