Friday 19 November 2010

SQL MP - Case of the missing DB size information.

When a colleague nudges you and says "You got SCOM open?  Just check a DB Size as I'm not seeing any data..." I tend to either sigh and wonder what they're on about or worry that somethings gone wrong with SCOM.

Well one of my colleagues asked me that question today, so I checked the SCOM console, went into the performance view in the SQL MP and, hmm... odd.  I can show the DB Size data for SQL 2000 DB's, but not SQL 2005 & 2008.

Oh, the DB information is the same.

But it's fine for SQL 2000


Little bit of googling later, and I find the bit of information that wasn't communicated around the office when the SQL MP was upgraded to 6.1.314.36
http://blogs.technet.com/b/kevinholman/archive/2010/08/16/sql-mp-version-6-1-314-36-released-adds-support-for-sql-2008r2-and-many-other-changes.aspx

The following monitors and rules have been deprecated:
Monitors:
  • DB Space Free (MB)
  • DB Log File Space Free (%)
  • DB Log File Space Free (MB)
  • Disk Free Space
Rules:
  • Collect Database Size (MB)
  • Collect Transaction Log Free Space (MB)
  • Collect Transaction Log Free Space (%)
  • Collect Transaction Log Size (MB)
ARGH!!
Microsoft hardcoded the value to 0 to stop the high config churn this rule was creating.

OK, so it's not going to be a simple rule re-enable as they've been deprecated for a reason.  However, the perf counter exists natively so it should be a simple task to collect that data.

Well I was going to find some time to look at it over the weekend and blog to process of doing it, but while I was doing my usual RSS feeds check, I spotted this on Kevin Holmans blog:
http://blogs.technet.com/b/kevinholman/archive/2010/11/19/collecting-sql-database-size-as-a-performance-counter.aspx

Thanks Kevin, you've just given me my weekend back.  Talk about timing!

1 comment:

kinemaster mod said...

very informative post. it like reading it. thanks for sharing your experience with us.