Collecting SQL Database size as a performance counter


There were some changes made in the most recent SQL MP I wrote about here:

SQL MP version 6.1.314.36 released–adds support for SQL 2008R2 and many other changes

 

Some of the noisy rules and monitors were disabled by default – and some workflows were deprecated, and replaced with new workflows.  Also – the SQL database class property discovery was changed – and now Config Churn is no longer an issue, at ALL, from the SQL MP.

This is great news!

However, one of the common things collected by this MP is no longer collected out of the box – Database Size.

This is normal.  A Management pack will (and should) focus on health.  It is not meant to be a collection of every performance collection for every possible counter that every customer might ever want.  It is totally expected that you as a customer, would examine what we collect out of the box – and disable anything you don’t care about.  It is also expected you would write additional rules and monitors to add monitoring for any Microsoft application, if you have additional items you want to see monitored or reported on.

Database Size is an example of that.

In the previous MP – DB size was captured in two places:

1.  The Database class property.  Previous versions of this MP had 4 properties of the database class, which would cause config churn.  These were Database Size (MB) (String), Database Size (MB) Numeric), Log Size (MB) (String), and Log Size (MB) (Numeric).  These are now deprecated – the discovery will hard code these values to “0” (zero) for all databases.  If you see that – this is NORMAL and by design.  We should not ever design a property of a class that will change often, as this one did.  Lesson learned.  🙂

image

 

2.  A performance collection rule.  This previous perf collection rule used a shared datasource (script) which was performing the free space monitoring and collection calulations.  This whole datasource was deprecated and replaced with a new model, and therefore this collection rule was deprecated as well.

image

 

Not to worry.  Database size is a simple performance object in perfmon, and very simple to collect.  Let’s create one if you think this is something you’d like to report on (largest DB’s report, etc)

 

The first step in creating a rule like this is to identify the data source.  In this case – in perfmon – there is a Performance counter for:

SQLServer:Databases\Data File(s) Size (KB)\*(all instances)

image

 

Next – lets identify a proper target.  Two obvious choices here would be the SQL Instance object, and the Database Object.  Since this is a simple performance collection rule, where I want to collect and insert ALL instances of the counter, I will choose the SQL instance as my target for this collection rule.  I want to replicate the way the SQL MP’s work – so I will actually create two rules – one to collect for SQL 2005, and one to collect for SQL 2008.  The reason I am doing this – is because if I targeted generically “SQL DB Engine” – the next version of SQL would be included in this parent class, but might use a different object/counter down the road.  So I will stick to known versions and perf counters.  So I will create my rule targeting “SQL 2008 DB Engine” and “SQL Server 2005 DB Engine”

I prefer to create my rules using the authoring console – so I will be performing this activity there.  You can do the same thing in the UI, using the wizards there as well.  First thing I will open the management pack that contains my custom SQL rules for SQL 2005. 

Go to File – Management Pack Properties.  We need to version our MP (increment by 1) since we will be changing it. 

We also need to add a reference here is we don’t already have it – to ensure this MP has a reference for the SQL 2005 Discovery MP.  This will allow us to choose SQL Classes later on when targeting our collection rules.  Click the references tab, and add the SQL 2005 discovery MP if it isn’t already present:

image

 

Then go to Health Model, Rules, New, Collection, Performance Based, Windows Performance Collection.

image

 

The first step is give my rule an ID.  This will be the ID of the management pack, plus some additional text.  It defaults to “NewElement” and we need to change that:

 

I will call mine “Microsoft.SQLServer.2005.Monitoring.Addendum.CollectSQL2005DBSize

Give the rule a display name that is in accordance with your custom rule naming standard:

Under “Target” – Browse all classes and find the Microsoft.SQLServer.2005.DBEngine. 

Under Category – change to Performance Collection.

When completed – here is how mine appears:

image

 

Click Next.

On this screen – we have the option to type in the performance counter, object, and instance we want to collect.

Great care should be taken here.  This is because the SQL DB Engine is a multi-instance object, and each instance appears differently in Perfmon.  If we don’t choose the correct object here – then we wont collect the data from all of our instances.  Let me explain.

In a “default instance” of SQL – the perf counter looks like this:

image

In a Named instance – it appears like the following:

image

If we typed in “SQLServer:Databases” we would only collect from the default instances of SQL in the environment.  If we typed in “MSSQL$I01:Databases” we would only collect the data from identically named instances in the environment.  However – we want to collect this from ALL instances.  In that case – we need to use a VARIABLE in the performance counter object – since the actual object names vary in Perfmon.  We can cheat by looking at a some other perf collection rules in the SQL MP and see how they handled this…. or we can look in discovered inventory and see if there is a good class property of our chosen class to handle this.

It just so happens that the SQL DB Engine class – has a property called “Performance Counter Object Name” that was created specifically for this purpose!  If you look at this value in discovered inventory, you can see these correspond perfectly with what we need:

image

