none
DAL QueryNotificationManager process causing high SQL waits.

    Question

  • Hello,

    I've recently noticed the following constant process with high wait time on my SQL server instance hosting my Operations Manager database -

    Database:  OperationsManager

    Task State:  SUSPENDED

    Command Type:  DELETE

    Application:  SC DAL--DAL QueryNotificationManager

    Wait Time (ms):  12090

    Wait Type:  BROKER_RECEIVE_WAITFOR

    Could someone please provide some insight into this? The high wait time is causing high average waits which result in unwanted alerts from the SQL Server MP. Is this expected behavior?

    Friday, November 8, 2013 9:36 PM

All replies

  • Hello,

    I've recently noticed the following constant process with high wait time on my SQL server instance hosting my Operations Manager database -

    Database:  OperationsManager

    Task State:  SUSPENDED

    Command Type:  DELETE

    Application:  SC DAL--DAL QueryNotificationManager

    Wait Time (ms):  12090

    Wait Type:  BROKER_RECEIVE_WAITFOR

    Could someone please provide some insight into this? The high wait time is causing high average waits which result in unwanted alerts from the SQL Server MP. Is this expected behavior?


    I observe the exact same issue. Where does this come from?
    Thursday, December 12, 2013 9:54 AM
  • What version of OM are you using with what UR? What version of SQL? Is the Sql Broker enabled?
    Thursday, December 12, 2013 3:51 PM
    Owner
  • I suggest running a SQL Profiler Trace to see exactly what queries are causing this condition.

    Jonathan Almquist | SCOMskills, LLC (http://scomskills.com)

    Friday, December 13, 2013 1:21 AM
    Moderator
  • It seems as though this query is causing the condition:

    WAITFOR (Receive convert(xml,message_body), conversation_handle from Queue_mid10_0_50_5_pid1448_adid2_r1230761596), TIMEOUT 300000

    As expected based on the query, the process is killed at 300000 milliseconds and restarts.

    Additionally, as referenced in the query by "pid1448" I traced the client process id (also listed in the trace data) to the Microsoft.Mom.Sdk.ServiceHost.Exe process on the SCOM server. 

    Friday, December 27, 2013 9:29 PM
  • Now on Ops Mgr 2012 R2 with SQL Server 2012 SP1 (11.0.3128) housing DW and OpsMgr DBs. SQL Broker is enabled.
    Friday, December 27, 2013 9:32 PM
  • Have you been able to resolve this issue?  I'm seeing the same thing on a SCOM 2012 SP1 environment which I upgraded to 2012 R2.  This is on SQL 2008 R2 SP1.

    Thanks

    Wednesday, January 8, 2014 12:49 PM
  • Hi,

    I have actually the same problem, do you have a solution for that ?

    Many thanks,

    Sebastien

    Tuesday, December 23, 2014 8:53 AM
  • I'm also getting this same problem. 2012 R2 system with 2012 SCOM. Fresh install. Any solution to this found?
    Friday, January 16, 2015 9:50 AM
  • Did anyone find resolution on this? We are experiencing the same in our SC environment. Killing the service does not provide long-term resolution as the service is essential to functionality.
    Thursday, August 6, 2015 10:02 PM
  • Hi All,

    I am also facing this issue,

    SCOM is full with events 2115,31552,29181 so trying to correlate

    Any inputs

    Thanks

    Sunil

    Thursday, August 27, 2015 11:19 AM
  • Hi,

    I have the same issue.

    Did you find a solution for this problem?

    A patch or something to resolve it?

    Thanks

    Regards,

    Stéphane


    Best regards, Mit freundlichen Grüssen, Avec mes meilleures salutations, Stéphane Haby

    Friday, February 12, 2016 12:14 PM
  • Hi All I know this is a few years old but I am experiencing the exact same issue. Just wondered if anyone got fix for this. Help appreciated.


    Dominic

    Wednesday, April 4, 2018 12:58 PM