MDF file full - cannot find the source of its giant size

Question

All,

I am completely at a loss here and I'm hoping you guys can help me out on this one. I am experiencing the following situation on an SQL Server 2008 R2 database.

MDF-file: Allocated space: 87392.69 MB Available space: 256.13 MB

The LDF file is only a few megabytes, most of which are available. So as you can see my database is pretty full at the moment. I tried looking for the source of this, but here is where things get "weird".

When check the top tables by usage the top table was only 3 GB big, followed by a few of 1 GB. I've summarized all those totals and came to something around +- 20 GB of data. There's a giant gap here... and I'm not sure where it's coming from. I've been googling for the last few hours hoping to get some pointers, I've checked if any BLOB data was causing this but that seems to be not the case as far as I can see.

Am I missing something here? Did anyone experience something like this before? I can't find anything related to this so I'm completely at your mercy.

If you need any specific details I'll add them when requested.

Top 10 tables based on Aaron's query. (masked names for security reasons)

Thanks in advance

Answer 1

Identify the top 10 biggest tables/indexes with this as a starter:

SELECT TOP (10) s.name, o.name, ps.row_count,
  used_bytes = ps.used_page_count * 8192,
  reserved_bytes = ps.reserved_page_count * 8192 
FROM sys.dm_db_partition_stats AS ps 
INNER JOIN sys.objects AS o
ON ps.[object_id] = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.is_ms_shipped = 0
ORDER BY reserved_page_count DESC;

SQL Server doesn't just occupy 87 GB for nothing - the space is being used somewhere. Maybe it's in a system table:

SELECT TOP (10) s.name, o.name, ps.row_count,
  used_bytes = ps.used_page_count * 8192,
  reserved_bytes = ps.reserved_page_count * 8192 
FROM sys.dm_db_partition_stats AS ps 
INNER JOIN sys.all_objects AS o -- change this to all_objects
ON ps.[object_id] = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
-- WHERE o.is_ms_shipped = 0 -- comment this out
ORDER BY reserved_page_count DESC;

Would also be curious to see what size the backup is, and what happens if you try shrink the data file.