473,883 Members | 1,626 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

HELP - SQL Server Crash ? Memory leak ?

Hi everybody !

I´m maintaining a large intranet (approx 10000 concurrent users) running on
one IIS box and one DB box with sqlserver 2000.

Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed on
the db box.
Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem to
be using it all.

Currently SQLSERVER 2000 crashes at least once a day.
Its very weird, I run performance monitor with counters on, memory, disk
usage, num users, locks and such.

There is no indications in the counters before the crashes, they just happen
very sudden.
Only indication is that sqlserver makes some huge jumps in memory usage and
mostly the sqlserver then crashes an hour or 2 later.

The only thing that peaks a lot are the locks/sec counter.

My analysis of disk usage, queues etc. tells me i got no kind of i/o
bottlenecks.

Can anybody give me a clue as to what i should do ?

Best regards, Thue
Jul 20 '05 #1
19 14856

"Thue Tuxen Sørensen" <tu***@esynergy .dk> wrote in message
news:3f******** **************@ dread14.news.te le.dk...
Hi everybody !

I´m maintaining a large intranet (approx 10000 concurrent users) running on one IIS box and one DB box with sqlserver 2000.

Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed on the db box.
Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem to
be using it all.

Currently SQLSERVER 2000 crashes at least once a day.
Its very weird, I run performance monitor with counters on, memory, disk
usage, num users, locks and such.

There is no indications in the counters before the crashes, they just happen very sudden.
Only indication is that sqlserver makes some huge jumps in memory usage and mostly the sqlserver then crashes an hour or 2 later.

The only thing that peaks a lot are the locks/sec counter.

My analysis of disk usage, queues etc. tells me i got no kind of i/o
bottlenecks.

Can anybody give me a clue as to what i should do ?
First, make sure you've applied all the latest service packs.

Also, look at the most recent errorlog after a crash (errorlog.1 most
likely). It should have a dump of what was going on.

That might give you a clue.

Also check your event log for anything.

Finally, if this doesn't turn up anything, call Microsoft.

SQL Server does not normally crash. I have some boxes that ran for more
than a year before we had to reboot them due to a physical move.


Best regards, Thue

Jul 20 '05 #2
1. Check the server logs for any informative error messages.


"Thue Tuxen Sørensen" <tu***@esynergy .dk> wrote in message
news:3f******** **************@ dread14.news.te le.dk...
Hi everybody !

I´m maintaining a large intranet (approx 10000 concurrent users) running on one IIS box and one DB box with sqlserver 2000.

Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed on the db box.
Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem to
be using it all.

Currently SQLSERVER 2000 crashes at least once a day.
Its very weird, I run performance monitor with counters on, memory, disk
usage, num users, locks and such.

There is no indications in the counters before the crashes, they just happen very sudden.
Only indication is that sqlserver makes some huge jumps in memory usage and mostly the sqlserver then crashes an hour or 2 later.

The only thing that peaks a lot are the locks/sec counter.

My analysis of disk usage, queues etc. tells me i got no kind of i/o
bottlenecks.

Can anybody give me a clue as to what i should do ?

Best regards, Thue

Jul 20 '05 #3
[posted and mailed, vänligen svara i nys]

Thue Tuxen Sørensen (tu***@esynergy .dk) writes:
Only indication is that sqlserver makes some huge jumps in memory usage
and mostly the sqlserver then crashes an hour or 2 later.


The fact that the memory usage of SQL Server jumps, is perfectly normal,
and is only a sign of that someone is using the application.

By default, SQL Server grabs as much memory it can. This is because the
bigger the cache SQL Server can have, the better will the response time
be.

Possible causes for SQL Server crashes:

* Bug in SQL Server, provoked by some SQL statement.
* Access violation in a extended procedure or OLE object that is called
by SQL Server from application code.
* Hardware problems.

The error log for SQL Server should give information about the case.

If I were you, I would investigate the second point before I opened a
case with Microsoft, because this is the most likely reason.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Thanks for all the answers !

Its running with SP3.

I think I explained the crashes a bit wrong before maybe ...
What I mean is that the sqlserver suddenly 'hangs' and that its impossible
to communicate with in any way.
The performance monitor also stops getting input and just freezes.

