During a past SCOM engagement I was asked to implement SQL Server Management Pack 6.6.0 for monitoring SQL Server 2012. This MP is getting better with every release and works in most cases without any issue.
I had two SQL Servers which were placed in the same domain and were configured identically. The only difference I met was, that I could not see any discovered database instances from SQL Server A, where SQL Server B showed up in the SCOM console with its database instances. Here just the similar view from my lab, so you understand which view I am talking about…
After checking the OperationsManager event log on each Server A and Server B, I found several events which did not relate to the missing database instances in the first place. I cleaned up the SCOM agent cache and restarted the SCOM agent service on each server. Finally I found a difference, on SQL Server A an event was logged…
You can find different solutions and reasons for this event . In my case there is a small difference you have to watch out.
Please check to see if this is a valid WMI Query. Out of memory.
(Note here the “Out of memory”)
This means there is not even enough memory to create WMI objects or fire off WMI queries on that specific server from the SQL Server MP. Probably a lot admins would think “No problem give this VM more RAM”. This would be a temporarily relief probably and would help, but it fights only the symptoms not the cause. As you remember this is a SQL Server and there are certain settings you need to make in order to function properly. ONE REALLY REALLY necessary setting you would need to make is to set the max memory setting on your SQL Server. By default, SQL Server’s max memory is 2147483647 and SQL Server will just keep using more and more memory until there’s none left on the system. It is like killing yourself – yup. So, the first thing is to limit the max memory setting. On Brent Ozars blog (there are many other blog which discuss these basic things) you find how to set this configuration. I have also written a post about the most basic SQL Server settings you would need to make for running a successful SQL / SCOM server environment. Find this post here http://stefanroth.net/2012/02/28/system-center-sql-server-2008-r2-performance-tuning/ . Have fun…
