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