How to get sql database space using KQL?

Question

How to get sql database space using KQL?

Answer 1

Yes, total space used for a SQL database as well total space.

space used = 2.1GB

total space = 10GB

Thanks, Mark

Answer 2

Hi Mark A. Pizarro

Thank you for getting back.

You can map to a SQL table...Querying an external table of type SQL.

You can also pack that into a request, you can use sql_request plugin.

Please let me know if that works for you.

Regards,

Oury

Answer 3

Hi Oury,

Thank you for your answers but I was looking something along the lines of

AzureMetrics

| where ResourceProvider == "MICROSOFT.SQL" and Resource != "MASTER"

| where TimeGenerated > ago(24h)

| where MetricName == "storage"

| summarize avg(Average) by Resource, bin(TimeGenerated, 1h)

| extend AverageMB = avg_Average / 1000000

| project TimeGenerated, Resource, AverageMB

But this is not outputting anything.

If this worked I was looking to replace Resource with name of database.

Thanks,

Mark

Answer 4

Hi Mark A. Pizarro

As suggested above, you can use KQL's sql_request plugin. You can run SQL queries through it, so something like this should give you what you're looking for.

evaluate sql_request(

'<SQL DB CONNECTION STRING>',

"SELECT DB_NAME() AS DB, size as TotalSpace , size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) AS FreeSpace FROM sys.database_files where type_desc = 'ROWS'"

Hope that helps.

Let me know the result and please do not forget to mark as accept answer if the reply was helpful.

Regards,

Oury