2

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[50]). 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] to [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] (now called [HourlyCounters_Old]). [HourlyCounters_New] now has 7M rows.
  • Drop [HourlyCounters_Old]

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

Thanks.

  • Is the database never going to grow again? If it is, why are you going to reclaim that space just so the database can occupy it again? Will you lease it out short-term? – Aaron Bertrand Jul 14 '15 at 15:12
  • The database will grow again, but not to that size. Most of the tables have historical data from previous years. In this case, it had data from 2013, which we don't use. – vmgmail Jul 14 '15 at 15:14
  • I then ran SHRINKDATABASE and SHRINKFILE - why would you do both shrink database and file ? Even if you have to shrink your database (keeping in mind that it wont grow and this caution), use SHRINKFILE. – Kin Shah Jul 14 '15 at 15:14
  • So it grew to 550 GB, you removed 63 GB and it only went down by 40 GB when you shrank. So you want that other 23 GB removed too. You're telling me your database won't grow 4%? Or even being generous, and shrinking to remove all of the 53 GB of unused space, 10%? I still find that hard to believe. Consider that shrinking and growing files is expensive and disruptive, and weigh that against the benefit of reclaiming a little bit of disk space (relatively), and only temporarily. You should re-think what you're trying to accomplish and why. – Aaron Bertrand Jul 14 '15 at 15:17
  • @AaronBertrand: Nope. 63GB is the new table with 7M rows. My point there was that, if a 7M table with exactly the same schema (and no indexes) took up 63GB, then a 138M-row table must be exponentially bigger. – vmgmail Jul 14 '15 at 15:21
1

Exactly how did you run the SHRINK commands? My understanding is that by default it just releases unused space at the end of the file, but you can also have the database compact itself first to make more space available. This is a slow and expensive operation, though; around an hour per 10 GB in my recent experience.

That said, I suspect your database is using more space than it ought to due to fragmentation. If you run a query like this:

SELECT TOP 10
    '[' + S.name + '].[' + O.name + ']' AS TableName,
    CASE WHEN I.type_desc = 'HEAP' THEN '(heap)' ELSE '[' + I.name + ']' END AS IndexName,
    Stats.avg_fragmentation_in_percent, Stats.fragment_count
FROM
    sys.indexes AS I
    INNER JOIN sys.objects AS O ON I.object_id = O.object_id
    INNER JOIN sys.schemas AS S ON O.schema_id = S.schema_id
    CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), O.object_id, I.index_id, NULL, NULL) AS Stats
WHERE
    S.name NOT IN ('sys')
ORDER BY
    Stats.avg_fragmentation_in_percent * Stats.fragment_count DESC

...you'll see the tables and indices which are both large and badly fragmented. Running ALTER INDEX MyIndx ON MyTBl REBUILD on a few of those should recover some space. As Aaron Bertrand said, you probably don't want to actually free this space with SHRINK; your database will need it sooner or later.

Heap tables are prone to wasted space; using ALTER TABLE MyTbl REBUILD on your bigger tables may help too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.