Add support for database size metrics

[syron]

Component(s)

receiver/sqlserver

Is your feature request related to a problem? Please describe.

From an AO (Application Operations) perspective it is hard to keep track of the database sizes without either scheduling scripts or manually execute them to get to know the size of the databases. In some scenarios a sudden growth could be fatal for the performance of the application. At the same time, non-growth could be a result of a problem.

Describe the solution you'd like

I would like to be able to get all databases it's sizes (for all file types - Logs and Data) as metrics in MB (MegaBytes).

A solution could be running the following the query and structure the metrics to something like:

• sql.databasesize{DatabaseName}{LogicalName}{FileType}

SELECT 
    db.[name] AS [DatabaseName], 
    mf.[name] AS [LogicalName], 
    mf.[type_desc] AS [FileType], 
    mf.[physical_name] AS [Path], 
    CAST(
        (mf.[Size] * 8
        ) / 1024.0 AS DECIMAL(18, 1)) AS [SizeInMB], 
    'By '+IIF(
            mf.[is_percent_growth] = 1, CAST(mf.[growth] AS VARCHAR(10))+'%', CONVERT(VARCHAR(30), CAST(
        (mf.[growth] * 8
        ) / 1024.0 AS DECIMAL(18, 1)))+' MB') AS [Autogrowth], 
    IIF(mf.[max_size] = 0, 'No growth is allowed', IIF(mf.[max_size] = -1, 'Unlimited', CAST(
        (
                CAST(mf.[max_size] AS BIGINT) * 8
        ) / 1024 AS VARCHAR(30))+' MB')) AS [MaximumSize]
FROM 
     sys.[master_files] AS mf
     INNER JOIN sys.[databases] AS db ON
            db.[database_id] = mf.[database_id]

Describe alternatives you've considered

Another way of doing this would be to look into the sqlserverqueryreceiver, but monitoring file size and database size is pretty crucial from an AO and DBA (Database Admin).

Additional context

No response

[github-actions[bot]]

Pinging code owners:

• receiver/sqlserver: @djaglowski @StefanKurek

  See Adding Labels via Comments if you do not have permissions to add labels yourself.

[github-actions[bot]]

This issue has been inactive for 60 days. It will be closed in 60 days if there is no activity. To ping code owners by adding a component label, see Adding Labels via Comments, or if you are unsure of which component this issue relates to, please ping @open-telemetry/collector-contrib-triagers. If this issue is still relevant, please ping the code owners or leave a comment explaining why it is still relevant. Otherwise, please close it.

Pinging code owners:

• receiver/sqlserver: @djaglowski @StefanKurek

See Adding Labels via Comments if you do not have permissions to add labels yourself.