SOLVED

Monitor SQL database size increase

Copper Contributor

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

 

 

Annotation 2019-03-12 094942.jpg

 

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

 

Annotation 2019-03-12 095522.jpg

 

 

 

 

3 Replies

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...

best response confirmed by Jason Eales (Copper Contributor)
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.

1 best response

Accepted Solutions
best response confirmed by Jason Eales (Copper Contributor)
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.

View solution in original post