Determining what objects SHRINKDATABASE will free space from

Question

First off, yes, shrink is bad. Don't use it, ever. I understand. Unfortunately, it's kind of SOP and I'm not in a position to argue since it's usually a stopgap until we can get a maintenance window to allocate more space and rebuild/reorganize indexes.

We recently had an issue where we were switching over from traditional backups to a third-party tool. Due to the size of the database and some large objects we'd recently removed, it made sense to shrink it prior to taking the clean backup with the new tool before the cutover. I had some reservations, but we went ahead with it as it rarely causes problems. Obviously, clearing the logs isn't really an issue, but we were shrinking the mdf. This caused a lot of blocking on just a few large, highly-transactional objects and, rightly so, brought up some pretty serious questions about how we use this.

I've been doing a lot of research, but haven't yet found enough detail to my satisfaction.

• Is there anywhere that actually describes the internals of how SHRINK is operating?

• Is there a way to determine which objects are more fragmented than others and how it's freeing up space? Alternatively, does it not matter how fragmented a table is and it's more about the overall size of the object compared to others in the db?

Paul Randall talks a bit about it here, but it's not enough detail.

I know you could sort of base it on the dm index stats, but it's been pretty difficult to run on some of our larger databases due to the size and over-indexing. I'm also not sure if that's all I'd need to be concerned with. Main tables are stored as heaps and only the indexes are really fragmented, but I'm not sure how different partitions affect things or if other parts of the underlying DB could also be getting freed up.

I did also see this question, but I'm not sure if I'd be able to configure it to the kind of granularity we're looking for.

Answer 1

A few thoughts:

1. Never use SHRINKDATABASE, always use SHRINKFILE.

You want to be in control of which file it is taking space from, and exactly how much so you leave some space in reserve. Run a query like this to see the actual used/free space in each file:

SELECT DB_NAME() as dbname, type_desc, name as logical_name, 
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
FROM sys.database_files WITH (NOLOCK)
ORDER BY type, file_id;

Let's say your primary data file is 100GB, but only 20GB is actually used, you might have a final target size of 25GB or 30GB to leave some internal free space. So you'd do a shrink something like:

DBCC SHRINKFILE (name='logical_name', size=30000)

2. Internal SHRINKFILE Behavior

Internally, shrinking a data file simply moves used pages one at a time from the end of the file to an open spot near the beginning until it has enough space at the end of the file to meet your target size.

It's a fairly simply brute-force method and pays no attention to the contents of the pages, which is why it frequently results in table fragmentation. From the Paul Randal article you linked:

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.

If the page it wants to move is locked by another process, it will be blocked.

3. SHRINKS keep their progress

If you start a shrink, and then have to stop it because of blocking or other conflicting activity, it will retain its progress. All the pages it successfully moved will stay moved. You can resume later to finish the work.

4. Finding out what tables will be impacted by a shrink

There might actually be some ways to do this, using queries on sys.allocation_units or sys.partitions or maybe using DBCC PAGE or something, but my recommendation is: don't bother, large databases will have the scattered remains of thousands of objects across the data file, most of which you don't care about.

If you're insistent, here are some advanced articles I found via search:

• Stack Overflow: See what data is in what SQL Server data file?
• Using DBCC PAGE to Examine SQL Server Table and Index Data by Armando Prato
• Inside the Storage Engine: GAM, SGAM, PFS and other allocation maps by Paul Randal
• Analyzing SQL Server Data File Anatomy by Murilo Miranda

You can already find out what tables/indexes are at play when it starts blocking, everything else is noise.

If that lock never clears to give you a chance to move the page, then you might have to schedule some downtime with no other activity.

5. When you are done, do a reindex to fix fragmentation

The data file might grow a bit again, but unless your database is one massive table, you'll still likely be left with a smaller end result than when you started.

Answer 2

First, there's a difference between DBCC SHRINKDATABASE and DBCC SHRINKFILE. You're right, Paul Randall's article doesn't discuss how DBCC SHRINKDATABASE decides which files to try to reclaim space from. The most important point there, however, is that you aren't in control of which files. If you need to reclaim 20GB, and you've got a 30GB log file and a 250 GB data file, it could reclaim that 20GB from your log file - which might well almost immediately grow back to 30GB, meaning the shrink was a complete waste of time. In most cases, you hopefully have a better understanding of the normal cycles of your data that SQL Server's engine does. If you've got a 30GB log file with 29+GB free, and a 250GB data file with 30GB free, you're far better positioned that the engine to know whether your log file only needs more than 5GB when you do an index rebuild/reorg or if it needs that much every night for a data load process, and whether you're looking at normal data activity (say 1GB of new data a month), or if you'll be loading 20GB of data into the system in a couple of days.

What's more, DBCC SHRINKDATABASE is not going to pay attention to where your data is. If you've got a 250GB data file on your almost full main drive, and a newly-created 100GB file on a brand new drive, DBCC SHRINKDATBASE is at least as likely to shrink the new file, not the old one, when the drive you need to free up space on is the old one.

Now, Paul's article does tell you how a file is shrunk. Both data and log files are shrunk by cutting off the end of the file.

With a log file, you have to wait until there's enough space at the end of the file that it can be truncated. The log files are treated like an endless loop; when it gets to the end, it starts back at the beginning. Over time, the end of the log will be clear, and you will eventually be able to free up the space.

With data files, SQL Server doesn't just patiently wait until the end of the file is empty. As stated in the article, the shrink operation finds the page closest to the end of the file, and moves it as close to the beginning of the file as it can, and repeats that until there's enough free space at the end of the file to shrink it to the desired size. It's not looking at what is or isn't currently fragmented; its only goal is to take the stuff at the end of the file and move it as far away as possible.

Again, look at Paul's example: He had a table with basically no fragmentation. Every page was moved. The last page became the first, the first page the last, and the fragmentation was total.

It's kind of like moving. You pack up all your boxes, label them what rooms they belong in. However, the movers happen to be illiterate, and put all the boxes in the living room. One of you "kitchen" boxes winds up by the fireplace, behind all the other boxes; the others may wind up with the "bedroom" boxes, or the "family room". What SQL's doing is a bit more organized than that, admittedly - but it's making no effort to group what it moves logically, it's just moving it as far as it can.

Hope this is helpful.