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
Best Answer
You said that you truncated large table, in such case you might not be able to shrink "immediately". From Dropping and Rebuilding Large Objects
It also says that
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