After updating to the new SQL management pack in my lab I start to see Critical alerts for some of my SQL instances, but not all. When looking at the Alert Context the Value is 0. If I open SQL Management Studio and connect to the Instance and run a query to get the Page Life Expectancy, I get a high value like 494012, the query I use is:

SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

Could this be an error in this Management Pack, perhaps I should disable this monitor? I also get the same for the monitor Buffer Cache Hit Ratio, also with a Value of 0 in Alert Context.

Get the same here on some but not all SQL instances. Not sure why as yet!

This monitor rely on Windows performance counter Buffer Manager:Page life expectancy

Can you open perfmon and check counters value? 

Hi Aleksandr,

That is what I do with the SQL query above.

Using perfom gives the exact same result. When doing this on one of my servers I see the value is constantly increasing with 1 for each second, currently displaying a value of 335261.

I think this has to do with improper permissions. I have assigned a dedicated domain user with sysadmin on SQL servers to all the SQL runas profiles. This I did before importing the new SQL Management Pack, and everything was fine. However, after installing this new Management Pack I started to see these alerts, and that the Alert Context had a value of 0. I also noticed that my Default Action account needed access to the SQL database. I did not verify it when I looked at it but I believe that the Default Action account did not have any rights to the instances where the alerts came from. So, perhaps some of the probing is not properly configured to use the right RunAs accounts.

Anyways, I will look in to this a bit further when I have some more time... Posting back the results when I have.

• Edited by Reidar Johansen 10 hours 49 minutes ago

Hi Aleksandr,

That is what I do with the SQL query above.

Using perfom gives the exact same result. When doing this on one of my servers I see the value is constantly increasing with 1 for each second, currently displaying a value of 335261.

I think this has to do with improper permissions. I have assigned a dedicated domain user with sysadmin on SQL servers to all the SQL runas profiles. This I did before importing the new SQL Management Pack, and everything was fine. However, after installing this new Management Pack I started to see these alerts, and that the Alert Context had a value of 0. I also noticed that my Default Action account needed access to the SQL database. I did not verify it when I looked at it but I believe that the Default Action account did not have any rights to the instances where the alerts came from. So, perhaps some of the probing is not properly configured to use the right RunAs accounts.

Anyways, I will look in to this a bit further when I have some more time... Posting back the results when I have.

• Edited by Reidar Johansen Thursday, September 19, 2013 11:42 PM

Hi Aleksandr,

That is what I do with the SQL query above.

Using perfom gives the exact same result. When doing this on one of my servers I see the value is constantly increasing with 1 for each second, currently displaying a value of 335261.

I think this has to do with improper permissions. I have assigned a dedicated domain user with sysadmin on SQL servers to all the SQL runas profiles. This I did before importing the new SQL Management Pack, and everything was fine. However, after installing this new Management Pack I started to see these alerts, and that the Alert Context had a value of 0. I also noticed that my Default Action account needed access to the SQL database. I did not verify it when I looked at it but I believe that the Default Action account did not have any rights to the instances where the alerts came from. So, perhaps some of the probing is not properly configured to use the right RunAs accounts.

Anyways, I will look in to this a bit further when I have some more time... Posting back the results when I have.

• Edited by Reidar Johansen Thursday, September 19, 2013 11:42 PM

Hi Aleksandr,

That is what I do with the SQL query above.

Using perfom gives the exact same result. When doing this on one of my servers I see the value is constantly increasing with 1 for each second, currently displaying a value of 335261.

I think this has to do with improper permissions. I have assigned a dedicated domain user with sysadmin on SQL servers to all the SQL runas profiles. This I did before importing the new SQL Management Pack, and everything was fine. However, after installing this new Management Pack I started to see these alerts, and that the Alert Context had a value of 0. I also noticed that my Default Action account needed access to the SQL database. I did not verify it when I looked at it but I believe that the Default Action account did not have any rights to the instances where the alerts came from. So, perhaps some of the probing is not properly configured to use the right RunAs accounts.

Anyways, I will look in to this a bit further when I have some more time... Posting back the results when I have.

• Edited by Reidar Johansen Thursday, September 19, 2013 11:42 PM

Any update on this Reider, Im having exactly the same issues.

Cheers

Zak

I had the same issue.  My problem was that the performance counters were not installed correctly.  There was a message in the application (I think) event log telling me which counters were missing.  I had to use the unlodctr/lodctr commands to reload them and then restart SQL.  After that, I started seeing valid values SCOM.

Hi Reidar, have a look if the account that you are using in the profile 'SQL Server Monitoring Account' has the ability to read from performance counters. Those workflows are using System.Performance.DataProvider DataSource.

cheers,

Marco

 

• Edited by MarcoDias 13 hours 52 minutes ago

Hi Reidar, have a look if the account that you are using in the profile 'SQL Server Monitoring Account' has the ability to read from performance counters. Those workflows are using System.Performance.DataProvider DataSource.

cheers,

Marco

 

• Edited by MarcoDias Thursday, March 13, 2014 8:43 PM