Sweet!  And if you spot check a few Perf Collection rules in the SQL MP using our same target class, you’d find they also use this.

So – back to the authoring console – we need to use this object, as a variable, for our Perfmon Object.  Here is how:  There is a fly-out on the right – this will show all the class properties based on our target – simply choose the correct one:

 

image

 

That will drop the entire variable into the object.  We only need to add the actual perfmon object at the end (:Databases)

image

 

For the counter – that’s simple – just type in the counter exactly as it is by name in perfmon:  Data File(s) Size (KB)

For the instance – check the box to include all instances.

For the Interval – we don’t expect this to change often, so once an hour is fine.  (You could even do once or twice a day, but then our hourly reports would not be populated).

image

 

Click Next.  For the Optimization tab – this counter might be a good candidate for optimization – not to even collect the data unless there is significant change, but since I chose once per hour – I will not use optimization and get an actual perf record per hour, for each database.

Done!  Now import this into your management group. 

To test if our new rule is working – go to My Workspace, create a new performance view, and scope it to “Collected by specific rules”.  Choose your rule from the list….

image

Once your SQL 2005 Servers have downloaded your new MP, applied the new config, and sent up their first performance data sample (takes up to the frequency of the collection rule), you will see this view populated:

 

image

 

Now – you can also run a “Performance Top Objects” report – and create a new one for “My Largest Databases”  (required daily aggregation – so wait 24 hours for data to show up)

 

Now – can repeat this process for the SQL 2008 Database Engines, to ensure you are collecting DB size for SQL 2008 hosted databases as well.


