ASPX pages failing when SQL Server busy

[John]

I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET
webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.

Everything works fine, but sometimes when I execute a particular stored
procedure (on server console using command line, nothing .net), my webapps
will fail intermitently. Custom errors are turned on, so at this stage I
can't give you error codes! But my assumption is that it's a SQL problem,
not a .NET one.

The sproc does lots of joining, aggrigates and sorting, and makes the tempdb
grow from nothing to over 3 gigs. The database it's actually in is around
300mb in size, and doesn't grow that much itself. All db's are in 'simple'
recovery model. The sproc contains no explicit transactions or temp tables.

Can anyone suggest what's going wrong?? I suspect tempdb, if only because of
it's massive growth. Could it be getting too big for the filesystem??? (I
have lots of disc space free, but maybe W2k can't cope with a single file
larger than x?)

Thanks,

John

[Mark]

Modify the web.config file to allow for asp.net errors to when accessed from
the local web server, but not the clients. Then log into the local web
server and try our your application. Something like:

<customErrors mode="RemoteOnly" /> or similar.

Too many factors involved below to NOT find out what the exact message is.

Also - make the tempdb a static size at 3 GB. Don't let it autogrow. That
will kill performance in a hurry. Also, your connection string should
include a time out setting that is higher than normal.

Good luck.

Mark
www.dovetaildatabases.com
"John" <js************@ecclesdeletethiscollege.ac.uk> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.

Everything works fine, but sometimes when I execute a particular stored
procedure (on server console using command line, nothing .net), my webapps
will fail intermitently. Custom errors are turned on, so at this stage I
can't give you error codes! But my assumption is that it's a SQL problem,
not a .NET one.

The sproc does lots of joining, aggrigates and sorting, and makes the tempdb grow from nothing to over 3 gigs. The database it's actually in is around
300mb in size, and doesn't grow that much itself. All db's are in 'simple'
recovery model. The sproc contains no explicit transactions or temp tables.
Can anyone suggest what's going wrong?? I suspect tempdb, if only because of it's massive growth. Could it be getting too big for the filesystem??? (I
have lots of disc space free, but maybe W2k can't cope with a single file
larger than x?)

Thanks,

John

[Miha Markic [MVP C#]]

John,

You might start by catching the errors and logging them to see where the
problem is.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"John" <js************@ecclesdeletethiscollege.ac.uk> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

I have a single server running SQL Server 2000 sp3a, and acting as a
ASP.NET webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.

Everything works fine, but sometimes when I execute a particular stored
procedure (on server console using command line, nothing .net), my webapps
will fail intermitently. Custom errors are turned on, so at this stage I
can't give you error codes! But my assumption is that it's a SQL problem,
not a .NET one.

The sproc does lots of joining, aggrigates and sorting, and makes the
tempdb grow from nothing to over 3 gigs. The database it's actually in is
around 300mb in size, and doesn't grow that much itself. All db's are in
'simple' recovery model. The sproc contains no explicit transactions or
temp tables.

Can anyone suggest what's going wrong?? I suspect tempdb, if only because
of it's massive growth. Could it be getting too big for the filesystem???
(I have lots of disc space free, but maybe W2k can't cope with a single
file larger than x?)

Thanks,

John

[http://www.visual-basic-data-mining.net/forum]

The stored procedure may be poorly designed. Analyze the execution plan of
the stored procedure to look for joins and statements that you can further
optimize

--
data mining and .net team
http://www.visual-basic-data-mining.net/forum
"John" <js************@ecclesdeletethiscollege.ac.uk> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.

Everything works fine, but sometimes when I execute a particular stored
procedure (on server console using command line, nothing .net), my webapps
will fail intermitently. Custom errors are turned on, so at this stage I
can't give you error codes! But my assumption is that it's a SQL problem,
not a .NET one.

The sproc does lots of joining, aggrigates and sorting, and makes the tempdb grow from nothing to over 3 gigs. The database it's actually in is around
300mb in size, and doesn't grow that much itself. All db's are in 'simple'
recovery model. The sproc contains no explicit transactions or temp tables.
Can anyone suggest what's going wrong?? I suspect tempdb, if only because of it's massive growth. Could it be getting too big for the filesystem??? (I
have lots of disc space free, but maybe W2k can't cope with a single file
larger than x?)

Thanks,

John

[John]

The error from the aspx pages is:

"Error: System.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the operation or the
server is not responding."

The timeout is the default 30 seconds.

This occured on multiple apps, using unrelated databases (but all the
same server, and same instance). They all seemed to exhibit timeout
behaviour, they all stopped responing for 30 seconds +, even though
some of the apps were using different db's so nothing could be locked
(and the big sproc that's running isn't using any explicit
transactions). The problems seem to appear just before the sproc
finished (after about 20 minutes of running).

Help!!!!
John
"Mark" <fi******@idonotlikejunkmail.umn.edu> wrote in message news:<uj**************@TK2MSFTNGP14.phx.gbl>...

Modify the web.config file to allow for asp.net errors to when accessed from
the local web server, but not the clients. Then log into the local web
server and try our your application. Something like:

<customErrors mode="RemoteOnly" /> or similar.

Too many factors involved below to NOT find out what the exact message is.

Also - make the tempdb a static size at 3 GB. Don't let it autogrow. That
will kill performance in a hurry. Also, your connection string should
include a time out setting that is higher than normal.

Good luck.

Mark
www.dovetaildatabases.com
"John" <js************@ecclesdeletethiscollege.ac.uk> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

I have a single server running SQL Server 2000 sp3a, and acting as a

ASP.NET

webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.

Everything works fine, but sometimes when I execute a particular stored
procedure (on server console using command line, nothing .net), my webapps
will fail intermitently. Custom errors are turned on, so at this stage I
can't give you error codes! But my assumption is that it's a SQL problem,
not a .NET one.

The sproc does lots of joining, aggrigates and sorting, and makes the

tempdb

grow from nothing to over 3 gigs. The database it's actually in is around
300mb in size, and doesn't grow that much itself. All db's are in 'simple'
recovery model. The sproc contains no explicit transactions or temp

tables.

Can anyone suggest what's going wrong?? I suspect tempdb, if only because

of

it's massive growth. Could it be getting too big for the filesystem??? (I
have lots of disc space free, but maybe W2k can't cope with a single file
larger than x?)

Thanks,

John

[John]

The error i'm getting is a timeout:

"Error: System.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the operation or the
server is not responding."

The timeout is the default 30 seconds. This occured on multiple apps, using
unrelated databases (but all the
same server, and same instance). They all seemed to exhibit timeout
behaviour, they all stopped responing for 30 seconds +, even though some of
the apps were using different db's so nothing could be locked
(and the big sproc that's running isn't using any explicit transactions).
The problems seem to appear just before the sproc
finished (after about 20 minutes of running).

How can I fix it!?????!!

John

"http://www.visual-basic-data-mining.net/forum" <si******@gmail.com> wrote
in message news:%2******************@tk2msftngp13.phx.gbl...

The stored procedure may be poorly designed. Analyze the execution plan of
the stored procedure to look for joins and statements that you can further
optimize

--
data mining and .net team
http://www.visual-basic-data-mining.net/forum
"John" <js************@ecclesdeletethiscollege.ac.uk> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

I have a single server running SQL Server 2000 sp3a, and acting as a

ASP.NET

webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.

Everything works fine, but sometimes when I execute a particular stored
procedure (on server console using command line, nothing .net), my
webapps
will fail intermitently. Custom errors are turned on, so at this stage I
can't give you error codes! But my assumption is that it's a SQL problem,
not a .NET one.

The sproc does lots of joining, aggrigates and sorting, and makes the

tempdb

grow from nothing to over 3 gigs. The database it's actually in is around
300mb in size, and doesn't grow that much itself. All db's are in
'simple'
recovery model. The sproc contains no explicit transactions or temp

tables.

[John]

Is it possible that, following a massive update on one production database
(several hundred thousand rows being deleted / inserted), that locks in
tempdb could escalate to table locks?? Would that cause the problems I'm
seeing? (ie the whole instance of SQL Server basically going single-task for
about 2 minutes)

If so, how can I avoid this escalation in tempdb?

Thanks,

John

"John" <js************@ecclesdeletethiscollege.ac.uk> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

The error i'm getting is a timeout:

"Error: System.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the operation or the
server is not responding."

The timeout is the default 30 seconds. This occured on multiple apps,
using unrelated databases (but all the
same server, and same instance). They all seemed to exhibit timeout
behaviour, they all stopped responing for 30 seconds +, even though some
of the apps were using different db's so nothing could be locked
(and the big sproc that's running isn't using any explicit transactions).
The problems seem to appear just before the sproc
finished (after about 20 minutes of running).

How can I fix it!?????!!

John

"http://www.visual-basic-data-mining.net/forum" <si******@gmail.com> wrote
in message news:%2******************@tk2msftngp13.phx.gbl...

The stored procedure may be poorly designed. Analyze the execution plan
of
the stored procedure to look for joins and statements that you can
further
optimize

--
data mining and .net team
http://www.visual-basic-data-mining.net/forum
"John" <js************@ecclesdeletethiscollege.ac.uk> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

I have a single server running SQL Server 2000 sp3a, and acting as a

ASP.NET

webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.

Everything works fine, but sometimes when I execute a particular stored
procedure (on server console using command line, nothing .net), my
webapps
will fail intermitently. Custom errors are turned on, so at this stage I
can't give you error codes! But my assumption is that it's a SQL
problem,
not a .NET one.

The sproc does lots of joining, aggrigates and sorting, and makes the

tempdb

grow from nothing to over 3 gigs. The database it's actually in is
around
300mb in size, and doesn't grow that much itself. All db's are in
'simple'
recovery model. The sproc contains no explicit transactions or temp

tables.

[Luke]

First thing, I would't run the web server on the same machine as SQLServer.
It will kill the consistency of your site's performance.

"John" wrote:

I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET
webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.

Everything works fine, but sometimes when I execute a particular stored
procedure (on server console using command line, nothing .net), my webapps
will fail intermitently. Custom errors are turned on, so at this stage I
can't give you error codes! But my assumption is that it's a SQL problem,
not a .NET one.

The sproc does lots of joining, aggrigates and sorting, and makes the tempdb
grow from nothing to over 3 gigs. The database it's actually in is around
300mb in size, and doesn't grow that much itself. All db's are in 'simple'
recovery model. The sproc contains no explicit transactions or temp tables.

Can anyone suggest what's going wrong?? I suspect tempdb, if only because of
it's massive growth. Could it be getting too big for the filesystem??? (I
have lots of disc space free, but maybe W2k can't cope with a single file
larger than x?)

Thanks,

John

[William \(Bill\) Vaughn]

Sure, TempDb is used quite a bit as a "scratchpad" to hold temporary
(intermediate) products and to sort the data. What kind of hard drives do
you have? Is the database segmented so it's not putting TempDb on the same
spindle as the OS and the other databases. Sure, you might be disk bound. If
you're sharing space with IIS, that could also hurt performance as could the
complexity of your SP. It could be that the query plan does not match the
set of parameters you're using. It could be that your connection pool might
be filling up as inbound customers can't get a connection because the ones
that are in use are still busy. It could be a dozen dozen other things as
well.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Luke" <Lu**@discussions.microsoft.com> wrote in message
news:7E**********************************@microsof t.com...

First thing, I would't run the web server on the same machine as
SQLServer.
It will kill the consistency of your site's performance.

"John" wrote:

I have a single server running SQL Server 2000 sp3a, and acting as a
ASP.NET
webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.

Everything works fine, but sometimes when I execute a particular stored
procedure (on server console using command line, nothing .net), my
webapps
will fail intermitently. Custom errors are turned on, so at this stage I
can't give you error codes! But my assumption is that it's a SQL problem,
not a .NET one.

The sproc does lots of joining, aggrigates and sorting, and makes the
tempdb
grow from nothing to over 3 gigs. The database it's actually in is around
300mb in size, and doesn't grow that much itself. All db's are in
'simple'
recovery model. The sproc contains no explicit transactions or temp
tables.

Can anyone suggest what's going wrong?? I suspect tempdb, if only because
of
it's massive growth. Could it be getting too big for the filesystem??? (I
have lots of disc space free, but maybe W2k can't cope with a single file
larger than x?)

Thanks,

John

[Jeff Dillon]

Sounds like your sql is bad, and possibly forgot a join, causing a cartesian
product. Break apart the code and test, perhaps with SQL Profiler

Jeff
"Luke" <Lu**@discussions.microsoft.com> wrote in message
news:7E**********************************@microsof t.com...

First thing, I would't run the web server on the same machine as SQLServer. It will kill the consistency of your site's performance.

"John" wrote:

I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.

Everything works fine, but sometimes when I execute a particular stored
procedure (on server console using command line, nothing .net), my webapps will fail intermitently. Custom errors are turned on, so at this stage I
can't give you error codes! But my assumption is that it's a SQL problem, not a .NET one.

The sproc does lots of joining, aggrigates and sorting, and makes the tempdb grow from nothing to over 3 gigs. The database it's actually in is around 300mb in size, and doesn't grow that much itself. All db's are in 'simple' recovery model. The sproc contains no explicit transactions or temp tables.
Can anyone suggest what's going wrong?? I suspect tempdb, if only because of it's massive growth. Could it be getting too big for the filesystem??? (I have lots of disc space free, but maybe W2k can't cope with a single file larger than x?)

Thanks,

John