The only way to get the site up running again is to restart the sqlserver
service (not the server).
Theres no indication in the errorlogs, as to what happens just before the
'crash'.
Ive looked through all of them, to see if any of them had some info I could
use.

All errorlogs begin with info regarding the startup of sqlserver
initialising the listener and starting up the db´s and such.
After all the info regarding the startup there is nothing in the log.

The next piece of info in the log is the entry where it writes that
sqlserver is terminating due to 'stop' request from service control manager.

And the stop request is issued by me after the system has crashed / is
hanging.

The event viewer is also not helping with anything.
No messages regarding what could cause the error.

I´m really frustrated about the problem, because I don´t have a clue to
chase down.
But thanks agian for all the answers and your time.
Please do not hesitate to write ! :o) if any of you suddenly comes up with
more things I could check out before calling in a pro.

Best Regards Thue
Jul 20 '05 #5
Hi there

I share your pain and frustration. I would like all possible causes of CPU
100% to be listed somewhere so I can check that I have taken all precautions
to avoid this. Is there such a page anywhere?!

I want to resolve the CPU 100% problem myself - I thought I had resolved
before but has come back to haunt me now with a new database server machine
with SQL Server 2000, SP 3, which apparently does not allow one to fully
remove the "named pipes" protocol. I resolved a CPU 100% issue months ago by
making sure only TCP/IP was used as the protocol, and removing Named pipes.
Removing from the Enterprise manager (button at bottom in general settings
tab), network protcol "named pipes", and from client connection settings
manager, so that only TCP/IP is allowed as a network protocol. My theory is
that these pipes become blocked, and this causes 100% CPU usage. Could
anyone confirm that this is a known symptom of the named pipes protocol ?!

I am currently having to reboot the machine every few days now since we put
in a new database server with the latest service packs (SQL Server 2000,
SP3). Removing the named-pipes protcol does not seem to have resolved this
nasty problem this time round. I have seen on some newsgroup postings, that
it is no longer possible to actually remove Named Pipes fully since SP3.

The following article thread indicates this:-
http://www.mcse.ms/message97673.html

which is kind of worrying to me, because I was fairly sure removing Named
Pipes as a protocol before, completely cured the CPU 100% symptoms.

My correspondence chess website www.chessworld.net makes heavy use of SQL
Server 2000. It has been running for over 2 years now, and sometimes has
about 200 members online or more within the space of 10 minutes. Overall SQL
Server 2000 has been great, but recently these reboots have been quite
frustrating, and I cannot seem to identify the cause. I continually monitor
any ASP pages that time out with SQL Server errors, and always keen to
ensure all queries run quick on my site. I do not think it is a bad sql
query problem. I continually make efforts to optimise all queries used on
the site. I have also made sure from a long time ago that (NO LOCK) is being
used on select statements to minimise lock escalation.

I found the following article today which is another possible cause of CPU
100%:-

http://support.microsoft.com/default...NoWebContent=1

which possible attributes the Microsoft search service to CPU 100%. I have
now disabled this service from our new database server machine, and put it
to Manual on Startup.

Help needed to resolve CPU 100% issue !

Best wishes
Tryfon Gavriel
Webmaster
www.chessworld.net


"Thue Tuxen Sørensen" <tu***@esynergy .dk> wrote in message
news:3f******** **************@ dread14.news.te le.dk...
Hi everybody !

I´m maintaining a large intranet (approx 10000 concurrent users) running on one IIS box and one DB box with sqlserver 2000.

Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed on the db box.
Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem to
be using it all.

Currently SQLSERVER 2000 crashes at least once a day.
Its very weird, I run performance monitor with counters on, memory, disk
usage, num users, locks and such.

There is no indications in the counters before the crashes, they just happen very sudden.
Only indication is that sqlserver makes some huge jumps in memory usage and mostly the sqlserver then crashes an hour or 2 later.

The only thing that peaks a lot are the locks/sec counter.

My analysis of disk usage, queues etc. tells me i got no kind of i/o
bottlenecks.

Can anybody give me a clue as to what i should do ?

Best regards, Thue

Jul 20 '05 #6