Comments (33)

  1. Anonymous says:

    @David –

    I don't know of a good way to estimate that.  it comes down to how many collections per day, whether or not you use Optimized collection and how well that works, and then how long you retain raw, daily and monthly aggregations in the DW.  Too many factors to throw out a number.  The biggest impact to the warehouse is going to be number of servers and number of counters, both of which are low in your case.  For the opsDB, its servers/counters/rate.

  2. Anonymous says:

    DB size is VERY important to DBA's.  Many were miffed when we removed this from the SQL MP as they depended on this counter for reporting.  The reason so – most customers leverage autogrow, and they need to track the size of the databases for trending and capacity planning.  Many care very little about free space monitoring, which is the focus of our MP, when autogrow is set, other than the free space on disk or free space as a factor of the max autogrow limit.  Total DB size is very important, and allows customers to report on the largest DB's, and trend them over time to make storage decisions.

  3. Anonymous says:

    You can use a sample report from jimmy Harpers blog on this:

    blogs.technet.com/…/sample-queries-and-reports-from-my-mms-session.aspx

    He has a simple performance chart.  You can implement scaleby using my blog article on the collection rule.

  4. Anonymous says:

    @David –

    Use groups.  Static or dynamic.  You can use groups of Windows Computer objects, or groups of SQL DB Engine objects.  Then use the groups to create an override, disabling or enabling your new monitors.

  5. Anonymous says:

    @kelly –

    See:  blogs.technet.com/…/how-to-collect-performance-data-for-sql-databases-multi-instance-objects.aspx

    Write the rules to collect this according to the OTHER article – targeting database, then simply write a report using a generic performance report and schedule it using PDF or excel.

  6. Anonymous says:

    @Vincent –

    It is SUPER easy to change that to Megabytes… or whatever you want – using the ScaleBy function – which I document here:

    blogs.technet.com/…/how-to-monitor-a-process-on-a-multi-cpu-agent-using-scaleby.aspx

    Simply add the function like:  <ScaleBy>1000</ScaleBy> and this should divide the number by one thousand and provide megabytes from a counter that provides bytes.

  7. Anonymous says:

    Kelly – are you looking for a report that shows the Database Size listed in a configuration report table like that?  You cannot do that – that was a terrible idea and should have never existed.  SQL DB size should never have been a class property.  It works on SQL 2000 because we never fixed that MP by deprecating those discoveries.

    You can however, write a SQL based report with this information, but you would have to base it on a SQL query that is custom written.

  8. Anonymous says:

    @David –

    Simple – look at the counters in perfmon – under the instances.  If there is one or no instances – then use SQL DB Engine, or SQL 20xx DB engine.  If there is multiple instances (one for each DB) in perfmon, then target SQL Database, or SQL Server 20xx DB.

  9. Anonymous says:

    @Kelly – yes, however, there is also a table in the perf detail report.  

    You asked to export this to PDF or Excel.  ANY report can be exported to PDF or Excel.  What exactly are you looking for?

  10. Anonymous says:

    James – I have not seen that, if you target SQL DB engine – it should only run on the specific engine you target.  Its probably an issue if how you are creating your collection rule – not an agent proxy issue.  Agent proxy is a requirement and should not be changed.

  11. Have you got our office bugged Kevin?

    We spent a good hour or so going over this today in the office as one of the DBA's was not impressed when they went to check the size of a DB today only to find out we had upgraded the SQL MP.

    I promised him faithfully that I would look into this weekend and add a rule to collect the perf counter ready for Monday.

    Thank you ever so much for making my weekend free again!!!

  12. Vincent says:

    Is it also possible to change the data that is returned from KB to MB? Or should this be done by a script instead?

  13. Ralph says:

    Thank you Kevin!  I found this very helpful!

  14. Marc says:

    Hi Kevin,

    I´ve realized this successfull, and I have now the File Size of the SQL Database. Thanks…great

    Now i´d like to have how much is used from the file, and how big is my database really?

    And there is my Problem, I have no idea how to realize this.

    I cannot create a sql Query for this for reporting, I cannot bring it in SCOM like in the old MP.

    I hope you can help me?

    Thank you for helpand your great blog

    Best regards, Markus

  15. Roman Yuferev says:

    Hi! Sorry for late join – just found this blog recently 🙂 Kevin – do you really mean that DB size is useful? Could you please explain me how DBAs uses it? In fact SQL MP right now care about space monitoring for their databases. I'm asking because I rather involved in SQL MP development process.

    Thank you!

  16. blogi says:

    Thanks, this works like a champ for SCOM 2012 as well.

  17. adam says:

    hallo kevin how can i get thes progerm

  18. kelly says:

    Kevin, how can we get report with values listed in pdf or excel

  19. Kelly says:

    Thanks for the quick response. Do you suggest generic performance report based on rule?  It didn't work for me. i will try again.

  20. Kelly says:

    Kevin,

    In the generic Reports library performance and performance detail both were giving chart.

  21. Kelly says:

    Please ignore, if it a duplicate post. I am looking for this type of report. It works fine for sql 2000 databases. With new MP,  how to get this type of report for sql 2005/2008 and sql 2012?

    blogs.technet.com/…/inventory-and-configuration-reports.aspx

  22. kelly says:

    Do you suggest  using the reporting services t or custom performance detail report? Microsoft would have provided  simpler way to do this since it is important report for any organization.

  23. Kelly says:

    It doesn't need to be in configuration report. We need similar tabular report with database names and sizes.

  24. kelly says:

    I would appreciate any ideas on getting database sizes in tabular report instead of chart mentioned in this article. i would also like to implement scaleby to get readable values.

  25. Kelly says:

    Thanks, Kevin. Jimmy Harper's sample report worked for me. Regarding scaleby function,  I am not seeing xml from configuration tab of the Rule.

  26. David says:

    Hi Kevin,

    I want to monitor the below SQL performance counter for some 25 servers.

    SQLServer: Access Methods — Page Splits/sec

    SQLServer: Buffer Manager—Buffer Cache Hit Ratio

    SQLServer: Databases Application Database—Transactions/sec

    which one i have to target for rules (SQL 2008 DB engine or database)

  27. David says:

    Thanks Kevin for your Prompt reply. It sounds good.

    In my environment, i have around 50 SQL servers, but i want to monitor the performance counter only for  25 servers. Could you please help me how to accomplish this?

  28. David says:

    Hi Kevin,

    if i monitor the 30 SQL performance counters for 50 SQL servers, how much disk space it would consume? Can you provide some average estimate of the disk space then i will increase the size of my opsmgr and DW database

    Please advice

  29. subin says:

    HI Kevin,

    We are having SCOM 2007 and I need to collect the database growth report in a weekly manner. Is there any query for that. I tried looking into Richard's Stored procedure but unable to find the sql query.

  30. James says:

    I noticed that when using this method on an active/active sql cluster with an instance on both and have agent proxy enabled on both nodes for the scom agent, it seems to collect data multiple times. For example, if I want to collect log file size every hour for each instance, it will show that I collect it twice. I believe this is due to the agent proxy being enabled. I am targeting sql 2008 db engine. Do you have any suggestions on fixing this?

  31. James says:

    Thanks, I am going to open a case with CSS as I can replicate this and seems odd. Will post here

  32. Kevin,

    Just FYI: in version 6.3.173.1 of the System Center Monitoring pack for SQL Server, counters were added to track database size:

    MSSQL 2005: Collect Database Allocated Size (MB)

    MSSQL 2008: Collect Database Allocated Size (MB)

    MSSQL 2012: Collect Database Allocated Size (MB)

    I verified that these are working and match the measurements of the performance collection rule outlined above (when divided by 1024 to convert from KB to MB).

    However, it may be useful for folks to work through your performance collection rule to monitor the transaction log size. To do this, setup performance collection rules for Log File(s) Size (KB) and Log File(s) Used Size (KB)

    Thanks,

    Frank

  33. Dominique says:

    Hello Kevin,
    I tried to work with this but after exporting my xxx – SQL 2008 MP where I placed the override I am not able to import it in the Authoring Console as it is requesting 17 standard MPs and trying to mount them failed!!!
    Any idea?
    Thanks,
    Dom