SOLVED

Monitor SQL database size increase

%3CLINGO-SUB%20id%3D%22lingo-sub-364490%22%20slang%3D%22en-US%22%3EMonitor%20SQL%20database%20size%20increase%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364490%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20monitor%20how%20the%20size%20of%20my%20database%20increases%20over%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20SQL%20server%20VM%20and%20have%20wired%20up%20the%20Log%20Analytics%20and%20set%20it%20to%20capture%20the%20SQL%20performance%20counter%20called%20%22SQLServer%3ADatabases(*)%5CData%20Files(s)%20Size%20(KB)%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20is%20being%20captured%20because%20when%20I%20run%20the%20following%20query%20I%20get%20results.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EPerf%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20ObjectName%20%3D%3D%3C%2FSPAN%3E%3CSPAN%3E%22SQLServer%3ADatabases%22%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20CounterName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Data%20File(s)%20Size%20(KB)%22%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20InstanceName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Jason_DB%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%20CounterName%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%20CounterValue%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F86504iA39984CDE5BB4A60%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Annotation%202019-03-12%20094942.jpg%22%20title%3D%22Annotation%202019-03-12%20094942.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20running%20a%20SQL%20Job%20over%20night%20on%20the%20database%20to%20insert%20two%20rows%20into%20a%20table%20every%205%20min%20but%20I'm%20only%20seeing%20the%20database%20size%20of%20%228%2C192%22!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20chart%20is%20linear%20and%20show%20no%20%22Data%20file%22%20size%20increase!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20something%20wrong%20with%20my%20query%20or%20do%20I%20not%20understand%20the%20SQL%20performance%20counter%20in%20collecting%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrent%20query%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EPerf%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20ObjectName%20%3D%3D%3C%2FSPAN%3E%3CSPAN%3E%22SQLServer%3ADatabases%22%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20CounterName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Data%20File(s)%20Size%20(KB)%22%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20InstanceName%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Jason_DB%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%20CounterName%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%20CounterValue%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20avg%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ECounterValue%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20CounterName%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%20bin%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ETimeGenerated%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%20%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3Em%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Erender%3C%2FSPAN%3E%3CSPAN%3E%20timechart%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERendered%20chart%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F86505i82D83B863DB99F5C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Annotation%202019-03-12%20095522.jpg%22%20title%3D%22Annotation%202019-03-12%20095522.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-364490%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20Database%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-365759%22%20slang%3D%22en-US%22%3ERe%3A%20Monitor%20SQL%20database%20size%20increase%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-365759%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Clive%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20as%20it%20turns%20out%20it%20was%20measuring%20correctly!%20I%20was%20not%20inserting%20enough%20data%20to%20force%20the%20datafile%20autogrowth%20to%20occur.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20when%20I%20loaded%20the%20insert%20up%20we%20had%20to%20occurrences%20of%20the%20datafile%20growing.%20This%20showed%20on%20the%20Perfmon%20on%20the%20SQL%20server%20and%20was%20captured%20by%20the%20Azure%20Log%20Analytics%20workspace.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-365374%22%20slang%3D%22en-US%22%3ERe%3A%20Monitor%20SQL%20database%20size%20increase%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-365374%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20feeling%20that%20counter%20looks%20at%20the%20total%20size%2C%20not%20the%20growth%20as%20you%20think.%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-au%2Fsql%2Frelational-databases%2Fperformance-monitor%2Fsql-server-databases-object%3Fview%3Dsql-server-2014%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-au%2Fsql%2Frelational-databases%2Fperformance-monitor%2Fsql-server-databases-object%3Fview%3Dsql-server-2014%26nbsp%3B%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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

 

 

 

 

2 Replies
Highlighted

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

Highlighted
Best Response confirmed by Jason Eales (Occasional 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.