Database capacity - used and empty only showing data for master database

[jedecastro]

Hi Team, we need to provide monthly report on SQL server database capacity - (current size, used size, %free).  

I'm looking at these 3 metrics: sql-server.databases.file.size, sql-server.databases.file.usedSpace, sql-server.databases.file.emptySpace

The issue is only sql-server.databases.file.size shows data for all databases.  The other 2 are only showing data for the "master" database and nothing else.

Does anyone know how to get all databases data from sql-server.databases.file.usedSpace and sql-server.databases.file.emptySpace ?

Extension version is  Microsoft SQL Server 2.2.2

[p_devulapalli]

@jedecastro How are the endpoints configured in the extension?

usedSpace and emptySpace is only reported for the database that the extension is currently connected to, if you want this metric for other databases , you need to have the config updated 

[jedecastro]

Thank you Phani, I'm actually a DBA so I'm not sure how the extension was configured.  Do you know how to update the extension to get these 2 information for all databases? What values do we need to add to the endpoints/extension please?

[p_devulapalli]

@jedecastro You need someone who has access to Dynatrace and has permissions to update the extension configuration. Below link has details around how to update the endpoint details , hope this helps

https://docs.dynatrace.com/docs/shortlink/extend-microsoft-sql#define-endpoints

[jedecastro]

Thank you Phani for the link.  I do have access to the extension and I can add endpoints.  But just to confirm that I understand correctly, if my SQL instance has 100 databases, in order for us to get the used/free space, we need to create 100 endpoints in the extension for each of those databases?

If yes, then this is not very efficient as we support 100s of instances with 10s/100s databases.. 😞

Do you know if Dynatrace has a plan to fix this?

[p_devulapalli]

@jedecastro From what I see its not really a limitation of Dynatrace but the way SQL server exposes the views . sys.allocation_units from where the space metrics are calculated exists inside each database unlike sys.master_files which is server wide

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-tra...

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-allocation-units...

Here is a idea which is open for the same and some context around why its the way it is currently 

https://community.dynatrace.com/t5/Product-ideas/Database-file-usage-monitor-for-all-Databases/idi-p...