6

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.

Database Size T-SQL DB Info

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

enter image description here

9

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
3

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;
3

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

enter image description here

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.