"Tryfon Gavriel" <tr****@gtryfon .demon.co.uk> wrote in message
news:bt******** ***********@new s.demon.co.uk.. .
Hi there

I share your pain and frustration. I would like all possible causes of CPU
100% to be listed somewhere so I can check that I have taken all precautions to avoid this. Is there such a page anywhere?!

I want to resolve the CPU 100% problem myself - I thought I had resolved
before but has come back to haunt me now with a new database server machine with SQL Server 2000, SP 3, which apparently does not allow one to fully
remove the "named pipes" protocol. I resolved a CPU 100% issue months ago by making sure only TCP/IP was used as the protocol, and removing Named pipes. Removing from the Enterprise manager (button at bottom in general settings
tab), network protcol "named pipes", and from client connection settings
manager, so that only TCP/IP is allowed as a network protocol. My theory is that these pipes become blocked, and this causes 100% CPU usage. Could
anyone confirm that this is a known symptom of the named pipes protocol ?!
Nope, never seen that happen.

I am currently having to reboot the machine every few days now since we put in a new database server with the latest service packs (SQL Server 2000,
SP3). Removing the named-pipes protcol does not seem to have resolved this
nasty problem this time round. I have seen on some newsgroup postings, that it is no longer possible to actually remove Named Pipes fully since SP3.

The following article thread indicates this:-
http://www.mcse.ms/message97673.html

which is kind of worrying to me, because I was fairly sure removing Named
Pipes as a protocol before, completely cured the CPU 100% symptoms.

My correspondence chess website www.chessworld.net makes heavy use of SQL
Server 2000. It has been running for over 2 years now, and sometimes has
about 200 members online or more within the space of 10 minutes. Overall SQL Server 2000 has been great, but recently these reboots have been quite
frustrating, and I cannot seem to identify the cause. I continually monitor any ASP pages that time out with SQL Server errors, and always keen to
ensure all queries run quick on my site. I do not think it is a bad sql
query problem. I continually make efforts to optimise all queries used on
the site. I have also made sure from a long time ago that (NO LOCK) is being used on select statements to minimise lock escalation.
Keep in mind that is NOT always a good solution.

I found the following article today which is another possible cause of CPU
100%:-

http://support.microsoft.com/default...NoWebContent=1
which possible attributes the Microsoft search service to CPU 100%. I have
now disabled this service from our new database server machine, and put it
to Manual on Startup.

Help needed to resolve CPU 100% issue !
Best bet is probably to try to have profiler running.
There can be many reasons. Keep in mind it's perfectly possible to be using
100% of the CPU and it not be a bug etc. It could simply be you're that
busy.

We have a DB server that from time to time hits 100% CPU. And stays that
way for a few seconds or more. It hurts performance, but returns to normal.


Best wishes
Tryfon Gavriel
Webmaster
www.chessworld.net


"Thue Tuxen Sørensen" <tu***@esynergy .dk> wrote in message
news:3f******** **************@ dread14.news.te le.dk...
Hi everybody !

I´m maintaining a large intranet (approx 10000 concurrent users) running

on
one IIS box and one DB box with sqlserver 2000.

Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed

on
the db box.
Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem to be using it all.

Currently SQLSERVER 2000 crashes at least once a day.
Its very weird, I run performance monitor with counters on, memory, disk
usage, num users, locks and such.

There is no indications in the counters before the crashes, they just

happen
very sudden.
Only indication is that sqlserver makes some huge jumps in memory usage

and
mostly the sqlserver then crashes an hour or 2 later.

The only thing that peaks a lot are the locks/sec counter.

My analysis of disk usage, queues etc. tells me i got no kind of i/o
bottlenecks.

Can anybody give me a clue as to what i should do ?

Best regards, Thue


Jul 20 '05 #7

"Thue Tuxen Sørensen" <tu***@esynergy .dk> wrote in message
news:3f******** **************@ dread14.news.te le.dk...
Thanks for all the answers !

Its running with SP3.
Good.


I think I explained the crashes a bit wrong before maybe ...
What I mean is that the sqlserver suddenly 'hangs' and that its impossible
to communicate with in any way.
You contradict this down below. Which is somewhat critical.

The performance monitor also stops getting input and just freezes.

What metrics are you measuring.

The only way to get the site up running again is to restart the sqlserver
service (not the server).
Theres no indication in the errorlogs, as to what happens just before the
'crash'.
Ive looked through all of them, to see if any of them had some info I could use.

All errorlogs begin with info regarding the startup of sqlserver
initialising the listener and starting up the db´s and such.
After all the info regarding the startup there is nothing in the log.

The next piece of info in the log is the entry where it writes that
sqlserver is terminating due to 'stop' request from service control manager.

Ok. This indicates that the server IS listening.

One thing you may want to do is issue a NET STOP SQLSERVERAGENT command
followed by NET STOP MSSQLSERVER and see which one (if either takes a long
period of time).

When starting does it start up quickly or take time? Is there anything in
the error log about recovering a DB?

Also, does your app call ANY extended stored procs (XP_fooname)


And the stop request is issued by me after the system has crashed / is
hanging.

Yeah. I wouldn't call this a crash. Not even sure I'd call it a hang. But
that's partly semantics.
The event viewer is also not helping with anything.
No messages regarding what could cause the error.

I´m really frustrated about the problem, because I don´t have a clue to
chase down.
But thanks agian for all the answers and your time.
Please do not hesitate to write ! :o) if any of you suddenly comes up with
more things I could check out before calling in a pro.

Just the above.

What happens if you wait? (how long do you wait before cycling it?)

Best Regards Thue

Jul 20 '05 #8
Tryfon Gavriel (tr****@gtryfon .demon.co.uk) writes:
I am currently having to reboot the machine every few days now since we
put in a new database server with the latest service packs (SQL Server
2000, SP3). Removing the named-pipes protcol does not seem to have
resolved this nasty problem this time round. I have seen on some
newsgroup postings, that it is no longer possible to actually remove
Named Pipes fully since SP3.


100% CPU may not be cause for alarm. When SQL Server becomes completely
unresponsive, it certainly is.

I know of two way this can happen. Or rather, I know of one, and one
"seemingly unresponsive". The one case where it becomes unresponsive,
is error 17883. If this happens, you should see this in the error log,
where you get a load of these messages. The message only appears with
SP3 or later hotfixes.

The other case I've seen was with some poor SQL. In this particular
case I was testing performance of this poor SQL for an article on my
web site. I was surprised to see that this particular query took so
much CPU, that issuing an sp_who could have a response time on over
30 seconds.

But there are probably more possibilities than these two. But then again,
it certainly not somehing which happens all over town, so if your SQL
Server becomes unresponsive, there is something fishy on your machine,
be that hardware or poor SQL statements.

One way to track down the latter is to have a profiler trace running,
and see what you get just before the machine goes into nirvana.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #9
Hi again.

Im measuring CPU, MEMORY, LOCKS and Disk Usage.
Nothing speciel in the logs about revovering.
We don´t use any extended procedures.
I have´nt tried to wait for a long time before restarting it, because theres
a lot of users waiting for it to be up again.
Usually i wait like 5 minutes or so.

/Thue

"Greg D. Moore (Strider)" <mo*****@greenm s.com> skrev i en meddelelse
news:k2******** ***********@twi ster.nyroc.rr.c om...

"Thue Tuxen Sørensen" <tu***@esynergy .dk> wrote in message
news:3f******** **************@ dread14.news.te le.dk...
Thanks for all the answers !

Its running with SP3.
Good.


I think I explained the crashes a bit wrong before maybe ...
What I mean is that the sqlserver suddenly 'hangs' and that its impossible to communicate with in any way.


You contradict this down below. Which is somewhat critical.

The performance monitor also stops getting input and just freezes.


What metrics are you measuring.

The only way to get the site up running again is to restart the sqlserver service (not the server).
Theres no indication in the errorlogs, as to what happens just before the 'crash'.
Ive looked through all of them, to see if any of them had some info I

could
use.

All errorlogs begin with info regarding the startup of sqlserver
initialising the listener and starting up the db´s and such.
After all the info regarding the startup there is nothing in the log.

