October 24, 2023 at 5:05 pm
Can someone share script to check Table Sizes in GB. ,and size of DB in GB.
The scripts I see online are showing wrong numbers . Total Size of Tables=500 GB but DB size is 3 TB, doesnt make sense!
Azure MI Business Critical Production
October 24, 2023 at 6:03 pm
Database Disk Usage:
USE {your database here};
GO
WITH database_size
AS (
SELECT dbsize = sum(CASE WHEN df.type = 0 THEN df.size * 1.0 ELSE 0 END)
, logsize = sum(CASE WHEN df.type = 1 THEN df.size * 1.0 ELSE 0 END)
FROM sys.database_files df
)
, database_pages
AS (
SELECT reservedpages = sum(a.total_pages)
, usedpages = sum(a.used_pages)
, pages = sum(CASE WHEN it.internal_type IN (202, 204) THEN 0
WHEN a.type <> 1 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages
ELSE 0
END )
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id
)
SELECT us.database_size_mb
, reserved_mb = cast(us.reserved_mb AS decimal(18,6))
, us.unallocated_space_mb
, df.data_size
, transaction_log_size = us.log_size_mb
, percent_unallocated = cast(us.unallocated_space_mb * 100 / df.data_size AS decimal(5, 2))
, percent_reserved = cast(us.reserved_mb * 100 / df.data_size AS decimal(5, 2))
, percent_data = cast(us.data_mb * 100 / df.data_size AS decimal(5, 2))
, percent_index = cast(us.index_mb * 100 / df.data_size AS decimal(5, 2))
, percent_unused = cast(us.unused_mb * 100 / df.data_size AS decimal(5, 2))
FROM database_size ds
CROSS JOIN database_pages dp
CROSS APPLY (VALUES (iif(ds.dbsize >= dp.reservedpages, (ds.dbsize - dp.reservedpages) / 128.0, 0)
, (ds.dbsize + ds.logsize) / 128.0
, dp.reservedpages / 128.0
, dp.pages / 128.0
, ds.logsize / 128.0
, (dp.usedpages - dp.pages) / 128.0
, (dp.reservedpages - dp.usedpages) / 128.0)
) us (unallocated_space_mb, database_size_mb, reserved_mb, data_mb, log_size_mb, index_mb, unused_mb)
CROSS APPLY (VALUES (us.reserved_mb + us.unallocated_space_mb)) df(data_size)
Disk Usage by File
USE {your database here};
GO
SELECT df.file_id
, df.type_desc
, df.name
, df.physical_name
, file_size_mb = df.size / 128.0
, fp.space_used_mb
, space_used_percent = cast(fp.space_used_mb * 100.0 / (df.size / 128.0) AS decimal(5,2))
FROM sys.database_files df
CROSS APPLY (VALUES (fileproperty(df.name, 'SpaceUsed') / 128.0)) AS fp(space_used_mb)Disk Usage by Table
USE {your database here};
GO
SELECT table_name = o.name
, index_name = i.name
, i.index_id
, file_group = f.name
, ps.reserved_mb
, ps.used_mb
, ps.pages_mb
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.object_id = o.object_id
CROSS APPLY (
SELECT reserved_mb = sum(a.total_pages) / 128.0
, used_mb = sum(a.used_pages) / 128.0
, pages_mb = sum(CASE WHEN it.internal_type IN (202, 204) THEN 0
WHEN a.type <> 1 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages
ELSE 0
END ) / 128.0
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id
WHERE p.object_id = i.object_id
AND p.index_id = i.index_id
) ps
WHERE o.type = 'U'
ORDER BY
o.name
, i.index_id; --ps.usedpages DESC;
GOIf you want total usage for a table - instead of broken out by each table then you can adjust it to group and sum. I would probably just add a SUM() OVER() to get the totals.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply