CheckShardSize.sql

EXEC sp_MSforeachdb '
IF ''?'' LIKE ''%Xdb.Collection.Shard%''
BEGIN
    USE [?];
    SELECT 
        DB_NAME() AS DatabaseName,
        name AS FileName,
        type_desc AS FileType,
        size/128 AS CurrentSizeMB,
        size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB,
        growth/128 AS GrowthSizeMB,
        CASE is_percent_growth 
            WHEN 1 THEN CAST(growth AS VARCHAR(10)) + ''%'' 
            ELSE CAST(growth/128 AS VARCHAR(10)) + '' MB'' END AS GrowthSetting
    FROM sys.database_files;
END
';