Our MDF file was originally 550GB in size (log is just 10MB), and the biggest table we had
[HourlyCounters] was 140 million rows with 580 columns (varchar). It's a table with a very inefficient schema, but there's nothing we can do about that.
Anyways, after moving some data around, I was able to drop
[HourlyCounters]. So now, the new table (with the same name) has 50K rows. I then ran SHRINKDATABASE and SHRINKFILE, and the MDF went down about 40GB. I know that's a decent chunk, but I know for a fact that this dropped table was larger than that (the current backup of that table with only 7M rows is 63GB).
How can SQL Server release all that free space? The new version of
[HourlyCounters] doesn't currently have any indexes, but I know the old one did have them.
It's worth mentioning how I went about dropping the table:
- Rename the original table
[HourlyCounters_Old]with a right-click in SSMS and Rename. This table had indexes.
- Create a new table
[HourlyCounters]with no indexes so that processes that stored in this table are not affected.
- Create table
[HourlyCounters_New]where I would store the most recent data from
[HourlyCounters_New]now has 7M rows.
Other info that may help: The drive where the data files are stored (E:) is 1.89TB, with 1.44TB used and 468GB free.
This is the result of the following query:
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files; name AvailableSpaceInMB DB 53490.812500 DB_log 441.703125