The next piece of info in the log is the entry where it writes that
sqlserver is terminating due to 'stop' request from service control

manager.

Ok. This indicates that the server IS listening.

One thing you may want to do is issue a NET STOP SQLSERVERAGENT command
followed by NET STOP MSSQLSERVER and see which one (if either takes a long
period of time).

When starting does it start up quickly or take time? Is there anything in
the error log about recovering a DB?

Also, does your app call ANY extended stored procs (XP_fooname)


And the stop request is issued by me after the system has crashed / is
hanging.


Yeah. I wouldn't call this a crash. Not even sure I'd call it a hang.

But that's partly semantics.
The event viewer is also not helping with anything.
No messages regarding what could cause the error.

I´m really frustrated about the problem, because I don´t have a clue to
chase down.
But thanks agian for all the answers and your time.
Please do not hesitate to write ! :o) if any of you suddenly comes up with more things I could check out before calling in a pro.


Just the above.

What happens if you wait? (how long do you wait before cycling it?)

Best Regards Thue


Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
5563
by: Mark D. Anderson | last post by:
About a month ago Richard Cornford did an interesting analysis of a memory leak in jscript (internet explorer) when there are "circular" references between DOM objects and (real) jscript objects: http://groups.google.com/groups?selm=bcq6fn%24g53%241%248300dec7%40news.demon.co.uk This message summarizes some testing I've done and their results. These results somewhat contradict Cornford's conclusions; I haven't
32
3879
by: John | last post by:
Hi all: When I run my code, I find that the memory that the code uses keeps increasing. I have a PC with 2G RAM running Debian linux. The code consumes 1.5G memory by the time it finishes execution. But I do not think it needs so much memory. About 500M memory should be enough. I have following questions about memory leak. (1).If in my code I only define constructor for my class, and do not define destructor, will it cause memory leak?
2
2320
by: Prashant | last post by:
Hello All, I am new to win32 programming,I was trying to get all the Subkeys under any Key. I have written a C program for achieving this, I am using WinNT Server . In this program *networkCardKeyList is a Global array The memory allocated to the pointers in this array are freed in the main function.
4
6100
by: Don Nell | last post by:
Hello Why is there a memory leak when this code is executed. for(;;) { ManagementScope scope = new ManagementScope(); scope.Options.Username="username"; scope.Options.Password="password"; scope.Path.Path=@"\\pc\root\cimv2";
13
1559
by: Boni | last post by:
I use 3-d party component. In this component I must pass a reference to my object. The problem is that this component has an ugly bug.When this component is disposed, it incorrectly don't delete the reference to my object from one of its shared lists.And since the operation repeats many times the leak is huge. Is there a way to kill my object anyway? Thanks a lot, Boni
1
2322
by: Charlotte | last post by:
Hello, We are doing a stability test on our application to prepare for Microsoft SQL Server 2000 certification, and we found a memory leak, but we cannot find its source and fix it. We use an ASP page written in JScript that connects to Analysis Services 2000 using ADOMD and retrieves data from it (this is a very simplified version of our real application that we created just to test the memory leak), see the code below.
0
1213
by: hairobinson | last post by:
Can any one help me explaing about Kernel Memory leak? what is kernel memory leak? how do we debug Kernel level Memory Leak? Do we have standard tool for finding it? How do we differentiate Kernel level memory leak with Application level Memory leak? Thanks in Advance,
1
13999
by: cherman | last post by:
I don't know if anybody here worked with gSOAP software, but I do hope so. I've made some simple server app using gSOAP example. Everythig worked fine until I switched it into ssl mode. Using it results in huge memory leak. My question is if anybody has any idea what am I doing wrong or what am I missing? Here is some of my code: ----------------------------------------------------------------------------------------------------- ...
22
9378
by: Peter | last post by:
I am using VS2008. I have a Windows Service application which creates Crystal Reports. This is a multi theaded application which can run several reports at one time. My problem - there is a memory leak someplace. I can not detect the memory leak by running several reports by hand, but when I run tha app as a servrice and process few hundred reports there is significant memory leak. The application can consume over 1GB of memory where it...
0
9793
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10858
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10420
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9582
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7974
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7134
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5804
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5996
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4225
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.