General Network Error/Memory Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

General Network Error/Memory Problem

Author

Topic

peddi_praveen
Starting Member

48 Posts

Posted - 2003-12-10 : 02:25:10

Hi Guys,
We are experiencing "General NetWork Error.Check Your Documentation". This error frequently taking place while a particular job runs.
This Job Calls a SP, which in turn, reads XML Of 400MB using sp_xml_Preparedocument,and process, puts in temp tables ... then moves the data from temp tables to permanent tables.

One of the factors that seem to have been realistically eliminated is actual network problems.
We were able to simulate the error by running sptest on the SQL server itself - using local loopback. So port no and IP address as well as network latency seems to be eliminated now.

We did see a lot of event logs from SQL server indicating insufficient memory. SQL Server logs themselves had some memory dumps which were also seen on the SQL profiler. When the server was rebooted, the problem seemed to have disappeared.

Apparently, there was some residual memory leak which was causing SQL server to run out of memory.

One of the things highlighted was that this could be because of calling sp_preparexml repeatedly in a loop at very short intervals - which is exactly what we are doing.

Im addressing this issue with following approach:
Capture Memory Statistics in normal scenario:
*SqL Server BufferManager:Cache Size
(RAM is devoted to SqL server’s data cache?)
*SqLServer:Memory Manager: Total Server Memory(KB)
*SqLServer:Memory Manager: Target Server Memory(KB)
(Is Memory Sufficeient?)
*SQL Server Buffer Mgr : Page Life
expectancy
*SQL Server Buffer Mgr :Lazy Writes/Sec
*SQL Server Buffer Mgr :Check Point
Pages/Sec
(Is Buffer Cache big enough)

*SQL Server: SQL Statistcs: Batch
Requests/Sec
(How Busy SQL Server CPU?)

->Profile All the Above things while this job runs.
Additionally, While the Job runs, Im also capturing following additional information
from SqL Profiler

Stored Procedures Event:
SP:Start
SP:Stmt Starting
Sp:Stmt Completed
SP:completed
SP:Recompile
SP:CacheInsert
SP:CacheMiss

Databases Event:
DataFile AutoGrow
LogFile AutoGrow

Locks Event:
Lock:Acquired
Lock:Cancel
Lock:DeadLock
Lock:Escalation
Lock:Timeout

Server:
Server Memory Change

Errors and Warnings:
ErrorLog
EventLog

Here is soe relevant data about the Server configuration

O/S Name...............: Windows NT 5.0
Service Pack..............: Build 2195: Service Pack 4
DBMS .....................: Microsoft SQL Server 2000
Edition...................: Enterprise Edition
Version...................: 8.00.818

ProcessorCount 8 8
ProcessorType 586 PROCESSOR_INTEL_PENTIUM
PhysicalMemory 3840 3840 (4026015744)

3. Databases Details

Database actual_db Settings :trunc. log on chkpt,autoshrink,concat NULL yields NULL,ANSI NULLs,ANSI warnings
******************************
Database tempdb Settings :select into/bulk copy,trunc. log on chkpt
Any Help/Direction is sought to resolve this issue.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-10 : 12:22:36

From what you have described, you are troubleshooting this correctly. Once you have collected the PerfMon data, post the values here so that we can assist. I'd be interested in the Total and Target memory for SQL Server. I'd also be interested in other counters. sql-server-performance.com is an excellent site for information.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2003-12-10 : 13:34:03

You should probably open up a Microsoft Support ticket for this also. It only costs $250, and they will throw some resources at it. you don't even have to pay for it if it turns out to be a Microsoft issue. Many times they will have already ran into this. They will want your dump files, a sqldiag file, and a little more information. No sense in doing it yourself when someone has probably had the same issue and it's cheap.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

peddi_praveen
Starting Member

48 Posts

Posted - 2003-12-11 : 07:30:01

Hi Guys,
Tx for the suggestion, Unfortunately, iam unable to re-produce the Error in My QA environment. Interestingly, what we found is,the problem is disappeared after re-boot the system. I just need to wait some more time to monitor this. Once , it occurs, it occurs frequently.
So,will have to wait for some more time.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources