Post reply

Problems with server memory... Not enough storage is available

  • July 10, 2006 at 4:28 am

    #153082

    We are getting the following error after max one hour of operations:

    Server: Msg 6624, Level 16, State 3, Procedure sp_xml_preparedocument, Line 61

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

    Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 61

    XML parsing error: Not enough storage is available to complete this operation.

    There is plenty of disk space for the DB and for the tempDB. (at least 10GB for tempdb and 50GB for the DB) So this is not a problem! DBs are also set to autogrowth.

    Most of the commands are like the one below: (this is the login command)

    EXEC process_command_2

    @command = 'login',

    @unparsedParams = 'login=usename&passwd=94jfk48hjai437gnmsdrfoieroijgm7870534b&uip=193.913.193.193',

    @encryptedSessionID = 'user',

    @paramsXML = 'admin94jfk48hjai437gnmsdrfoieroijgm7870534b193.913.193.193'

    So the XMLs we open are quite small. We don't even need to use text parameters varchar(8000) are always enough.

    We have a win 2k3 server SP1 with 4 GB RAM. The server is set to use dynamic memory: min 1000 MB max 1500MB

    When we start getting the problems, the process sqlservr.exe uses only aprox 900,000 K of RAM. Total RAM occupied on the server is 1.28GB (out of 4GB; we don't use /3GB switch in boot.ini, therefore available for programs are only 2GB)

    If I disconnect from the server in Enterprise Manager I cannot connect again since I get the "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" error.

    Operations in EM are partally blocked: For example If I open a DB Properties, I get "Error 701: There is insuficient system memory to run this query error"

    -------------------

    If put the /3GB switch in boot.ini, when the total ram occupied on the machne reaches approx 2.5GB I get the error:

    Error: 6624

    Procedure: sp_xml_preparedocument

    Line: 61

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

    There must be a root cause for all this

    Thanks!

  • July 10, 2006 at 4:32 am

    #648177

    If anybody volunteers to help me I can provide a RA invitation.

    Please contact me on YM. My ID is sangraalus

    Thanks!

  • July 10, 2006 at 4:54 am

    #648180

    So if I disconnect from the server in EM I cannot connect back because of the above mentioned error. This happens with windows authentication. I can although connect with a SQL login.

    In QA I canot login with win authentication. Only with SQL login. But I get the error when executing a command that handles an XML like the one above.

  • July 10, 2006 at 5:24 am

    #648184

    Maybe this can help you. It is a perfmon snapshot whet the server was blocked.

    http://www.astechnix.ro/gabi/perfmon.JPG

  • July 10, 2006 at 6:12 am

    #648188

    I hate to ask, but are you doing what the error message states you should do to fix the problem?  Specifically, when you prepare an XML document, you get a handle to the document in memory.  You MUST use sp_xml_removedocument to free it from memory.  SQL Server has different memory areas - the XML appears to be in an area that is bounded.

    From books online...

    "A parsed document is stored in the internal cache of Microsoft® SQL Server™ 2000. The MSXML parser 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."

    If you are using this stored proc, then can you post the contents of the "typical" stored proc you call - perhaps there's something in there.

    Look forward to hearing from you.  Cheers, Ian

     

  • July 10, 2006 at 8:49 am

    #648267

    Take a read through Ken Henderson's Guru guide to architecture. The xp_prepare stuff runs in memory out of sql process and has a limited memory pool - I assume, but don't know for sure that this is only resolved using 64bit architecture. I had problems with this when using the /3GB switch ( with 8Gb ram on sql2k) - I assume the same would happen when awe goes over 16Gb too.

    Lots of the sql 2005 stuff runs in the bottom 2gb of ram - I suspect that the only way to get the true performance is on 64bit platforms. As Ian states if you don't clear your handles you will get problems.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • July 10, 2006 at 9:10 am

    #648275

    This was true.

    I has to do a lot of debugging to discover this. An error was generated once in a while between the prepare and remove xml procedures

    This is what I wrote on Exprests Exchange:

    ---------------------------------

    Thanks Richard,

    Your advice led me to the core of the problem:

    I have indeed only pairs of those procedure. But once in a while, there was an error while processing the result (about 7 procedures, and 2 triggers inside the stack) (Permission to execute xp_cmdshell). This error caused to stop the processing imediatelly. Once the memory was filled by these calls, no other call was possible because the memory (maybe the one allocated to each connection) was full.

    So, for everybody to remeber, always handle errors or change to SQL 2005

    Thanks all!

  • July 11, 2006 at 6:53 am

    #648439

    Every time you use sp_xml_preparedocument, God kills a kitten.

    Remeber that.

    heh

  • July 11, 2006 at 7:01 am

    #648443

    excellent Jeff!

    and a dba gains another grey hair !! 

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply