none
MSSQL: Monitoring failed with Event ID 4001 Out of Memory RRS feed

  • Question

  • We get the following error in SCOM every few days. I have upgraded to MP 6.7.15, but it also happened before:

    Management Group: SU-SCOM. Script: ModuleAction Module: ActiveRequestsDataSource.ps1 Version: 6.7.15.0 : Error occurred during Request count data source executing.
    Computer:<Server>
    Reason: Out of memory  
    Position:133
    Offset:22  
    Instance:<Instance>
    Detailed error output: Out of memory

    The error has come from the following modules:

    ActiveRequestsDataSource.ps1
    ActiveSessionsDataSource.ps1
    GetHKPoolMemoryConsumption.ps1
    StolenServerMemory2014DataSource.ps1
    DBDiskLatencyDataSource.ps1

    The server has plenty of memory, and the instances are part of an Availability Group

    Any idea what is causing this?

    Tuesday, January 3, 2017 5:42 PM

Answers

  • Hi everyone,

    We found a workaround to get rid of the "out of memory" errors. Try to take the following steps and then share your results here or at sqlmpsfeedback@microsoft.com. Pay attention that we suggest you make two changes at once as we don't know if it's enough just one of them to solve the problem. The only thing is known at the moment—if you apply both changes, the errors will disappear.

    So, two changes are:

    • to isolate the SQL Server WMI provider;
    • to increase the UnloadTimeout.

    To isolate the provider in its own host please follow these steps from an elevated PowerShell (the namespace ComputerManagement11 is for SQL 2012, 10 is for 2008 & 2008 R2, 12 is for 2014, 13 is for 2016):

    $a = [WMI]'Root\Microsoft\SqlServer\ComputerManagement11:__Win32Provider.name="MSSQL_ManagementProvider"'

    $a.HostingModel = "NetworkServiceHost:SQL"

    $a.put()

    To revert the change:

    $a = [WMI]'Root\Microsoft\SqlServer\ComputerManagement11:__Win32Provider.name="MSSQL_ManagementProvider"'

    $a.HostingModel = "NetworkServiceHost"

    $a.put()

    To increase the unload timeout to 30 minutes please follow these steps:

    • Open WBEMTEST
    • Click on Connect
    • In Namespace type Root\Microsoft\SqlServer\ComputerManagement11 then the Connect button
    • Click on the Query button
    • Type select * from __win32provider where name = 'MSSQL_ManagementProvider' then click Apply
    • Double click on the resulting row
    • Double click on the value UnloadTimeout
    • Select Not NULL then type 00000000003000.000000:000 then click on Save Property
    • Click on Save Object
    • Click Close


    Also, want to share with you that we've recently released a new preview version of SQL 2008-2016 MPs where introduced a number of improvements. We'll be glad to hear your feedback on it. You can have a read of the release here: https://blogs.msdn.microsoft.com/sqlreleaseservices/released-public-preview-for-sql-server-management-packs-update-7-0-3-0/.
    • Proposed as answer by GouravIN Thursday, March 15, 2018 4:02 AM
    • Marked as answer by SWCO_DBA Tuesday, March 27, 2018 1:21 PM
    • Edited by Alex Protsenko Wednesday, May 30, 2018 8:24 PM Typo
    Wednesday, March 14, 2018 5:46 PM

