Monitor SQL database size increase

[Jason Eales]

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

[#2]

I have a feeling that counter looks at the total size, not the growth as you think.  https://docs.microsoft.com/en-au/sql/relational-databases/performance-monitor/sql-server-databases-o...

[#3 · Accepted solution]

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.