I need to find out more info and haven't had any luck.
What happens to the memory allocated for a XML document when a query uses sp_xml_preparedocument and then the proc sp_xml_RemoveDocument isn't used.
I am not looking for an alternative to using the XML prepare document. I know there are alternatives.
If a query uses sp_xml_preparedocument and the memory is allocated, I can see those documents with handles pretty easily using Select * from sys.dm_exec_xml_handles(0)
That shows me info about the XML documents. But what if the connection that originally created the document is no longer active or the query ended and connection is closed.
when the connection is closed, does the memory get released? does it leak somewhere and become unavailable?
Most importantly, If the memory is still being held somewhere (or leaked and lost) how do I identify it and get it release?
Basically, sp_xml_RemoveDocument does the following two things.
1. Reclaim memory
2. Close Handle
If connection is closed without calling it, the memory and HANDLE will not get released. How can we check it. Here is the one.
SELECT *
FROM sys.dm_os_memory_objects
WHERE type = 'MEMOBJ_MSXML';
If memory keeps growing, we need to pay attention.
If it happens, freesystemcache might not help. To release them, you can try the following workaround.
DECLARE @i INT=1;
WHILE (SELECT * FROM sys.dm_os_memory_objects WHERE type ='MEMOBJ_MSXML' AND pages_allocated_count > 8)
BEGIN
BEGIN TRY
EXEC sp_xml_removedocument @i;
END TRY
BEGIN CATCH
....
END CATCH
SET @i=@i+1
END
BTW, I never tried it. Not sure if it works or not. If no luck, I'm afraid, you need to restart instance to remove them.
GASQL.com - Focus on Database and Cloud