Can a stored procedure cause a memory leak?

memory-leaks, scheduled-tasks, sql-server, stored-procedures

Solution

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 = '<root>
                      ... some valid xml ... 
                    </root>'

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;

[Note: I rarely use cursors. Wherever possible and appropriate I always try to do it the set-based way]

Just for the record, even though I always like to see an explicit `CLOSE` and `DEALLOCATE` for cursors:

LOCAL cursors are implicitly deallocated 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 deallocated when the parameter or variable referencing the cursor in the code that called the procedure goes out scope. Ref.

Problem

We have a sql server 2008 box. On this server we have a scheduled job that calls a large stored procedure that opens xml files and loads them into tables. After an extended period of up time the SQL server consumes literally all the available memory. (in fact the page file nearly ate all the disc space) Is it possible for a stored proc to leak memory? Is it possible for an SSIS package to leak memory? Thanks in advance!!

Original source