OpsMgr – SQL Server MP, SQL 2008 & Free Space Counters

Recently, we ran into a situation where I wanted to determine how much free space was available on both the OperationsManager and OperationsManagerDW databases over a period of time. When I went to find these counters in the Operations Console / Monitoring / Microsoft SQL Server / Databases there were no counters for these database in either the Database Free Space or Transaction Log Free Space performance views. Digging into this I found that some of the normal metrics which I would expect to be available are not if the database back-end is SQL 2008 (instead of SQL 2005). There was a good discussion on this available at: http://social.technet.microsoft.com/forums/en-US/systemcentermonitoring/thread/6b124e09-3262-420f-b79c-0096562ec945/. From this discussion and investigation on my systems it appears that SQL 2008 does not include the sqldmo.dll which is needed to provide these values.

To resolve this I downloaded the “Microsoft SQL Server 2005 Backward Compatibility Components” which was available at: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075. I installed  the resulting MSI (Server2005_BC.msi) and installed only the SQL-DMO components. Once those were available I manually registered the dll (regsvr32 sqldmo.dll from the C:\Program Files\Microsoft SQL Server\80\Tools\Binn folder). Once this was done I rebooted the SQL server (I am not sure if that was required) and in my lab I was able to validate that the performance counters now appear. Screenshots are below to show how the counters are now beginning to populate in my environment.

sqlmp01

SQLmp02

Summary: No database free space or transaction log free space performance counters available for your SQL 2008 servers? You may need to install the “Microsoft SQL Server 2005 Backward Compatibility Components” and register sqldmo.dll on your SQL 2008 servers.

UPDATE: It looks like Marnix already did a writeup on issues with these counters. His is focused on permissions issues and is available at: http://thoughtsonopsmgr.blogspot.com/2009/08/its-all-about-permissions.html 

UPDATE2: The OpsMgr R2 book also discusses some of this situation in Chapter 4 on page 139.

Leave a Reply