Filegroup

Hi,

I started using the IDERA SQLdm. is there a table where i can query the filegroup information? like free space, etc?

Carlo

  • Did you look through the canned reports in DM? I always forget to do that and am usually surprised but what's in there.



    However, I've used this script for a long time to quickly get a feel for what's going on with drives and database file growth. It might not be exactly what you're looking fore but it might get you close.



    USE SQLdmRepository;
    GO

    DECLARE @ServerName AS VARCHAR(50);
    DECLARE @DriveName AS CHAR(1);
    DECLARE @OldestCollectionDate AS DATETIME;
    DECLARE @DatabaseName AS Varchar(200);


    -- Enter Server Name, Drive Letter, Oldest Collection Date, Database Name

    SET @Servername = 'IV-SQL-DBAP1';
    SET @DriveName = 'M';
    SET @OldestCollectionDate = '2017-04-01';
    SET @DatabaseName = 'SQLdmRepository'

    --------------------------------------------------
    --------------------------------------------------

    SELECT InstanceName,
    DriveName,
    (TotalSizeKB / 1024) / 1024 AS [Total GB],
    (UnusedSizeKB / 1024) / 1024 AS [Unused GB],
    (UnusedSizeKB / TotalSizeKB) * 100 AS [Percent Free],
    MAX(DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), UTCCollectionDateTime)) AS [Collection Date max]
    FROM DiskDrives AS D
    INNER JOIN MonitoredSQLServers M ON D.SQLServerID = M.SQLServerID
    WHERE InstanceName LIKE '%'+@ServerName+'%'
    AND DriveName = @DriveName
    AND DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), UTCCollectionDateTime) >= @OldestCollectionDate
    GROUP BY InstanceName,
    DriveName,
    TotalSizeKB,
    UnusedSizeKB,
    (UnusedSizeKB / TotalSizeKB) * 100
    ORDER BY InstanceName, DriveName, MAX(DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), UTCCollectionDateTime)) DESC;

    ---------------------------------------------------
    ---------------------------------------------------
    SELECT m.InstanceName,
    n.DatabaseName,
    CONVERT( DECIMAL(15, 2), d.DataFileSizeInKilobytes / 1024) / 1024 AS [database file GB],
    CONVERT( DECIMAL(15, 2), d.DataSizeInKilobytes / 1024) / 1024 AS [DataSize GB],
    CONVERT( DECIMAL(15, 2), d.IndexSizeInKilobytes / 1024) / 1024 AS [Index Size GB],
    CONVERT( DECIMAL(15, 2), d.TextSizeInKilobytes / 1024) / 1024 AS [Text Size GB],
    CONVERT( DECIMAL(15, 2), d.DataExpansionInKilobytes / 1024) / 1024 AS [Data Expansion Size GB],
    CONVERT( DECIMAL(15, 2), d.LogFileSizeInKilobytes / 1024) / 1024 AS [Log File Size GB],
    CONVERT( DECIMAL(15, 2), d.LogSizeInKilobytes / 1024) / 1024 AS [Log Size GB],
    MAX(DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), UTCCollectionDateTime)) AS [max datetime]
    FROM DatabaseSize d
    INNER JOIN SQLServerDatabaseNames n ON n.DatabaseID = d.DatabaseID
    INNER JOIN MonitoredSQLServers m ON m.SQLServerID = n.SQLServerID
    WHERE
    ----
    n.DatabaseName = @DatabaseName
    AND m.InstanceName = @ServerName

    ---- If you want info for all DBs on a server comment out the above lines and uncomment the block below
    --
    --d.DatabaseID
    --IN
    --(
    -- SELECT DatabaseID N
    -- FROM SQLServerDatabaseNames N
    -- INNER JOIN MonitoredSQLServers M ON N.SQLServerID = M.SQLServerID
    -- WHERE M.InstanceName = @ServerName
    --)
    ----

    AND DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), UTCCollectionDateTime) >= @OldestCollectionDate
    GROUP BY m.InstanceName,
    DataFileSizeInKilobytes,
    n.DatabaseName,
    d.DataFileSizeInKilobytes,
    d.DataSizeInKilobytes,
    d.LogFileSizeInKilobytes,
    d.LogSizeInKilobytes,
    d.IndexSizeInKilobytes,
    d.TextSizeInKilobytes,
    d.DataExpansionInKilobytes
    ORDER BY m.InstanceName,
    n.DatabaseName,
    MAX(DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), UTCCollectionDateTime)) DESC;