How to find out which object is taking space? 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 &gt; 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