Forum Discussion
Jason Eales
Mar 11, 2019Copper Contributor
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
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.
- MonitorpackCopper ContributorAn Example on how to monitor SQL
https://www.monitorpack.com/help-en.php?page=support-informatique-en/how-to-monitor-sql-server.php - CliveWatson
Microsoft
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-object?view=sql-server-2014
- Jason EalesCopper Contributor
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.