tests/Test-SqlDatabaseFileGrowth.ps1

function Test-SqlDatabaseFileGrowth {
    [CmdletBinding()]
    param (
        [parameter()][string] $TestName = "SQL Database File Growth",
        [parameter()][string] $TestGroup = "configuration",
        [parameter()][string] $TestCategory = "SQL",
        [parameter()][string] $Description = "Results of SQL file auto-growth configuration",
        [parameter()][hashtable] $ScriptParams
    )
    try {
        $startTime = (Get-Date)
        #[int]$Setting = Get-CmHealthDefaultValue -KeySet "keygroup:keyname" -DataSet $CmHealthConfig
        $MaxRunTimeSeconds = 30
        [System.Collections.Generic.List[PSObject]]$tempdata = @() # for detailed test output to return if needed
        $stat   = "PASS" # do not change this
        $except = "WARNING" # or "FAIL"
        $msg    = "No issues found" # do not change this either
        $query = "SELECT
s.database_name,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
s.backup_start_date >= DATEADD(dd,-CONVERT(INT,7),GETDATE())
ORDER BY s.database_name, backup_finish_date DESC, backup_start_date ASC"

        $res = Get-CmSqlQueryResult -Query $query -Params $ScriptParams
        $res | Foreach-Object {
            if ($_.TimeTaken -gt $MaxRunTimeSeconds) {
                $stat = $except
                $msg = "one or more growth tasks took longer than $MaxRunTimeSeconds seconds"
            }
            $tempdata.Add(
                [pscustomobject]@{
                    Database = $_.database_name
                    BackupType = $_.BackupType
                    BackupSize = $_.bkSize
                    RunTime = $_.TimeTaken
                    BackupStart = $_.backup_start_date
                }
            )
        }
    }
    catch {
        $stat = 'ERROR'
        $msg = $_.Exception.Message -join ';'
    }
    finally {
        Set-CmhOutputData
    }
}