ENTERPRISE geodatabase feature class size

[Danny_Thompson]

Hi, 

Please could you advise on the best way to extract ENTERPRISE geodatabase feature class sizes?

The Enterprise geodatabase sits on SQL Server.
Happy to accept SQL or Python as the method.

Something along the lines of:

SELECT [Database Name] = DB_NAME(database_id),

       [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN Type_Desc = 'LOG' THEN 'Log File(s)'

                     ELSE Type_Desc END,

       [Size in MB] = CAST( ((SUM(Size)* 8 ) / 1024.0) AS DECIMAL(18,2) )

FROM   sys.master_files

GROUP BY     GROUPING SETS

             (

                     (DB_NAME(database_id), Type_Desc),

                     (DB_NAME(database_id))

             )

ORDER BY     DB_NAME(database_id), Type_Desc DESC

GO

Database Name	Type	Size in MB	Size in GB
AProject1	TOTAL	625.0	0.6
AProject2	TOTAL	2846.9	2.8
AProject3	TOTAL	27794.8	27.8

[George_Thompson]

This is a tool that will report lots of information on your Enterprise Geodatabase: https://arcgismonitor.maps.arcgis.com/home/item.html?id=f343f6b2bbcf434386f6dde1e468e7ab

Use the SQL below at your own risk...............................

Here is some SQL that will provide size on disk.

DECLARE @TblNames Table
(
    COUNTER INT IDENTITY(1,1),
    tbl_name nvarchar(100) NULL
)
DECLARE @TableSizes AS TABLE
(
    [TblName] VARCHAR(255), [NumRows] INT,
    [Reserved_Size] VARCHAR(10), [Data_Size] VARCHAR(10), [Index_Size] VARCHAR(10), [Used] VARCHAR(10)
​)
 
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT s.NAME +'.'+t.name FROM sys.Tables t
JOIN sys.Schemas s ON s.SCHEMA_ID = t.schema_id
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
INSERT INTO @TableSizes
EXEC sp_spaceused @str
SET @I = @I +1
END
 
SELECT * FROM @TableSizes
ORDER BY numrows DESC

 

Here is some SQL with more data than above:

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name