Bsrgvty

I have database of size 537gb. How can I find out which object is taking space.
I executed sp_spaceused, which showed me unallocated space is 502GB and 88GB used space. How can I release unallocated space to get some free space. My HDD is getting full due to this. Please advise.

Edit: I have checked via below script as well. But total UsedSpaceMB of each table is 47GB. Still I am unable to figure out how DB size is 537 GB.

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, SUM(a.used_pages) * 8 AS UsedSpaceKB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB, CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i
 ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p
 ON i.object_id = p.OBJECT_ID
 AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
 ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s
 ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%'
 AND t.is_ms_shipped = 0
 AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY 7 DESC

It appears that your database data file has empty space.

If you know for sure that the data file will not grow out again, you could shrink the file.

If you have to shrink, the cleanest way is doing this with TRUNCATEONLY so you don't move any data pages and only release the space back to the OS if it is possible.

You can get the data file(s) with this query, change the databasename to your database.

SELECT [name]
FROM sys.master_files
WHERE database_id = db_id('DatabaseName')
AND type_desc = 'ROWS';

Afterwards you could try shrinking the data page until 100GB without any movement.

Use [DatabaseName]
GO
DBCC SHRINKFILE ('name',102400 ,TRUNCATEONLY);
-- try to shrink until 100GB, without moving data pages

You can read up on shrinking date files and...

• why it sucks here
  


• why it's bad for performance here
  

You can use a script something like this to find tables and their sizes:

 SELECT name = OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id), 
 rows = SUM(CASE
 WHEN index_id < 2
 THEN row_count
 ELSE 0
 END), 
 reserved_mb = 8 * SUM(reserved_page_count) / 1024, 
 data_mb = 8 * SUM(CASE
 WHEN index_id < 2
 THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
 ELSE lob_used_page_count + row_overflow_used_page_count
 END) / 1024, 
 index_mb = 8 * (SUM(used_page_count) - SUM(CASE
 WHEN index_id < 2
 THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
 ELSE lob_used_page_count + row_overflow_used_page_count
 END)) / 1024, 
 unused_mb = 8 * SUM(reserved_page_count - used_page_count) / 1024
FROM sys.dm_db_partition_stats
WHERE object_id > 1024
GROUP BY object_id
ORDER BY reserved_mb DESC;

The fastest and simplest way is using a standard report.
Right click on the database: