How to get database size:
run this script:
SELECT
so.object_id AS ObjectID,
so.name AS ObjectName,
(CONVERT(decimal(20,4),(SUM (ps.reserved_page_count) * 8))) / 1024 As Reserved_MB,
(CONVERT(decimal(20,4),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
) * 8)) / 1024 As Data_MB,
(CONVERT(decimal(20,4),(CASE WHEN (SUM(used_page_count)) >
(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
)) THEN (SUM(used_page_count) -
(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
))) ELSE 0 END) * 8)) / 1024 As Index_MB,
(SUM (
CASE
WHEN (ps.index_id < 2) THEN ps.row_count
ELSE 0
END
)) AS [RowCount]
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.objects AS so ON so.object_id = ps.object_id
WHERE so.object_id > 100
GROUP BY so.object_id, so.name
ORDER BY [Reserved_MB]Desc
Thanks,
Rami Heleg.
