Forum Discussion

Jason Eales's avatar
Jason Eales
Copper Contributor
Mar 11, 2019
Solved

Monitor SQL database size increase

Hi

 

I want to be able to monitor how the size of my database increases over time.

 

I have created a SQL server VM and have wired up the Log Analytics and set it to capture the SQL performance counter called "SQLServer:Databases(*)\Data Files(s) Size (KB)". 

 

The data is being captured because when I run the following query I get results.

 

Perf
| where ObjectName =="SQLServer:Databases" and CounterName == "Data File(s) Size (KB)"  and InstanceName == "Jason_DB"
| project TimeGenerated, CounterName, CounterValue

 

 

 

I have been running a SQL Job over night on the database to insert two rows into a table every 5 min but I'm only seeing the database size of "8,192"!

 

The chart is linear and show no "Data file" size increase! 

 

Is there something wrong with my query or do I not understand the SQL performance counter in collecting?

 

Current query

Perf
| where ObjectName =="SQLServer:Databases" and CounterName == "Data File(s) Size (KB)"  and InstanceName == "Jason_DB"
| project TimeGenerated, CounterName, CounterValue
| summarize avg(CounterValue) by CounterName, bin(TimeGenerated, 5m)
| render timechart

 

Rendered chart

 

 

 

 

 

azure monitor
SQL Database
  • Jason Eales's avatar
    Jason Eales
    Mar 13, 2019

    Hi Clive

     

    So as it turns out it was measuring correctly! I was not inserting enough data to force the datafile autogrowth to occur.

     

    So when I loaded the insert up we had to occurrences of the datafile growing. This showed on the Perfmon on the SQL server and was captured by the Azure Log Analytics workspace.

3 Replies

Resources