Solved

Warning: unable to allocate 'min server memory' of 2000 MB. Unable to post notification to SQLServerAgent (reason: MapViewOfFile() returned error 8, 'Not enough storage is available to process this co

Posted on 2006-06-22
19
7,340 Views
Last Modified: 2012-08-14
Dear Experts,

We are having hard time with our SQL Server 2000 Standard Edition.

These are a vew of the errors we are receiving:

When trying to start a job in EM/MS, or running some complex SPs:
--------------------
Unable to post notification to SQLServerAgent (reason: MapViewOfFile() returned error 8, 'Not enough storage is available to process this command.')
--------------------

In the SQL Server log when starting we get the warning:
--------------------
Warning: unable to allocate 'min server memory' of 2000 MB.
--------------------

After 1-2 days of work:
--------------------
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection"
--------------------

This and other DLL's when performing several actions in EM (like the properties of the server, etc):
--------------------
Cannot load the DLL xplog70.dll 8 (Not enough storage is available to process this command
--------------------

SQL Server Profiler receives the following error after logging in (when displaying the trace grid):
--------------------
Failed to read trace data.
Unspecified error .... (in Profiler 2000)
An unspecified error... (in Profiler 2005)
--------------------

Event Viewer errors:
--------------------
--------------------
17120 :
SQL Server could not spawn process_loginread thread.
-----------
Every 5-15 mins:
--------------------
Error: 17803, Severity: 20, State: 12
Insufficient memory available.
--------------------

RESTARTING SQL SERVER FIXES ALL THE PROBLEMS FOR THE NEXT 1-2 DAYS.


This is our Server:
Windows 2003 Server Standard Edition, 4 processors, 4 GB RAM, more than half of free space on each Disk. It is a dedicated server. No other applications are running on the same server, but SQL.

SQL Server2000 Standard Edition, configured to use fixed 2000 MB RAM, all 4 processors.

We have one main DB that is set, like all the other ones to grow auto matically.

We have cleared all the logs from both the SQL server and SQL server agent.

Questions: Why can't the server allocate the 2000 MB RAM? We have tried with 1000MB and we get no more warning in the log. I have tried with 1999MB. Sometimes I get the error sometimes I don't. With 2000, I always get the error. When SQL Server Agent is not started together with SQL the error does not always appear if the memory is set below 2000 MB (<2000 MB)



We used to believe it's the transaction log of our main DB. and we truncated it. Our  complete backup is running every week. The differential is running dayly.
this is what we used ( BACKUP LOG dbname WITH TRUNCATE_ONLY )

We are completelly puzzled. We are also on the verge of giving up SQL server and go for Oracle.


Thank you in advance!
0
Comment
Question by:sangraalus
  • 11
  • 8
19 Comments
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 16961455
1) Have you set the server to  use a fixed memory allocation or dynamic.  it is better, especially as there are no oter apps running on the server, to set SQL to dynamically allocate its own memory;

then set the server to favour SQL ("Boost SQL Server Priority" on the "Processer" tab in the Server properties)

2) check the disk allocation/location of Tempdb  some complex queries cache stuff in tempdb; if is not allowed to grow or is on the default location you can have issues
2a) ensure tempdb recovery model is set to "simple" ("Options" tab on Database properties)

3) check the location/space allocation of the SQL logs

4) errrr...
5) um...

/Richard
0
 
LVL 1

Author Comment

by:sangraalus
ID: 16962435
Thanks Richard for the reply, but, :(

my problem is not a performance one but a problem of SQL not working.

1) I have read on a MS site that fixed memory is better for dedicated servers, since the server never has to free space. I am absolutelly sure that making it dynamic SQL will behave the same, 'cause there is no other applic on the server and there is the rest of 2GB up until 4GB to consume.

 and i have boosted performance as well

2) tempdb is at its defaul location and the drive has 10 GB free. No other DB but the master and the msdb one are located on that partition (their total size alltogether is less than 500 MB)

3)plenty of spaced, and cleared

and the recovery model is simple

Additional comments:

we also encounter many time the error:
SuperSocket info: (SpnRegister) : Error 1355.

When trying to acces the properties of a database on the Permissions tab:
Error 701: There is insufficient system memory to run this query,

0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 17010586
ouch!!

what user profile is SQL Agent running (local system or domain\account)?

it looks as if there are some form of memory leakage!! though i have not known it in many years of uninterrupted SQL use!!

Do you use the OLE (sp_OACreate etc) or xp_ (eg xp_Startmail) stored procedures; these can leak memory.

Otherwise, I'm stumped here sorry.  

/Richard
0
 
LVL 1

Author Comment

by:sangraalus
ID: 17016871
Hi,

thanks for the reply,


Use use sp_OACreate to send an email if a job fails.

The problem is that the job fails due to our unknows issue:
Executed as user: NT AUTHORITY\SYSTEM. Cannot load the DLL odsole70.dll, or one of the DLLs it references. Reason: 8(Not enough storage is available to process this command.). [SQLSTATE 42000] (Error 0)

The normal behaviour does not send an email.

We use xp_crypt....

But we have been using the same system for 3 years. The problem appeared only one month ago.
0
 
LVL 1

Author Comment

by:sangraalus
ID: 17029869
I have set the virtual memory to system managed mode but the problems remain the same.

:(((((
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 17030832
i have just seen this article on MS SQL server site...

http://support.microsoft.com/default.aspx?scid=kb;EN-US;840856

Looks as if there is a feature in SP3!!

/Richard
0
 
LVL 1

Author Comment

by:sangraalus
ID: 17031214
This article you mentioned discusses some issues regarding the SP3 but I have SP4 installed.

Thanks anyway!
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 17036383
when did u install sp4 - ie did problem manifest after sp4 installation?

/R
0
 
LVL 1

Author Comment

by:sangraalus
ID: 17037089
It might have appeared after the SP4 installation. There was no other major change in the meanwhile.

If this is true, how can I rollback to SP3?

Thanks!
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 10

Expert Comment

by:RichardCorrie
ID: 17037663
> how can I rollback to SP3?

the short answer is that you cannot!!

except uninstall/reinstall/reapply sp3 then reattach databases..  Gulp!!

I am out of ideas; have you tried the MS SQL Forum?

Sorry i have not been much help!!

/Richard
0
 
LVL 1

Author Comment

by:sangraalus
ID: 17037740
I have tried on many forums, but no usefull answers so far.

Thanks anyway!
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 17040919
have you trired www.sqlservercentral.com ?
/R
0
 
LVL 1

Author Comment

by:sangraalus
ID: 17041222
Trying now!
0
 
LVL 1

Author Comment

by:sangraalus
ID: 17071620
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 = '<params><login>admin</login>
<passwd>94jfk48hjai437gnmsdrfoieroijgm7870534b</passwd><uip>193.913.193.193</uip></params>'

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!

0
 
LVL 1

Author Comment

by:sangraalus
ID: 17071793
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.
0
 
LVL 1

Author Comment

by:sangraalus
ID: 17071947
Maybe this can help you. It is a perfmon snapshot whet the server was blocked.

http://www.astechnix.ro/gabi/perfmon.JPG
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 17071955
do you use  sp_xml_removedocument after you have finished with you XML.

SQL2000 uses an external COM object to access XML documents; I have had issues (though not the same as yours) if I have not been strict about releasing the XML document.

/Richard
0
 
LVL 10

Accepted Solution

by:
RichardCorrie earned 435 total points
ID: 17071996
from BOL:
Note  A parsed document is stored in the internal cache of 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.

/Richard
0
 
LVL 1

Author Comment

by:sangraalus
ID: 17073347
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!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now