SQL SERVER – XML Document Could Not be Created Because Server Memory is Low. Use sp_xml_removedocument to release XML documents

A client hit “XML document could not be created” on a server with 300 GB of RAM, most of it free. The message says server memory is low. It is not lying. It is talking about a different pot of memory from the one you are looking at. I measured what OPENXML actually costs, and the number is worse than people expect.

The Error

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

That is error 6624. It usually reaches you wrapped in an application exception, several layers deep, which is part of why it takes so long to identify.

Which Memory It Means

OPENXML does not use SQL Server’s memory the way the rest of the engine does. sp_xml_preparedocument hands the work to the MSXML parser. That parser gets its own allowance: one eighth of the memory available to SQL Server, capped at 4 GB however much the machine has.

I am labelling that clearly. The one eighth and the 4 GB cap are documented figures, not something I measured. Reaching the ceiling here would have meant allocating several gigabytes. I was not willing to do that on a working machine for the sake of a screenshot.

But the shape of it explains the client’s case completely. Their server had 300 GB free and it made no difference, because the parser was never going to be allowed more than 4 GB of it. Adding RAM to a machine hitting this error changes nothing.

What One Document Costs, Measured

This part I did measure. I built a document of 50,000 order elements, 4,639 KB in all. Then I prepared ten copies, deliberately released none of them, and watched SQL Server’s memory.

DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT, @n;   -- and never removed

prepared  process_mb  mb_above_start
0         636         0
1         648         12
2         657         21
3         667         31
4         678         42
5         688         52
6         698         62
7         708         72
8         718         82
9         729         93
10        739         103

Read the last column. Ten documents, 103 MB, and the line is almost perfectly straight. A 4.6 MB document costs about 10 MB while it is prepared, and nothing is shared between copies. Identical content, ten times the cost.

Now scale it up. Take documents ten times that size, with a handful running at once. You are in hundreds of megabytes before anything has gone wrong. Against a 4 GB ceiling, that arithmetic ends where my client ended.

The Handle Leak Nobody Notices

A prepared document lives until you remove it or the connection closes. Not until the batch ends. Not until the procedure returns.

So an early return, a RAISERROR, or any path that skips the last line leaves it behind. With connection pooling it is worse. That connection goes back to the pool still holding the memory, gets handed to the next request, and leaks again.

The shape that survives a failure is this one:

DECLARE @h int;
BEGIN TRY
    EXEC sp_xml_preparedocument @h OUTPUT, @doc;
    INSERT dbo.Orders (id, cust, amt)
    SELECT id, cust, amt
    FROM   OPENXML(@h, '/orders/o', 1)
           WITH (id int '@id', cust nvarchar(20) '@cust', amt decimal(18,2) '@amt');
    EXEC sp_xml_removedocument @h;
    SET @h = NULL;
END TRY
BEGIN CATCH
    IF @h IS NOT NULL EXEC sp_xml_removedocument @h;
    THROW;
END CATCH

One thing worth knowing before you look for proof that the memory came back. After I removed all ten handles, process memory stayed at 739 MB. That is normal, and not a leak. The memory is free again inside SQL Server. SQL Server is simply not in a hurry to hand it back to Windows.

The Fix Is Not More Memory

My client’s pattern was ordinary growth. The job had worked for years, and the documents got bigger. OPENXML did not slow down gradually. It hit a wall.

So I moved them to XQuery. It is parsed by the engine itself rather than by MSXML, so it is not inside that eighth. Then I measured both on the same 4,639 KB document, pulling the same 50,000 rows.

method              ms    rows
OPENXML             821   50000
XQuery incl. CAST   271   50000

OPENXML             829   50000
XQuery incl. CAST   242   50000

OPENXML             780   50000
XQuery incl. CAST   222   50000

Three runs each. XQuery came out about three times faster. I put the CAST to the xml type inside its timing, so both methods start from the same string. Leaving the cast outside would have flattered XQuery.

If the data reaches your procedure already typed as xml, that cast disappears. It does when the application passes an xml parameter. I timed that too. XQuery ran in 62 to 72 milliseconds against OPENXML’s 718 to 872. Ten times, for changing a parameter type.

What the Replacement Looks Like

This is OPENXML:

DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT, @doc;

SELECT id, cust, amt
FROM   OPENXML(@h, '/orders/o', 1)
       WITH (id int '@id', cust nvarchar(20) '@cust', amt decimal(18,2) '@amt');

EXEC sp_xml_removedocument @h;

And this is the same thing in XQuery:

SELECT o.value('@id',   'int')            AS id,
       o.value('@cust', 'nvarchar(20)')   AS cust,
       o.value('@amt',  'decimal(18,2)')  AS amt
FROM   @x.nodes('/orders/o') AS q(o);

No handle. Nothing to remember to release. Nothing to leak on an error path. The whole class of problem in this post stops existing. That is better than fixing it.

If You Cannot Change the Code Today

Find the leaks first, because they are cheap to fix. Search your procedures for sp_xml_preparedocument. Check that each one has a matching removal on every path out, error paths included.

Then look at how many run at once. Documents that are fine one at a time are not fine six at a time. Staggering a batch buys room without touching any logic.

Then change the biggest one to XQuery and leave the rest. You do not have to convert everything in a week. The one handling the largest documents is the one hitting the ceiling.

And for anything new, do not start with OPENXML. It has been the slower option with the worse memory behaviour for years. The only good reason to write it today is that the code around it already does.

Error 6624 is not the server running out of memory, it is a parser running out of its allowance.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.