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;
GO
If 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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy