Tuesday, December 30, 2008

XML parsing error: Not enough storage is available to complete this operation

We had a stored procedure which was running fine in the production environment until recently, just out of the blue, we started getting the following SQL exception:
The adapter failed to transmit message going to send port "SendPort1" with URL "SQL://testsvr/testdb/". It will be retransmitted after the retry interval specified for this Send Port. Details:"HRESULT="0x80040e14" Description="The statement has been terminated."
HRESULT="0x80040e14" Description="XML parsing error: Not enough storage is available to complete this operation."

The stored procedure was using sp_xml_preparedocument to insert data from an xml source into the database. The data size was approximately 10MB.

We went through a lot of posts, some of which suggested restarting the server; which obviously is not the solution for the production environment. We tried out a simple trick which worked in our case. Execute the following command on Sql server:
dbcc freeproccache
This command is primarily used to clear the procedure cache.

No comments: