asp/sql server memory leak

[scott_mcarthur2003@yahoo.co.uk]

We are running asp (not asp.net) and vb com dll web sites on a machine
which also has sql server 2000 sp3a running and uses Windows Server
2003. We recently migrated from Windows 2000 (same database) where we
had no problems. However on the new machine, despite much being much
more powerful and having the same usage, the box is periodically
grinding to a halt. The cause seems to be sql server using excessive
amounts of memory, it steadily builds up to after a reboot to a high
level. This seems to point in the direction of a memory leak, is
anyone aware of any such problems with asp/sql server on windows 2003
and how to diagnose/fix them. I am aware of some unclosed connections
in the asp code and objects that have not be set to nothing but this
did not seem to cause a problem in the old machine and it is not
practical to fix them all in the short term. I thought it might be
unclosed connections although when I looked at SQL Server:General
Statistics and User Connections I did not seem to see a large or
increasing number of connections.

[Roji. P. Thomas]

Cross post?
I am seeing this message everywhere.

--
Roji. P. Thomas
SQL Server Programmer ;)
________________________
<scott_mcarthur2003@yahoo.co.uk> wrote in message
news:92c92d63.0311240147.8a86775@posting.google.co m...

> We are running asp (not asp.net) and vb com dll web sites on a machine
> which also has sql server 2000 sp3a running and uses Windows Server
> 2003. We recently migrated from Windows 2000 (same database) where we
> had no problems. However on the new machine, despite much being much
> more powerful and having the same usage, the box is periodically
> grinding to a halt. The cause seems to be sql server using excessive
> amounts of memory, it steadily builds up to after a reboot to a high
> level. This seems to point in the direction of a memory leak, is
> anyone aware of any such problems with asp/sql server on windows 2003
> and how to diagnose/fix them. I am aware of some unclosed connections
> in the asp code and objects that have not be set to nothing but this
> did not seem to cause a problem in the old machine and it is not
> practical to fix them all in the short term. I thought it might be
> unclosed connections although when I looked at SQL Server:General
> Statistics and User Connections I did not seem to see a large or
> increasing number of connections.

[Bob Barrows]

Please do not multipost Scott. This is definitely a database-related
question so .asp.db was the perfect group in which to post it. Posting it
here as well did not increase your chances of getting an answer (most of us
subscribe to both groups). On the contrary, if somebody had taken his time
to answer it here, only to find that it was already resolved in the other
group, that person may have been annoyed enough to ignore any future posts
from you, thereby decreasing your chances of getting help in the future.


There are times when you will not be sure which group is most appropriate,
and you will want to post a question to both groups. In that situation, you
should use the cross-posting technique, rather than posting the same message
multiple times. To crosspost, put a semicolon-delimited* list of the
newsgroups to which you wish to post in the To: header of your post and post
it once. It, and any replies to it, will appear in all the newsgroups in
your list. So, if I reply in .asp.db,
my reply will also appear here in .asp.general.

* ... or whatever delimiter is recognized by your news client

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows]

[email]scott_mcarthur2003@yahoo.co.uk[/email] wrote:

> We are running asp (not asp.net) and vb com dll web sites on a machine
> which also has sql server 2000 sp3a running and uses Windows Server
> 2003. We recently migrated from Windows 2000 (same database) where we
> had no problems. However on the new machine, despite much being much
> more powerful and having the same usage, the box is periodically
> grinding to a halt. The cause seems to be sql server using excessive
> amounts of memory, it steadily builds up to after a reboot to a high
> level.

SQL Server is designed by default to take all available memory. If more
memory becomes available, it grabs it. You are describing the default
behavior. You can limit the memory used by SQL in the server configuration
screen in Enterprise Manager. But this rarely needs to (should) be done. I
will say that MS recommends not using IIS and SQL on the same box if you
need SQL to perform to its maximum capabilities. SQL needs as much memory as
you can throw at it, especially when used in high-throughput multi-user
applications.

> This seems to point in the direction of a memory leak, is

This is definitely a possibility

> anyone aware of any such problems with asp/sql server on windows 2003
> and how to diagnose/fix them. I am aware of some unclosed connections
> in the asp code and objects that have not be set to nothing but this
> did not seem to cause a problem in the old machine and it is not
> practical to fix them all in the short term.

It's a likely source of the problem ... Sounds like it may be practical to
fix them after all ....

>I thought it might be
> unclosed connections although when I looked at SQL Server:General
> Statistics and User Connections I did not seem to see a large or
> increasing number of connections.

The number of connections does not need to be large. A good sign is an
orphaned connection, especially if you cannot kill it. There does not need
to be a large number of connections leaking memory to cause IIS to crash. Al
l it takes is one.

However, here are a few other things you can look at:

[url]http://support.microsoft.com?kbid=309051[/url]

Check out the IIS6 Resource Kit:
[url]http://www.microsoft.com/downloads/details.aspx?FamilyID=56fc92ee-a71a-4c73-b628-ade629c89499&DisplayLang=en[/url]

Also, run IISState to capture your problem and have it looked at:
[url]http://www.iisfaq.com/default.aspx?view=P197[/url]



Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[scott_mcarthur2003@yahoo.co.uk]

Thanks for the response, sorry about the multipost, not sure exactly
where to post in and was getting a bit desparate. One question, how
can I tell if a connection has been "orphaned"

> The number of connections does not need to be large. A good sign is an
> orphaned connection, especially if you cannot kill it. There does not need
> to be a large number of connections leaking memory to cause IIS to crash. Al
> l it takes is one.
>
> However, here are a few other things you can look at:
>

[Bob Barrows]

[email]scott_mcarthur2003@yahoo.co.uk[/email] wrote:

> Thanks for the response, sorry about the multipost, not sure exactly
> where to post in and was getting a bit desparate. One question, how
> can I tell if a connection has been "orphaned"
>

Several indicators:
- connections from a non-running application are present
- The "last batch" was several hours ago. Some applications deliberately
leave open connections in place, so you will need to use your knowledge of
the apps to distinguish the legitimate connections from the orphaned ones.
- You can't "kill" it

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.