Solved

Remote MS SQL DB connection problem via ASP - Microsoft OLE DB PROVIDER FOR SQL SERVER ERROR 80004005

Posted on 2004-08-11
48
1,084 Views
Last Modified: 2008-02-01
Hi guys,

I use to run my ASP script on the same server as my MS SQL , but due to CPU resources problem i had no choice but to relocate my ASP to another server in the same datacenter

My global.asa file uses TCP/IP  to access the database , when there are only a few users everything works fine and fast , but as more and more users comes in , the site becomes very very slow ( but CPU load on script server is only less than 20% ) and i will get this SERVER ERROR 80004005.

I tried to adjust the max thread setting on my windows 2003 , but i cant seems to find it in the registry , any idea how to edit the metabase and how to solve my MS SQL 80004005 error ?

Does anyone know why and how i can solve theses issue??

Please advise and thanks in advance

regards


0
Comment
Question by:weeee
  • 22
  • 17
  • 6
  • +2
48 Comments
 
LVL 2

Expert Comment

by:KJHDI12
ID: 11773428

You use the full version of SQLServer? MSDE is limited to 20 connections or something like that.
0
 

Author Comment

by:weeee
ID: 11773528
yes i am using full license standard edition of MS SQL 2000
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11776300
what are the machine profiles for the Application
and database server
OS/Memory/Disk/etc

How much data is being returned in the result set.

if you are running a query that returns a VERY large
result set your performance will degrade VERY rapidly
as more users come online.

watch the memory consumption on the application server as well you are hitting a wall somewhere

Jay
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11776316
0
 

Author Comment

by:weeee
ID: 11777069
hi there,

my MS SQL spec are dual xeon 2.8 , 3 gig ram , running on scsci HDD ( this machine ram usage is 1.94gig )

my script server spec is p4 2.8 1 gig ram running on IDE HDD ( this machine ram usage is only 250mb )

both are win2k3

and they are hosted in the same datacenter with ping time between each less than 1ms
0
 

Author Comment

by:weeee
ID: 11777107
oh yes... i have ask my developer , he said the return query is not big set of results

the dual xeon is hookup on 100mps uplink , while the p4 is hookup on 10mps uplink. they are not on lan
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11777131
Also, what queries are hitting your database from the app server?
for each page? and how many rows does each rows & columns return?
0
 
LVL 2

Expert Comment

by:KJHDI12
ID: 11777134

10mps uplink?
0
 

Author Comment

by:weeee
ID: 11777168
i mean the ethernet speed 100meg and 10meg respectively .... both on in Theplanet DC
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11777220
do you have an AspSessionMax  set on your application server?
0
 

Author Comment

by:weeee
ID: 11777244
hi there jay ,

how do i know if i have aspsessionmax ???

i am a quite a newbie ...sorrie for my stupid question
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11777314
Check the following

Registry Path:
HKEY_LOCAL_MACHINE \SYSTEM
\CurrentControlSet
\Services
\Inetinfo
\Parameters


PoolThreadLimit REG_DWORD

Range: 0 – Unlimited

Default: 2 * # MB

This parameter specifies the maximum number of I/O worker threads that can be created in the Inetinfo process, which limits the number of simultaneous connections. IIS sets PoolThreadLimit to 2 * number of megabytes of RAM present in the machine. If this value is larger than 256, it will be clamped to 256. If a value is present in the registry, it overrides IIS's calculation. Each pool thread watches for a network request and processes it, either by sending back a static file or by passing the request to an ISAPI extension DLL (such as ASP) or to a CGI. If the ISAPI extension processes a request synchronously and it takes a long time to process requests, then it will tie up the worker thread, leaving IIS with fewer worker threads to process other requests. For this reason, well-written ISAPI extensions, such as ASP, implement their own thread pools, place requests in a queue, and process them asynchronously with their own threads, so as not to tie up IIS worker threads. In general, if you find that the default limit of 256 threads is inadequate, you probably have a poorly written ISAPI extension tying up IIS worker threads. 256 is a lot of threads to have active simultaneously and will incur significant overhead in synchronization and context switching.

PoolThreadLimit is a hard limit that includes all IIS worker threads, including the HTTP, FTP, NNTP, and SMTP services. PoolThreadLimit will always be greater than or equal to MaxPoolThreads.

The ASP thread pool is a separate set of threads. Its size is controlled by the AspProcessorThreadMax metabase setting. The largest possible number of outstanding ASP requests is the sum of AspRequestQueueMax and AspProcessorThreadMax.

MaxPoolThreads REG_DWORD

Range: 0 - Unlimited

Default: 4 per processor

This parameter specifies the number of I/O worker threads to create per processor. Each pool thread watches for a network request and processes it. The MaxPoolThreads count does not include threads that are consumed by ISAPI applications; it refers only to the number of worker threads available to process request for static files. IIS will create more threads as needed to process ISAPI requests. The total number of IIS worker threads is capped by PoolThreadLimit.

