Gathering SQL Server database free space and space usage by schema


I did analysis for SQL Server database free space and space usage by schema twice in the last few months, without any outside monitoring tools. I wrote this down here for future reference. A significant part of the code came from this article here by The MAK, with slight modification. And I’ve verified that it gives the correct result.

[expand title=”T-SQL to get row count and space usage by schemas”]
[code language=”sql”]
–To get a particular schema, add “a3.name = ‘schemaName’”
SELECT
–(row_number() over(order by a3.name, a2.name))%2 as l1,
a3.name AS [schemaname],
count(a2.name ) as NumberOftables,
sum(a1.rows) as row_count,
sum((a1.reserved + ISNULL(a4.reserved,0))* 8) AS reservedKB,
sum(a1.data * 8) AS dataKB,
sum((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN
(a1.used + ISNULL(a4.used,0)) – a1.data ELSE 0 END) * 8 )AS index_sizeKB,
sum((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN
(a1.reserved + ISNULL(a4.reserved,0)) – a1.used ELSE 0 END) * 8) AS unusedKB
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, SUM (ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN (SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204) GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) WHERE a2.type <> ‘S’ and a2.type <> ‘IT’
group by a3.name
ORDER BY a3.name
[/code]
[/expand]

[expand title=”T-SQL to get database free space”]
[code language=”sql”]
— database free space by file
select
name
, filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,’SpaceUsed’)/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a
–sum of all database free space, excluding log file free space
select
sum(convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)))/1024 as FreeSpaceGB
from dbo.sysfiles a where groupid > 0
[/code]
[/expand]

,

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.