Query to get Database size and table size.
Query to get Database size in GB.
SELECT D.NAME, Cast((SUM(MF.SIZE) * 8.00 /1024.00/1024.00)as Numeric(18,2)) AS [SIZE (In GB)] FROM SYS.MASTER_FILES MF INNER JOIN SYS.DATABASES D ON D.DATABASE_ID = MF.DATABASE_ID WHERE D.DATABASE_ID > 4 GROUP BY D.NAME ORDER BY [SIZE (In GB)] DESC
Query to get Table size in GB with no of rows.
SELECT SCHEMA_NAME(T.Schema_id) + '.' + T.Name AS Tablename, SUM( CASE WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows ELSE 0 END ) AS Rowcounts, CAST(( SUM(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC( 18, 3)) AS Totalspacegb FROM SYS.Tables T INNER JOIN SYS.Indexes I ON T.Object_id = I.Object_id INNER JOIN SYS.Partitions P ON I.Object_id = P.Object_id AND I.Index_id = P.Index_id INNER JOIN SYS.Allocation_units A ON P.Partition_id = A.Container_id INNER JOIN SYS.Filegroups Fg ON I.Data_space_id = Fg.Data_space_id INNER JOIN SYS.Database_files Df ON Df.Data_space_id = Fg.Data_space_id WHERE T.Is_ms_shipped = 0 AND I.Object_id > 255 GROUP BY SCHEMA_NAME(T.Schema_id) + '.' + T.Name ORDER BY Totalspacegb DESC
Query to get Service Broker Queue size in GB with no of rows.
Select OBJECT_NAME(IT.parent_object_id),SUM( CASE WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows ELSE 0 END ) AS Rowcounts,CAST(( SUM(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC( 18, 3)) AS Totalspacegb From sys.partitions P Inner Join sys.internal_tables IT On IT.OBJECT_ID = P.OBJECT_ID INNER JOIN SYS.Allocation_units A ON P.PARTITION_ID = A.Container_id Where P.index_id In (1, 0) Group By OBJECT_NAME(IT.parent_object_id) Order by 3 DESC
Regards,
Nirav Gajjar