All replies

  • Hi Sir,

    >>The server has plenty of memory

    What is the SQL server OS version ?

    That error implies that issue arises at executing script .

    I'd like to check if there is any update for "memory leak" issue of that OS .

    Best Regards,

    Elton


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, January 5, 2017 3:51 AM
    Moderator
  • Hi Elton,

    We're using Windows 2012 R2.

    I made a change to the MaxMemoryPerShellMB PowerShell option, increasing from 1GB to 2GB to see if we had a out of memory condition on PowerShell, but I got the error again today:

    Event ID: 4001. Management Group: SCOM. Script: ModuleAction Module: StolenServerMemory2012DataSource.ps1 Version: 6.7.15.0 :
    Error occurred during SQL 2012 Stolen Server Memory data source executing.
    Computer:<Server>
    Reason: Out of memory
    Position:134
    Offset:22
    Instance:<Instance>
    Detailed error output: Out of memory

    Thursday, January 5, 2017 2:38 PM
  • Is this error occurring only on 1 Agent or on Multiple SQL Servers ?

    Gautam.75801

    Friday, January 6, 2017 6:18 AM
  • This happens across multiple SQL Servers.
    Monday, January 9, 2017 2:23 PM
  • Hello,

    I've found 2 KBs which could help you to resolve these warnings:
    https://support.microsoft.com/en-us/kb/3103616

    https://support.microsoft.com/en-us/kb/3146604

    Please make sure that you have the above applied to Windows Server on the agents, especially the first one.


    Tuesday, January 17, 2017 9:58 AM
  • Alex,

    These KB's are already installed on the server that gives the error message. We're pretty much up to date on all patches from Microsoft Update.

    Any other ideas?

    Jens

    Tuesday, January 17, 2017 4:56 PM
  • Jens, thank you for your answer.

    How often do those errors appear? 

    Also, could you check the following, please?

    1. Make sure that SQL Server leaves enough free memory for normal operation of Windows Server https://stefanroth.net/2015/08/04/scom-2012-event-id-4001-getsql2012spnstate-vbs-missing-database-instances/

    2. Try to increase the MaxMemoryPerShellMB to 3GB. Just to understand if it can resolve the issue.
    Wednesday, January 18, 2017 1:06 PM
  • Alex,

    The errors appear every few days, across many different database servers.

    1. All our databases on the server have max memory set, and there is plenty of memory for the OS

    2. I made the change on one of our servers and I'll keep an eye on that server to see if the problem reappears.

    Jens

    Wednesday, January 18, 2017 9:25 PM
  • Alex,

    I got the error again even after changing the MaxMemoryPerShellMBto 3GB:

    Event ID: 4201. Management Group: SU-SCOM. Script: ModuleAction Module: GetHKPoolMemoryConsumption.ps1 Version: 6.7.15.0 : Error occurred during Request count data source executing.
    Computer:<server>
    Reason: Out of memory
    Position:246
    Offset:22
    Instance:<instance>
    Detailed error output: Out of memory

    Thursday, January 19, 2017 2:41 PM
  • Jens,

    Thank you for the update. We're going to continue the investigation. Please expect an update from us by January 27th or earlier.

    Friday, January 20, 2017 1:09 PM
  • Hi Jens,

    Sorry for the delay in replying. Unfortunately, we don't know exactly what was the cause. May I ask you to check the next two things in your environment? 

    1. Set MaxMemoryPerShellMB to 0 in order to remove the limit at all.

    2. Increase the memory and handle quotas in the WMI provider service as described here http://babaus.azurewebsites.net/2015/08/14/wmi-fine-tuning-for-config-mgr/ and here https://blogs.technet.microsoft.com/samdrey/2013/07/16/windows-2008-r2-wmi-quota-issue-caused-by-either-a-wmi-leak-or-an-application-overallocating-wmi-memory/

    Thank you.

    Monday, January 30, 2017 11:27 AM
  • Alex,

    I have made the changes on one server, I'll monitor if the out of memory error returns.

    Monday, January 30, 2017 9:28 PM
  • Alex,

    The error came back within a few hours of making the change:

    Event ID: 4001. Management Group: SU-SCOM. Script: ModuleAction Module: StolenServerMemory2012DataSource.ps1 Version: 6.7.15.0 :
    Error occurred during SQL 2012 Stolen Server Memory data source executing.
    Computer:<server>
    Reason: Out of memory
    Position:134
    Offset:22
    Instance:<database>
    Detailed error output: Out of memory

    Tuesday, January 31, 2017 2:31 PM
  • Jens, thank you for notifying. We'll continue investigating the issue.

    Please expect to receive an update from us by February 8th.

    Wednesday, February 1, 2017 12:17 PM
  • Hi Sir,

    I am sorry that this issue still hasn't been resolved.

    If there is no progress, I would suggest you contact Microsoft Customer Services and Support to get an efficient solution:

    http://support.microsoft.com/contactus/?ln=en-au

    Have a nice day!

    Best Regards,

    Elton


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, February 9, 2017 4:21 AM
    Moderator
  • Hi There,

    Just a suggestion:

    Reason: Out of memory  
    nbsp;
    Offset:22  
    Instance:<Instance>
    Detailed error output: Out of memory

    I checked internally with the SQL MP development team and they mentioned "Out of Memory" error represents WMI is over loaded.

    There was another case where the user was receiving the same error and he flushed the health service of the Agent and it kind of fixed it.

    Try flushing the Healthservice state of the Agent and see if that stops the error or not.


    Gautam.75801

    Thursday, February 9, 2017 12:40 PM
  • Hi Jens,

    We didn't manage to find the cause other than a performance issue. Again, I'm going to ask you to check few things. 

    1. Take a look at the Operations Manager log on the affected machines. How often "Out of memory" errors appear? How many such events occur during a day? Are there warnings/errors other than ones thrown by SQL MPs?

    2. Take a look at the WMI Activity log (Microsoft>Windows>WMI Activity). Are there warning/error events which could help to find the failure cause?

    Thank you.

    Thursday, February 9, 2017 4:11 PM
  • Hi Alex,

    Sorry about the late reply, but I'm still facing the issue.
    I did flush the health service of the agent, but I don't really think it helped.

    The out of memory error has happened 18 times in February for this specific machine.

    The latest incident was on 2/28/17 at 5:36:00pm, and there is a corresponding error in the WMI log:

    Operations Manager Log:
    2/28/17 5:36:00PM
    Management Group: SU-SCOM. Script: ModuleAction Module: GetHKPoolMemoryConsumption.ps1 Version: 6.7.15.0 : Error occurred during Request count data source executing.
    Computer:<server>
    Reason: Out of memory  
    Position:246
    Offset:22
    Instance:<instance>
    Detailed error output: Out of memory

    WMI Log:
    2/28/17 5:36:00PM
    Id = {00000000-0000-0000-0000-000000000000}; ClientMachine = <server>; User = <domain>\SQLMonitor; ClientProcessId = 3104;
    Component = Unknown; Operation = Start IWbemServices::ExecQuery - ROOT\Microsoft\SqlServer\ComputerManagement12 :
    select PropertyName, PropertyNumValue, PropertyStrValue from SqlServiceAdvancedProperty where SqlServiceType = 1
    AND ServiceName = 'MSSQL$SQL2014PR' AND (PropertyName = 'Clustered' OR PropertyName = 'VSNAME'); ResultCode = 0x80041006; PossibleCause = Unknown

    Wednesday, March 1, 2017 2:38 PM
  • We are facing the same issue,across several SQL 2012 servers. They started when we updated the MP to 6.7.15.
    Thursday, March 16, 2017 10:09 AM
  • In addition to my earlier remark: the problem persists in the newly published version 6.7.20.
    Thursday, March 16, 2017 12:39 PM
  • That's bad....

    Gautam.75801

    Thursday, March 16, 2017 12:44 PM
  • Jens, we've found one more likely solution, hope it'll help you and the others in this thread to resolve the issue "Out Of memory".

    Increase these parameters in registry of key HKLM\SOFTWARE\Microsoft\WBEM\CIMOM

    “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WBEM\CIMOM\Low Threshold On Events (B)" to 35000000 (default is 10000000) 
    ”HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WBEM\CIMOM\High Threshold On Events (B)" to 70000000 (default is 20000000)
    “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WBEM\CIMOM\Low Threshold On Client Objects (B)" to 40000000 (default is 10000000) 
    ”HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WBEM\CIMOM\High Threshold On Client Objects (B)" to 60000000 (default is 20000000)

    You need to update 4 parameters, in case you can't see specified parameters, just create them. They are all of <g class="gr_ gr_493 gr-alert gr_gramm gr_run_anim Grammar only-ins replaceWithoutSep" data-gr-id="493" id="493">DWORD</g> type.


    Friday, March 17, 2017 10:47 AM
  • I made those changes. I cleared agent state, but I did not reboot the server.

    However, the error came back:

    Event ID: 4201. Management Group: SU-SCOM. Script: ModuleAction Module: ActiveRequestsDataSource.ps1 Version: 6.7.15.0 : Error occurred during Request count data source executing.
    Computer:<Server>
    Reason: Out of memory
    Position:133
    Offset:22
    Instance:<Instance>
    Detailed error output: Out of memory
    --------
    Property <server.domain>_CreationTime does not exist at path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Modules\{5661876B-61C7-167E-1289-FDD37869A38B}\S-1-5-21-1071647620-1809656571-2576383827-8746\Script\SQL2014MP\SqlHostNames.

    Wednesday, March 22, 2017 2:24 PM
  • Those are for WMI, so you need at least to restart WMI. But I don't exactly know if it's enough for getting them as active parameters.

    Also, I can't see CPUUsage module in the affected module list in the topic starter message. Is it right? Aren't there the "Out of memory" errors for this module?

    Wednesday, March 22, 2017 4:58 PM
  • I restarted the WMI and Monitor Agent on the server, but I still got two errors since yesterday:

    Event ID: 4001. Management Group: SU-SCOM. Script: ModuleAction Module: StolenServerMemory2012DataSource.ps1 Version: 6.7.15.0 :
    Error occurred during SQL 2012 Stolen Server Memory data source executing.
    Computer:<server>
    Reason: Out of memory
    Position:134
    Offset:22
    Instance:<Instance>
    Detailed error output: Out of memory

    And:

    Event ID: 4201. Management Group: SU-SCOM. Script: ModuleAction Module: DBDiskLatencyDataSource.ps1 Version: 6.7.15.0 :
    Error occurred during DB Disk Latency data source executing.
    Computer:<server>
    Reason: Out of memory
    Position:427
    Offset:35
    Instance:<instance>
    Detailed error output: Out of memory

    Thursday, March 23, 2017 3:11 PM
  • Hi  '

    Any update on the issue ? i think i having some similar.

    Event ID: 4001. Management Group: XXX_Collaboration_Monitoring. Script: GetSQL2012DBFreeSpace.vbs. Version: 6.7.15.0 : The next errors occured:
    Cannot get a list of enabled Sql Server protocols
    Error Number: -2147217402
    Description: Out of memory
    Instance: OPSMGR

    Thursday, May 25, 2017 2:29 AM
  • Hi, we've just caught such warnings on our test lab. We'll try to solve them and then get back to you all with the results.
    Thursday, May 25, 2017 9:50 AM
  • OK, that is good news as we're continuing to see this error on our systems.
    Thursday, May 25, 2017 1:29 PM
  • We applied a resolution from here https://support.microsoft.com/en-sg/help/126962/-out-of-memory-error-message-appears-when-you-have-a-large-number-of-programs-running on Friday, 2nd of May. As described in there, we set SharedSection to "1024,20480,1024" (instead of "1024,20480,768") and then rebooted the server. Today, two days later, there are still no "Out of memory" errors in OpsMan log. 

    We'll continue keeping an eye on that server and let you know if the error comes back. Could you guys give it a try too and tell us if it solves the problem?

    • Proposed as answer by GouravIN Thursday, December 7, 2017 3:40 AM
    • Unproposed as answer by GouravIN Thursday, December 7, 2017 3:40 AM
    • Proposed as answer by GouravIN Thursday, December 7, 2017 3:40 AM
    Monday, June 5, 2017 11:07 AM
  • I made the above change two days ago and rebooted the server.

    The out of memory error came back after 36 hrs:

    Event ID: 4201. Management Group: SU-SCOM. Script: ModuleAction Module: StolenServerMemory2014DataSource.ps1 Version: 6.7.15.0 :
    Error occurred during SQL 2014 Stolen Server Memory data source executing.
    Computer:<db server>
    Reason: Out of memory
    Position:131
    Offset:22
    Instance:SQL2014PR
    Detailed error output: Out of memory

    Wednesday, June 7, 2017 2:17 PM
  • Yesterday, we caught the error too. Now we check this solution: https://blogs.technet.microsoft.com/askperf/2014/08/11/wmi-high-memory-usage-by-wmi-service-or-wmiprvse-exe/.

    Could you increase the memory quota and then, if it doesn't help, try to find the leaking WMI provider using their instruction?


    Thursday, June 8, 2017 2:04 PM
  • Hi, any updates here? We are having the same problem, and are wondering if you found any permanent solution? Thanks.
    Monday, July 3, 2017 8:57 AM
  • We have just updated the SCOM SQL Management Pack to 6.7.31.0 and the error is still present, we also had the error appearing in 6.7.15.0
    Monday, July 3, 2017 11:12 PM
  • Regarding to the below error...
    Do you use Low-Privileges accounts or 'Sysadmin' rights on SQL DB?

    Error:
    Event ID: 4201. Management Group: SU-SCOM. Script: ModuleAction Module: StolenServerMemory2014DataSource.ps1 Version: 6.7.15.0 : 
    Error occurred during SQL 2014 Stolen Server Memory data source executing.
    Computer:<db server>
    Reason: Out of memory 
    nbsp;
    Offset:22 
    Instance:SQL2014PR
    Detailed error output: Out of memory

    Tuesday, July 11, 2017 12:35 PM
  • I'm not the original poster but I'm having the issue on 6.7.20.0 on multiple servers and they are all done with the low privilege option.

    Thursday, July 13, 2017 1:08 AM
  • I'm not sure, but when I raised privileges from 'low' to 'Sysadmin' this error disappeared.
    I can't test it anymore, because in our environment we have to use 'low-privileges', but if you have a possibility to raise the right, it might be worth to trying?

    Btw, I've updated SQL MP to version 6.7.31.0 and it didn't resolve this problem.

    Thursday, July 13, 2017 6:46 AM
  • I completed the first part, setting the MemoryPerHost to 1073741824. However that didn't seem to help. I did not go through the second part of the instructions as I couldn't really identify a WMI process that was using excess memory. I even setup a scheduled task to restart WMI on a daily basis, but that still didn't help.

    Now I just changed the SCOM_SQLMPLowPriv account on one server to have the sysadm role. Let's see if that helps, however its not a configuration I'm too comfortable with.

    Tuesday, July 18, 2017 3:49 PM
  • Any result?
    Monday, July 24, 2017 12:25 PM
  • The out of memory error returned 8 days later even with sysadm role enabled:

    Management Group: SCOM. Script: ModuleAction Module: DBDiskLatencyDataSource.ps1 Version: 6.7.15.0 :
    Error occurred during DB Disk Latency data source executing.
    Computer:<server>
    Reason: Out of memory  
    Position:427
    Offset:35
    Instance:SQL2014
    Detailed error output: Out of memory

    Tuesday, July 25, 2017 2:27 PM
  • Thank you for this answer. I didn't have to test in my environment, because my customer couldn't accept this exception (switch from low privileges to sysadmin).
    It looks like that we have to wait for new version of MP from Microsoft...
    Wednesday, July 26, 2017 8:20 AM
  • Same issue here

    Happening to my own MS SQL OPSMNGR server.  i was thinking updated MP version but it seems not like a solution here. And is already Sysadmin account. 

    Any new trial from your end recently? 

    Alert: MSSQL: Monitoring warning

    Last modified by: System

    Last modified time: 9/2/2017 5:33:48 AM

    Alert description: Event ID: 4001. Management Group: DHL_Collaboration_Monitoring. Script: GetSQL2012DBFreeSpace.vbs. Version: 6.7.15.0 : The next errors occured:

    Cannot get a list of enabled Sql Server protocols  Error Number: -2147217402

     Description: Out of memory

     Instance: OPSMGR


    Hi~ o(* ̄▽ ̄*)ブ4

    Saturday, September 2, 2017 8:48 AM
  • I am also getting this randomly across multiple instances. Example below.

    Event ID: 4001. Management Group: GSJM-SCOM. Script: GetSQL2008AgentJobStatus.vbs. Version: 6.7.31.0 : The next errors occurred:
    Cannot get a list of enabled Sql Server protocols
    Error Number: -2147217402
    Description: Out of memory
    Instance: MSSQLSERVER

    Is there a better way to address this than trying to put all those hotfixes into the systems? 

    Monday, September 4, 2017 12:45 AM
  • Jens, could you tell me how many instances of SQL Server you have installed on the affected servers? Do you have instances of Reporting Services and/or Analysis Services on the same servers? If so, do you use appropriate MPs to monitor them? Do you use Replication MP to monitor deployments of Replication?

    The only thing that we found out is that the error appears when WMI's experiencing high load. In our case, we got the error on a server where there were 3 instances of SQL Server, 2 instances of RS, 2 instances of AS. All of them were monitored with appropriate MPs. After we reduced the load by disabling AS and RS monitoring, the error disappeared.

    Please describe your environment.


    Thursday, September 21, 2017 10:12 AM
  • I am having this issue as well across many SQL servers.  I'm on MP 6.7.31.0, I do not use low priv accounts, we have separate users set for every SQL monitoring profile, there is over 200GB free RAM on a few of the systems at all times.  I have the DB engine (2 instances), SSAS, and SSRS.  The error happens randomly across all SQL monitors (there's no one monitor triggering it that I can see).  I'm glad others are experiencing this.  At first I thought I configured something wrong.
    Monday, September 25, 2017 1:55 PM
  • Hi, any updates here? We are having the same problem. Thanks.
    Friday, October 20, 2017 7:20 AM
  • Anything new?  We're seeing the same issue with 6.7.31.0 across multiple servers as well, both SQL 2012 and 2016.  5 different VMs, with varying amounts of memory, always the same errors:

     

    SQL 2012:

    Event ID: 4001. Management Group: xxxxxx. Script: ModuleAction Module: StolenServerMemory2012DataSource.ps1 Version: 6.7.31.0 : 
    Error occurred during SQL 2012 Stolen Server Memory data source executing.
    Computer:xxxxxx
    Reason: Out of memory 
    Position:134 
    Offset:22 
    Instance:xxxxxx
    Detailed error output: Out of memory 

    SQL 2016:

    Event ID: 4211. Management Group: xxxxxx. Script: ModuleAction Module: GetHKPoolMemoryConsumption.ps1 Version: 6.7.31.0 : Error occurred during Request count data source executing.
    Computer:xxxxxxx 
    Reason: Out of memory 
    Position:246 
    Offset:22 
    Instance:xxxxxxxx
    Detailed error output: Out of memory 
     

    Monday, October 30, 2017 12:57 PM
  • Did you restrict the memory of SQL? The OS should have at least 4GB, but it might need more.

    The powershell query runs "outside" of SQL and uses "OS" memory. If SQL uses all the memory it's allowed to take the OS might simply not have enough.

    "plenty of memory" is relative, if it uses all of it, it might not be as "plenty" as you think.


    Rob Korving
    http://jama00.wordpress.com/

    Monday, October 30, 2017 1:06 PM
  • Did you restrict the memory of SQL? The OS should have at least 4GB, but it might need more.

    The powershell query runs "outside" of SQL and uses "OS" memory. If SQL uses all the memory it's allowed to take the OS might simply not have enough.

    "plenty of memory" is relative, if it uses all of it, it might not be as "plenty" as you think.


    Rob Korving
    http://jama00.wordpress.com/


    I cannot speak to the OP, but in my case SQL is capped on all of our SQL Servers, allowing the OS at least 8GB.  For example, one server where I'm getting the error has 64gb with only one SQL instance (min memory at 0 and max memory set to 56,000mb).  It's a sandbox server so I don't care about the minimum.  That certainly should be enough for the script to do whatever it needs to do.

    Ralph Bley

    Wednesday, November 1, 2017 3:11 PM
  • In our case, the server has 768GB of RAM and over 200GB were free for use by the OS.  The SQL instance is limited to 400GB, leaving room for SSAS and SSRS as well.

    None of the suggestions helped.  None of the other monitoring MPs have issues, only the SQL MP.

    Wednesday, November 8, 2017 3:50 PM
  • Wanted to add to this post, and I'd love to hear from anyone that may have resolved this. We have this alert come up in our environment on nearly all of our SQL servers (we have many) almost on a daily basis. The SQL instances range from 2008 to 2016, and all servers have a minimum of 4gb of RAM for OS memory. Some of them have as high as 200gb of free memory. We are running the 6.7.31.0 MP for Microsoft SQL Server Core Library. I've tried the above suggestions as well with no luck on resolving this alert.

    Has anyone successfully resolved this?

    Friday, December 1, 2017 3:27 PM
  • I'm still having this issue. I'm currently working with Microsoft support, but they have not been able to solve the issue.
    Wednesday, December 6, 2017 9:07 PM
  • Hi As i have seen the issue from beginning, Could you please let me the accounts have appropriate privilege to run the workflow.

    if not do this please:-

    1. Create a new SQL Monitoring account and associated them with the 3 SQL Run As Profiles (SQL Server Default Action Account, SQL Sever Discovery Account, SQL Server Monitoring Account). (I used the SCOM default action account)
    2. Set the account as More Secure account and add the agent to the Account’s Distribution list
    3. Add the servers to the Distribution list of the SQL monitoring account
    4. Add the SQL monitoring account to the Computer’s “Performance Monitor Users”, “Event Log Readers” and “Users” group
    5. Ensure the SQL monitoring account is the computer’s local administrator and is a sysadmin to SQL server

    And here is the way to do Point 4 above

    Here is the way to add the SQL monitoring account to the computer’s “Performance Monitor Users”, “Event Log Readers” and “Users” group.

    1. Run cmd as administrator
    2. Run lusrmgr.msc, click groups
    3. Locate the target group, such as Performance Monitor Users, double click the group
    4. Click Add and then Add the user to the group, click OK

    And if already configured then please revert. Will looking from your side!

      

    Thanks, Gourav

    Thursday, December 7, 2017 3:44 AM
  • Any update here!

    Cheers, Gourav (Please do take a moment to "Vote as Helpful" and/or "Mark as Answer" wherever applicable. Thanks!) or find my Facebook and LinkedIn link in profile.

    Monday, December 11, 2017 3:26 AM
  • The latest MP is at version 7.0.0.0 released on 11/14/2017. Anyone still having the error on this version? 

    Thinking to update as I am seeing this across multiple servers.


    Wednesday, December 20, 2017 12:27 AM
  • Jumping back after almost a year. Customer has the same issue with out of memory in Always on servers with 7.0.0.0 any pointer for this will be appreciated.

    Gautam.75801

    Monday, January 1, 2018 1:56 AM
  • I have multiple customers facing Out of memory alert issues with latest SQL MP (similar to https://social.technet.microsoft.com/Forums/lync/en-US/03c5b2e5-efdb-4e9b-bf98-8f86f93ec2c2/mssql-monitoring-failed-with-event-id-4001-out-of-memory?forum=operationsmanagermgmtpacks)

    Computer:<Server> 
    Reason: Out of memory  
    Position:133 
    Offset:22  
    Instance:<Instance>
    Detailed error output: Out of memory

    We have tried to increase MemoryPerHost and other property to higher level but still alerts generating.

    Any update on how to get this issue fixed?
    Tuesday, January 9, 2018 9:56 PM
  • Also seeing the same on 7.0.0.0

    Thursday, January 11, 2018 9:12 AM
  • Hi Patrik,

    There are issues with this MP and team is working on it for further please go through the blog Blog MP 7 or shoot a mail to with your problem with sqlmpsfeedback@microsoft.com


    Cheers, Gourav (Please do take a moment to "Vote as Helpful" and/or "Mark as Answer" wherever applicable. Thanks!) or find my Facebook and LinkedIn link in profile.

    Thursday, January 11, 2018 9:58 AM
  • Hi everyone,

    We found a workaround to get rid of the "out of memory" errors. Try to take the following steps and then share your results here or at sqlmpsfeedback@microsoft.com. Pay attention that we suggest you make two changes at once as we don't know if it's enough just one of them to solve the problem. The only thing is known at the moment—if you apply both changes, the errors will disappear.

    So, two changes are:

    • to isolate the SQL Server WMI provider;
    • to increase the UnloadTimeout.

    To isolate the provider in its own host please follow these steps from an elevated PowerShell (the namespace ComputerManagement11 is for SQL 2012, 10 is for 2008 & 2008 R2, 12 is for 2014, 13 is for 2016):

    $a = [WMI]'Root\Microsoft\SqlServer\ComputerManagement11:__Win32Provider.name="MSSQL_ManagementProvider"'

    $a.HostingModel = "NetworkServiceHost:SQL"

    $a.put()

    To revert the change:

    $a = [WMI]'Root\Microsoft\SqlServer\ComputerManagement11:__Win32Provider.name="MSSQL_ManagementProvider"'

    $a.HostingModel = "NetworkServiceHost"

    $a.put()

    To increase the unload timeout to 30 minutes please follow these steps:

    • Open WBEMTEST
    • Click on Connect
    • In Namespace type Root\Microsoft\SqlServer\ComputerManagement11 then the Connect button
    • Click on the Query button
    • Type select * from __win32provider where name = 'MSSQL_ManagementProvider' then click Apply
    • Double click on the resulting row
    • Double click on the value UnloadTimeout
    • Select Not NULL then type 00000000003000.000000:000 then click on Save Property
    • Click on Save Object
    • Click Close


    Also, want to share with you that we've recently released a new preview version of SQL 2008-2016 MPs where introduced a number of improvements. We'll be glad to hear your feedback on it. You can have a read of the release here: https://blogs.msdn.microsoft.com/sqlreleaseservices/released-public-preview-for-sql-server-management-packs-update-7-0-3-0/.
    • Proposed as answer by GouravIN Thursday, March 15, 2018 4:02 AM
    • Marked as answer by SWCO_DBA Tuesday, March 27, 2018 1:21 PM
    • Edited by Alex Protsenko Wednesday, May 30, 2018 8:24 PM Typo
    Wednesday, March 14, 2018 5:46 PM
  • Great Job! :)

    Cheers, Gourav Please remember to mark the replies as answers if it helped.

    Thursday, March 15, 2018 4:02 AM
  • That workaround seem to work regarding the "Out of Memory" errors. Not seen this alert after appliying on several machines for a couple of days, now.
    Because that has to be done on every affected machine can we expect the resolution in the new release of the SQL MPs?


    Monday, March 19, 2018 12:39 PM
  • In SQL MPs, we're going to reduce the number of WMI requests which MP workflows establish while they're running. It should help to get rid of the "Out of memory" errors (or make them rarer). However, we can't provide the above solution in SQL MPs.

    Also, have a question for you. Do your agents—where the "out of memory" appears—connect not to only one management group? We've recently learned that multihomed agents are prone to the error.

    Monday, March 26, 2018 10:45 AM
  • I have seen agents with a single management group having this issue with several customers.

    Gautam.75801

    Monday, March 26, 2018 5:46 PM
  • I originally posted this problem 14 months ago. We tried a lot of different things, and this was the first workaround that actually have solved our issue. 
    After making this change we have not seen the out of memory error for about two weeks.

    Thanks!

    Tuesday, March 27, 2018 1:24 PM
  • I originally posted this problem 14 months ago. We tried a lot of different things, and this was the first workaround that actually have solved our issue. 
    After making this change we have not seen the out of memory error for about two weeks.

    Thanks!

    Finally you marked it as an Answer :-), Good to hear the issue is not more.

    Gautam.75801


    Tuesday, March 27, 2018 5:05 PM
  • OK, I see. Thank for answering.

    So, it seems that the workaround above is the only solid solution to solve the "out of memory" problem.

    Wednesday, March 28, 2018 1:06 PM
  • Thank you for confirming it. 
    Wednesday, March 28, 2018 1:07 PM
  • Bookmarking this. :)

    Should we expect any update fixing this in the upcoming MP updates?

    Cheers


    Sam (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" wherever applicable. Thanks!)

    Wednesday, March 28, 2018 2:10 PM
  • Hi everyone,

    We found a workaround to get rid of the "out of memory" errors. Try to take the following steps and then share your results here or at sqlmpsfeedback@microsoft.com. Pay attention that we suggest you make two changes at once as we don't know if it's enough just one of them to solve the problem. The only thing is known at the moment—if you apply both changes, the errors will disappear.

    So, two changes are:

    • to isolate the SQL Server WMI provider;
    • to increase the UploadTimeout.

    To isolate the provider in its own host please follow these steps from an elevated PowerShell (the namespace ComputerManagement11 is for SQL 2012, 10 is for 2008 & 2008 R2, 12 is for 2014, 13 is for 2016):

    $a = [WMI]'Root\Microsoft\SqlServer\ComputerManagement11:__Win32Provider.name="MSSQL_ManagementProvider"'

    $a.HostingModel = "NetworkServiceHost:SQL"

    $a.put()

    To revert the change:

    $a = [WMI]'Root\Microsoft\SqlServer\ComputerManagement11:__Win32Provider.name="MSSQL_ManagementProvider"'

    $a.HostingModel = "NetworkServiceHost"

    $a.put()

    To increase the unload timeout to 30 minutes please follow these steps:

    • Open WBEMTEST
    • Click on Connect
    • In Namespace type Root\Microsoft\SqlServer\ComputerManagement11 then the Connect button
    • Click on the Query button
    • Type select * from __win32provider where name = 'MSSQL_ManagementProvider' then click Apply
    • Double click on the resulting row
    • Double click on the value UnloadTimeout
    • Select Not NULL then type 00000000003000.000000:000 then click on Save Property
    • Click on Save Object
    • Click Close


    Also, want to share with you that we've recently released a new preview version of SQL 2008-2016 MPs where introduced a number of improvements. We'll be glad to hear your feedback on it. You can have a read of the release here: https://blogs.msdn.microsoft.com/sqlreleaseservices/released-public-preview-for-sql-server-management-packs-update-7-0-3-0/.

    Hi Alex,

    great guide on a hot topic. Can you guys publish this as a technet blog? You will be able to reach far greater audience then. I was lucky to stumble on this while searching, but other might not find it. :)

    Or is this fixed in the 7.0.4.0 Version of the SQL Management Packs?

    Thanks,

    Regards


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    Tuesday, May 15, 2018 9:01 AM
    Moderator
  • Have the same issue. Will try to implement this workaround.

    Could you tell if this will be fixed in the next MPs update ?

    (starting a change manegement process for hundreds of SQL Servers to implement this workaround on every of them - as I understand it needs to be implement on every affected server, not the SCOM server only - may take like a year)

    Monday, June 11, 2018 7:37 AM
  • Hi all,

    we still see "out of memory" errors even with version 7.0.7.0 of the SQL MP.

    Event ID: 4001. Management Group:
    Script: GetSQL2012DBFileGroupFreeSpace.vbs. Version: 7.0.7.0 : The next errors occurred:
    Cannot get a list of enabled Sql Server protocols
    Error Number: -2147217402
    Description: Out of memory

    Regards,
    Konstantin

    Tuesday, August 28, 2018 2:21 PM
  • Same here.  SQL Management Pack v7.0.7 and still get the "out of memory" alerts.  However the workaround put forward by Stoyan Chalakov seems to be working.  Any server that has the issue we perform the work around on and the alert doesn't come back.

    http://www.dreamension.net

    Friday, September 7, 2018 5:01 AM