By default, only four CGI applications can run concurrently. If you run many CGI applications, you should increase this value in order to increase the throughput. You could set the UsePoolThreadForCGI value (under ..\Services\W3SVC\Parameters) to FALSE (0); however, this is somewhat dangerous because it can significantly decrease performance during high usage of CGI applications. Generally, it is not good to create more than 20 threads per processor.

0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11777342
it should be in the same place in the registry as what i just gave you.

Ummm which version of IIS are you running anyway?


Jay
0
 

Author Comment

by:weeee
ID: 11777393
hi there,

i followed your instruction to the registry , but there is no POOLTHREADLIMIT REG_DWORD

i only see :

(Default) REG_SZ
DispatchEntries REG_MULTI_SZ
ListenBackLog REG_DWORD

... pardon me , sometime back i manage to bump myself into seeing such setting in metabase , but i cant seems to remember how to get back into editing the metabase, the last time i was there i was tunining the maxthreadprocess
0
 

Author Comment

by:weeee
ID: 11777399
i am running IIS6 on windows 2003 standard edition
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11777432
sorry this info was from iis 5.0 I don't know where they have moved them in 6 yet

0
 

Author Comment

by:weeee
ID: 11777491
hi jay ,

thats the problem .. all the info i found on the net describe how to set for windows 2000 , they didnt say much about setting for IIS6 :(
0
 
LVL 34

Expert Comment

by:arbert
ID: 11777539
"MSDE is limited to 20 connections or something like that."

None of the SQL Server product line has max connections--why do people say that (MSDE does have a governor, but no max cap on connections).

Have you run SQL profiler to see if the statements make it to the database?  Have you ran profiler to see the start and end times of the statements?  You need to narrow the problem down and figure out if it is IIS or SQL server that has the biggest problem....
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11777586
Its got to be the app server
ps ..  did you look at utiliztion on the database server for each individual processor?

has it been configured to take full advantage of all the processors?

just to make sure it isn't overloading one of the processors while the other waits around

Jay
0
 

Author Comment

by:weeee
ID: 11777641
hi jay,

I think standard edition is only limited to 2 processor , it is already using both the processor , and utilization is never beyond 50% for both processor graph combine into one

>>arbert , i do not know how to run the profiler :((( not quite a developer , i dont know programming , i am helping out my developer work on these issues of "hardware" while he is working on other expansion of the site

cheers
0
 
LVL 34

Expert Comment

by:arbert
ID: 11777654
Ok, if you say "its got to be", it must be....Nobody has given any performance stats or even suggested the gathering of statistics to pinpoint the problem, so how can you say that???  Has anyone ran Windows Perfmon on any of the servers to see where the bottlenecks are?
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11777668
can you give me the EXACT error message?
the "SERVER ERROR 80004005" has several considerably differnet variants
0
 

Author Comment

by:weeee
ID: 11777705
hi jay,

the SERVER ERROR 80004005 was all i got from the user who gets this error , personally i had been unable to re-enact this senario error , but i did manage to get VERY SLOW page loading once certain number of people comes online ( i have a section in my admin scripts to see who is currently online )
0
 
LVL 34

Expert Comment

by:arbert
ID: 11777728
"the "SERVER ERROR 80004005" has several considerably differnet variants"

Including a permssions problem on the SQL Server...
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Expert Comment

by:Jay Toops
ID: 11777746
You need to construct somthing that can re-create this otherwise you will
never know if it is fixed.

I suggest a testing tool like
http://www.paessler.com/products/webstress

(fully functional test version available)

Jay
0
 
LVL 34

Expert Comment

by:arbert
ID: 11777772
Doesn't really do any good to stress test if nobody can figure out how to use Profiler and Windows Perfmon....

You really need to run these tools to figure out where and why the problem is happening.  That stress test product is great, but it doesn't look at the backend statistics...
0
 

Author Comment

by:weeee
ID: 11778054
actually guys , come to think about it , i am talking about the scenario whereby ~ 10-12 user simutanouesly access the server and the server slows down . the app server is only running one and only site , its quite hard to imagine the default settings cant handle 10-12 users ?

if i run the same scripts on the dual xeon server with the MS SQL i can get at least 40-50 users online at one shot without any slow down, the other time when i tried running script on app server and MS SQL on its own server , i get loading time 10 times of the script on the MS SQL server , in the end i manage to solve this issue by changing the global.asa connection string to use TCP/IP instead of the default name pipes and the processing time come back to only 5% slower than the script hosted on the MS SQL server.

so guys what do you think ?
0
 

Author Comment

by:weeee
ID: 11778574
hi guys manage to get the exact error :

Microsoft OLE DB Provider for SQL Server error '80004005'
Timeout expired

<<<SCRIPTNAME>>>>
0
 

Author Comment

by:weeee
ID: 11778945
guys , it seems that if i generate a report or similar requesting huge amount of data i will sure get the timeout error though i already set the ASP timeout to 500sec
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 100 total points
ID: 11778995
Ya, setting the timeout to a large number is just going to make your clients mad when they have to wait--IDENTIFY the problem...See what's being passed to the database and why it's taking so long.  If the profiler on the database shows fast response times, then it's your ASP code....
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11782493
There are other timeouts than the asp one.
The db connection one for instance.


Might there be any kind of locking mechanism going into play
are any of  your queries performing update statements?

at this point id be interested in what the sql trace is doing.
and id really closely look at the performance on the application server.

Jay
0
 

Author Comment

by:weeee
ID: 11782554
hi guys ,

can teach me the way to identify and see if there is problem with the DB ??

what do you guys think if i hook up the MS SQL server to APP server , do u think such speed problems will still exists ??
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11782732
if your ping time is 1ms it is doubtfull that this would help.
0
 

Author Comment

by:weeee
ID: 11782854
sighzz.... how man ?

i am in a fix now , what i dont understnad is why if my ping is 1ms, why should my 2 server worries about SQL when the speed is not an issue ???
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11783015
Got about a month.. im sure i could cover all the reasons by then

Jay
0
 

Author Comment

by:weeee
ID: 11783043
???jitoops i dun understand your last message
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11783060
BTW look for the iis settings in the
The Registry settings have been replaced with Metabase.xml in the %windir%\system32\inetsrv folder.

0
 

Author Comment

by:weeee
ID: 11783089
i got the xml file , but i am not sure how i can open this file

any idea jay ?
0
 

Author Comment

by:weeee
ID: 11783166
hmmm , jay i found the best meta base explorer

it is call IIS resource kit

http://www.microsoft.com/downloads/details.aspx?FamilyID=56fc92ee-a71a-4c73-b628-ade629c89499&displaylang=en

i think this is how i manage to edit the metabase the other time

so jay , what should i look out for and what should i set ?
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11783170
you can look at it with IE ... just do file->open and specifiy the path to this file

this file can be edited with notepad but be SURE to save a backup copy of the xml file beforehand.
I use GVIM (a text editor that has context highlighting)  but you won't want to do that.

Probably most any HTML editor could do a good job for you.. (xml is a close variant and is incorporated into most newer html editors)

anyone have annother idea? ...
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11783220
Here is an excellent page explaining ASP/SQL interaction and performance tips
it also explans the 5% drop in performance you are experiancing.

http://www.sql-server-performance.com/asp_sql_server.asp

Jay
0
 

Author Comment

by:weeee
ID: 11783267
thks jay

thks for your help , dont mind if u can give me your email for any further asistance i might need your enlightenment ?

drop me a mail at weeee@pacific.net.sg

regards
0
 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 11783271
>> My global.asa file uses TCP/IP  to access the database , when there are only a few users everything works fine and fast , but as more and more users comes in , the site becomes very very slow ( but CPU load on script server is only less than 20% ) and i will get this SERVER ERROR 80004005.

How many times is your global.asa script accessing SQL server? Do any of your other pages require large recordsets? Are you making sure your SQL recordsets are client side and not server side?
0
 

Author Comment

by:weeee
ID: 11783316
hi jay ,

cant seems to find any problem with the metabase

my setting for the follow is

ASPsessionTimeout = 20
Aspscripttimeout = 90
ASPprocessorThreadMax = 75
ASPrequestqueueMax = 3000
0
 

Author Comment

by:weeee
ID: 11786615
http://www.webservertalk.com/post948220.html

found this on the net , seems like i am not the only one having this issue , anyone has any concreate and solid fix to this ?

regards
0
 
LVL 34

Expert Comment

by:arbert
ID: 11786678
You're not the only one experiencing--other people have just searched and figured out how to use profiler and perfmon to diagnose and fix the problem...
0
 

Author Comment

by:weeee
ID: 11786700
:((( i already asked my developer to go and use the profiler to diagnose the problem , he said he is trying to improve stored procedure now
0
 
LVL 10

Accepted Solution

by:
Jay Toops earned 400 total points
ID: 11787193
This is more of a problem than first glance sugests.

While there are many ways this kind of problem can happen
I have found quite a number of instances where this is happening
when people have upgraded to 2003 server from 2000

this problem can be replicated by writing an application that
opens/closes many database connections.

certain of the connections will randomly fail.

Somthing is geting overloaded and causing the system to
become non-responsive.

If you look into the documentation for sp4 on windows 2000 there are a number
of these that were documented and fixed.  

I wonder if this is a case of these patches not making it into the 2003 deployment yet...

interesting eh?

Jay
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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

20 Experts available now in Live!

Get 1:1 Help Now