script to check size of Tables, Database and free space

  • 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

  • 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 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply