Yes, a memory leak is possible if you forget to call sp_xml_removedocument (for each matching sp_xml_preparedocument):
A parsed document is stored in the internal cache of SQL Server. The MSXML parser (Msxmlsql.dll) uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.
Example usage:
DECLARE @xml_text VARCHAR(4000), @i INT SELECT @xml_text = '... some valid xml ...' EXEC sp_xml_preparedocument @i OUTPUT, @xml_text .... EXEC sp_xml_removedocument @i
Another form of memory leak is forgetting to both close and deallocate a cursor:
DECLARE c CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR SELECT ... .... CLOSE c; DEALLOCATE c;
Just for the record, even though I always like to see an explicit CLOSE and DEALLOCATE for cursors:
LOCAL cursors are implicitly de-allocated when the stored procedure, trigger, or batch in which they were created terminates, unless the cursor has been passed back as a parameter. The LOCAL cursor will then be implicitly de-allocated when the parameter or variable referencing the cursor in the code that called the procedure goes out scope.
Ref.
[Note: I rarely use cursors. Wherever possible and appropriate, I always try to do it the set-based way]