Need assistance shrinking MDF File

Question

Verbose logging was accidentally left on for one of our database ballooning our database MDF file from roughly 90GB to 192GB.

We've disabled verbose logging, truncated the logging table but now we want to shrink the MDF file to reclaim disk space as the drive the MDF file is on is only around 200GB. I don't have the luxury of increasing its size. Our disk space monitoring tool is alerting me that drive space is less than 8% over and over and over.

Given this is the first instance this has ever happened, we don't expect to ever need that extra 102GB of disk space again. We'd like to reclaim it.

I've tried to use the GUI to shrink the database and the files but it doesn't actually change in size. I've also tried using DBCC SHRINKFILE and DBCC ShrinkDatabase with NoTruncate and TruncateOnly. No matter what I do the file doesn't seem to budge.

One potential issue is that somehow the database initial size is 188GB. Deleting and re-creating the database isn't a very practical option for us so how can I shrink the MDF file and reclaim our disk space?

Running dbcc shrinkfile('My_Data'), I get the following results:

DBID: 5 
Field: 1
CurrentSize:24030464
MinimumSize: 128
UsedPages: 17770472
EstimatedPages: 17770472

The database MDF file is still the same size (192,243,712 KB).

The table has a clustered index:

Name:TRACES
ObjectID: 394588594
Type: Clustered Index/b-tree

The query:

SELECT
    name,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

...for the database I am trying to shrink, returns:

Name: My_Data AvailableSpaceInMB: 48693.375000
Name: My_Log  AvailableSpaceInMB: 67028.437500

I've also tried shrinking in small chunks using a PowerShell script that shrinks in 20MB increments. That also doesn't change the size of the MDF file.

I've tried dbcc shrinkfile(My_Data) without any other options from SQL Management Studio. It runs for a long time, finishes, and the MDF file is still the same size. Specifying a target size, it runs for a good 30-40 minutes (finishes) and nothing changes file size wise.

I ran this: DBCC SHRINKFILE (N'My_Data' , 139045) and get the following results:

DbId: 5
Field: 1
CurrentSize: 24030464
MinimumSize: 128
UsedPages: 17808744
EstimatedPages: 17808744

Answer 1

I've tried to use the GUI to shrink the database and the files but it doesn't actually change in size.

You said that you truncated large table, in such case you might not be able to shrink "immediately". From Dropping and Rebuilding Large Objects

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after a transaction commits. This implementation supports both autocommit and explicit transactions in a multiuser environment, and applies to large tables and indexes that use more than 128 extents.

It also says that

Because the physical phase occurs after a transaction commits, the storage space of the table or index might still appear as unavailable. If this space is required for the database to grow before the physical phase is completed, the Database Engine tries to recover space from allocation units marked for deallocation. To find the space currently used by these allocation units, use the sys.allocation_units catalog view.

Other reason why you would not be able to shrink immediately because records are marked ghosted. And shrink will actually give you space backup after background Ghost Cleanup operation has completed, some more information about Ghost Cleanup Task

Initial size as such is not initial size actually, its the current size. You can shrink below initial size. What I suggest you is wait for some time till background operation finishes. Use dbcc shrinkfile tsql command to shrink the files and shrink in small chunks.

Shrinking is single threaded and takes time.

PS: If you are not aware, please note that shrinking causes massive logical fragmentation so don't forget to rebuild fragmented indexes after shrinking is done

Answer 2

Use dbcc shrinkfile('mdfname'). Don't specify notruncate or truncateonly. I've never had to use those options before so I looked them up and they won't do what you want.

• Notruncate doesn't release the free space from the end of the file back to the OS.
• Truncately only releases free space from the end of the file but doesn't move pages around to make that space. Seeing as your pages will be all over the file it won't have any free space at the end to release.

If it still doesn't shrink check the output of sp_spaceused and make sure space is actually available. Also try doing an index rebuild on your logging table and trying again - if it's a heap then sometimes space isn't released until this is done (I've seen this but I don't know why it happens).

---

Assuming you work this out let's talk about what happens next. As Thomas Pullen mentioned if the database is in full recovery model this will expand your log massively and if you are in an AG or mirror they could go out of sync for a while. Make sure you don't hit a limit/fill the disk and stop production.

Upon completion everything will be fragmented in the database. On your next index maintenance the log file will blow out to a large size again so you will need to monitor that for the same reason. But after this you can shrink your log and you'll be back to normal.

Answer 3

Try dbcc cleantable on the tables that you performed truncation or rebuild indexes. I had run the shrinkfile in smaller portions and seperatly with smaller block sizes. None of these actually reclaimed any space even though they completed successfully without error. The cleantable took a long time to complete, and was very disk intensive. Once the cleantable completed, I was able to run the DBCC shrinkfile to the size I needed.

Answer 4

Bear in mind that shrinking generates lots of transaction log, so you will need very carefully to manage the size of your transaction log and its backups, while the shrink is running.

Do it in small-ish chunks like 2GB or 5GB, maybe start even smaller, and see how you go. That way if it causes issues while running they hopefully won't last too long. Shrinking is a massively I/O-intensive operation so be prepared for significant performance impact. Run at low- or no-activity times.