Nov 21, 2013

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


When we will  open the XML doc using stored procedure sp_xml_preparedocument in sql server while we are not removing it by executing the procedure:

EXECUTE SP_XML_REMOVEDOCUMENT @hdoc

We may get error message:

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

Solution:

Step 1:

Get the session_Id by executing the following sql query which is holding the active handler opened by sp_xml_preparedocument

SELECT * FROM SYS.DM_EXEC_XML_HANDLES(0)

Step 2: Now kill the all the session_Id one by one which has returned by above query:

KILL Session_Id

For example:

KILL 175
KILL 76
....

It will solve this problem!!

2 comments:

  1. Hi Ritesh,

    I am getting the similar error. I don't see any open xml documents and Database memory usage is not too high.
    Any thoughts ?

    Msg 6624, Level 16, State 1, Procedure sp_xml_preparedocument, Line 1
    XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents.

    /*------------------------
    select * from sys.dm_exec_xml_handles(0)
    ------------------------*/
    session_id document_id namespace_document_id sql_handle statement_start_offset statement_end_offset creation_time original_document_size_bytes original_namespace_document_size_bytes num_openxml_calls dormant_duration_ms row_count
    ----------- ----------- --------------------- ---------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------------- ----------------------- ---------------------------- -------------------------------------- -------------------- -------------------- --------------------

    (0 row(s) affected)

    /*------------------------
    select sum(memory_usage) from sys.dm_exec_sessions
    ------------------------*/

    -----------
    77

    (1 row(s) affected)

    ReplyDelete
  2. Hi Ritiesh,

    How to use SELECT * FROM SYS.DM_EXEC_XML_HANDLES(0) and
    KILL Session_Id in my sp.....
    because if I decalare one variable and store that variable in above query result then show mulitple result error...means query is execute then then getting multiple session id then it is not possible to store single variable values.....i am use distinct and get single result and assign kill command(e.g KILL session_id ) but show error in kill command....
    How to resolved this issue please help me....
    thanks in advance...

    ReplyDelete