Hi All,

We have seen this message twice since we migrated to SCOM 2007 R2 (From SP1):

XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents.

From what we understand some stored proc is not releasing memory properly. Every time this happens we have to restart the SQL instance that hosts the operations manager databases to release the memory.

I opened a CSS call but MS did not help. The Microsost SCOM support tech said it was a SQL problem and the SQL support tech (also from Microsoft) sent me the documentation for the SQL memory architecture !!! (not at all usefull).

I'd like to know if anyone has seen this problem with R2. Right now I'm not sure if the problem is from SCOM stored procs using and not releasing the memory or from the SQL server not having enough resources.

Thanks for any input on this one.

Where are you getting this error?  Popup?  Event log?  On what server?  Need a LOT more details.

What version of SQL are you running?  What SP and rollup?

What is the configuration of your SQL server (cluster, single instance, multiple instances?)

How much memory does you SQL server have?

How much RAM is allocated to this instance? 

Have you set a limit for the instance to limit the amount of memory SQL is allowed to use?


Only have an issue 2 times in several months... it sounds very random and environmental to me.  The most common cause is extreme memroy pressure on the SQL server.  This is not a common issue at all, and there arent any other reports of this with SCOM.

Thanks Kevin for the reply.

The error message comes from our Operations Manager Root Management Server event logs. It raised the following data warehouse alert monitors:

Data Warehouse Managed Object Type Synchronization Data Writer Recovery State
Data Warehouse Domain Snapshot Synchronization Data Writer Recovery State
Data Warehouse Event Data Collection Writer Recovery State
Data Warehouse Configuration Synchronization Data Reader Recovery State
...

Here is a sample alert description:

Data Warehouse configuration synchronization process failed to read data from the operational database. Failed to generate data to be forwarded to the Data Warehouse. The operation will be retried.
Exception 'SqlException': XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents.
Failed to load and parse 'InstalledManagementPack' xml. sp_xml_preparedocument returned '1'

The SQL server has 16GB of memory and is running on cluster with several instance (SQL Server 2005 x64 + SP2+ KB934458, KB934459 and KB941450  Version: 9.0.3200). The instance has the Server Memory option set to 5GB and the Other Memory option set to 1GB.

Thanks again.

This sounds like the SQL Instance doesn't have enough memory allocated to it.  In all of my years of working with SQL Server and OpsMgr, I've never seen this error message before.

What version and patch level of SQL Server are you running?  Take a look at this KB artcile to determine this:  http://support.microsoft.com/default.aspx/kb/321185

Do you have the "minimum server memory" option set for this SQL Instance? 

Also, have you looked at the following SQL Counters
MSSQL:MemoryManager\Target Server Memory
MSSQL:MemoryManager\Total Server Memory
MSSQL:BufferManager\Buffer Cache hit Ratio (this one is really important - should be above 97-98, ideally as close to 100% as possible.)

Looking at these counters might be able ot tell us if this SQL Instance is memory starved or not.  It sounds like it might be.

Hi Michael,

The version is 9.00.3200.00 SP2 Standard Edition (64bit). The "target server memory" and "total server memory" perf counter are fix at 5242880. The "Buffer Cache hit ratio" is usually steady at 98.8 and rarely drops below 97.

We have 300 agents and our Opsmgr database has around 125GB of data. The SQL instance has 5GB of memory allocated to it. Opsmgr has been running with this config from the beginning but has grown a lot since (almost 2 years). Do you think we need to grant more memory to the instance ?

Thanks again.

Due to inactivity marked as Answered. Feel free to re-open.

Hi 

I am facing same issue in the scom 2012 r2.

please find the error below 

On SCOM MS:

Alert name :Data Warehouse relationship synchronization process failed to write data 

Alert description :Data Warehouse relationship synchronization process failed to write data to the Data Warehouse database. Failed to store data in the Data Warehouse. The operation will be retried.
Exception 'SqlException': Sql execution failed. Error 6624, Level 16, State 1, Procedure sp_xml_preparedocument, Line 1, Message: XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents.

Event id in Operation log:  

Event id :33333 

Data Access Layer rejected retry on SqlError:
 Request: DataWarehouseSynchConfigChangeTypeGet -- (InstalledManagementPackXml=<ManagementPacks><ManagementPack>7b9f4239-7351-e72b-4ce4-d6c56ddb401d</ManagementPack><ManagementPack>848edb97-9da5-e2ae-24d4-56...), (RETURN_VALUE=-6)
 Class: 16
 Number: 6624
 Message: XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents.

ON SCOM DB server:

SQL version : SQL 2012 SP1 X64 bit Host all SCOm db (SCOM DB,SCOM DW,Reporting)

Ervent id : 28005 ,Application log,source: MSSQLSERVER

Please suggest me in this regard.

Thanks