Intermittent / Under stress "SQL Server does not exist or access denied."

We recently moved from having SQL Server and the Web Server from the one machine onto two.  Now under certain conditions we get "SQL Server does not exist or access denied." in the middle of processing.  I have seen this error referenced on this website, as well as many others, but an unable to find a solution that works for us.  I have pared everything back, and can duplicate the problem at will against two different db servers.  I:
- installed SQL Server 2000 on a second Windows 2003 server
- created the database
- created the table below
- populated some data into the table (each time you run the asp code in creates more rows)
- changed SQL Server to only listen on TCP/IP
- ran the asp.  It consistently crashes between 1000 and 1300 iterations

- I have run Profiler and immediately before the error a line "Attention" appears but with no (apparently) useful information
- Looking at SQL Server error log, there doesn't appear to be anything useful, except possilbly "Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'xp_readerrorlog'."

Let me know if you need anything else!

ASP Server: Windows 2003 IIS6
DB Server: Windows 2003, SQL Sever 2000, Service Pack 3a

CREATE TABLE [dbo].[CauseTimeout] (
      [IdentityId] [int] IDENTITY (1, 1) NOT NULL ,
      [OtherId] [int] NULL

      )  ON [PRIMARY]
<%option explicit%>


sub WriteStatus(asValue)
      response.write("<b>" & asValue & "</b><BR>")
end sub

dim sSQL
dim rsTemp
dim rsTemp2
dim iValueCount
dim lTmp
dim db1
dim miSQLCount
dim moTimer

      set db1 = server.CreateObject("ADODB.Connection")
    "PROVIDER=SQLOLEDB;DATA SOURCE=(our db);UID=chadTest;PWD=chadTest;DATABASE=chadTest2"

      Call WriteStatus("Version 7")

      Server.ScriptTimeout = 18000

      sSQL = "Select IdentityId From CauseTimeout Order by IdentityId"
      set rsTemp = db1.Execute(sSQL)
      while not rsTemp.EOF
            iValueCount = iValueCount + 1

            sSQL = "Insert Into CauseTimeout (OtherId) values (2)"
            Call ExecSQL(sSQL, "")

            sSQL = "Select Max(IdentityId) From CauseTimeout"
            Call ExecSQL(sSQL, rsTemp2)

            lTmp = rsTemp2(0)

            sSQL = "Update CauseTimeout Set OtherId = 3 Where IdentityId = " & lTmp
            Call ExecSQL(sSQL, "")

            if iValueCount Mod 100 = 0 Then
                  Call WriteStatus(iValueCount & " values updated")

                  if not(response.IsClientConnected) Then
                  end if
            end if


      Call WriteStatus("done")

response.write("<BR Clear='All'>")


Who is Participating?
AustinSevenConnect With a Mentor Commented:
It's good that you've isolted the problem.    Maybe you need to further isolate the problem on the faulty apps server by examining Windows h/w and s/w config plus app s/w installation/config.   Compare with correctly functioning apps server config.  If you spot a differences, change one thing at a time and test.

arbertConnect With a Mentor Commented:
Have you looked at Windows Perfmon to see where the weak points are?  I'd look at disk IO, Memory Usage, Paging, and, if a multiple proc box, context switching.

We've found when most of these errors happen it's because of DiskIO and queuing.  But, it really depends on what's going on with your server too.

Also, look at the memory usage of IIS.  Are you close and setting all objects to nothing when you're done with them?
chudlesAuthor Commented:
I had looked at Performance Monitor, and have just looked a lot more, a can't see anything that looks particularly bad, although I'm not very experienced in using the tool.

- With disk I/O, I looked at Avg Disk Queue Length.  On the DB Server there is activity, it sits at about 40 - 60 in the graph (which means about .4 to .6 as it is multiplied by 100).  This drops straight to 0 just before I get the error on the app server.  On the app server there is very little activity

- With memory, I looked at total usage, pages / sec, and some others.  I couldn't see any measure that had significant usage on either server.

- Both machines have 2 processors, however I don't know how to see context switches.

- IIS is using quite a lot of memory (about 200mb).  However, this is our production server and other stuff is going on.  (The DB Server I'm testing against is not our production db server.  I'm the only one connecting to this db server).  Generally, we don't set recordsets and connections to nothing, but I am in the page I'm using to cause the error.

Some more information:
After I get the error, there seems to be a gap of about 20s before I can re-establish a connection to the db server.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

One thing to do is run the same SQL you current run via the ASP page directly via Query Analyzer directly on the db server and also on a different server that has SQL Server client tools on it.   In this case, you would establish if the problem only occurs via the 'app' - ie. IIS/ASP.   You have already suggested that the problem didn't occur when IIS was on the DB server.    It's a good thing to move ISS off of course but the fact that you didn't have the problem before seems to indicate that the problem might not be directly to do with SQL Server.   I am not offering a solution but a way to isolate the problem domain.   Once you've done that, you can start to identify the actual problem.

mastooConnect With a Mentor Commented:
You might also look at SQLServer General Statistics - User Connections in perfmon.  We were getting the same error due to leaking connections - Sql Server would stop answering after several thousand leaks.
chudlesAuthor Commented:
Looking at the two suggestions above, I reran the asp a number of times.  Whilst it is processing the number of user connections fluctuates between 2 and 4.  (I am the only person connecting to this db server and I have a Query Analyzer window taking a connection).  When the asp crashes crashes, there is about 20s before you can rerun the asp and it can connect.  However, during this time, I can switch to Query Analyzer and manually do the same inserts / updates that the asp was doing.

chudlesAuthor Commented:
Some more information:
We have three servers
Windows 2003 with IIS6 and SQL Server 2000 sp3a
Windows 2003 with IIS6
WIndows 2000 with SQL Server 2000 sp3a

I can cause the error by running the asp on either of the asp servers connecting to a database on another server.
This reminds me of some COM/DCOM issues I've seen where an app component works well if it's on the same box as SQL Server but like a dog if it's on a different server.   I can't quite remember what the fix was then and, in any case, versions have moved on since I saw those problem.   MDAC configuration might be worth looking into.   However, from what you've said, the problem probably isn't with SQL Server.    Might be worth re-posting in an IIS/ASP type forum with the extra info you've collected.

chudlesAuthor Commented:
I now think the problem is on the App Server, and not the DB Server.  So as such, it probably isn't a SQL Server problem.

I wrote an additional asp that just connected to the db, and read the number of records from the table, and used two Web Servers.  I ran the code that causes the timeout on App Server 1.  During the processing, I ran the second asp on both App Server 1 and 2.  When the timeout occurred on App Server 1 the asp that counted the records also timed out on App Server 1, but it continued running without a problem on App Server 2.  I did this 3 times and got consistent results.

I guess I'll leave this open in case someone has some ideas soon.  As this is my first posting, I'm not sure of the ettiquette with what to do with the points if I get no answer.
chudlesAuthor Commented:
What I wrote was probably confusing.  I can get both app servers to fail.  However, when I get either of the app servers to fail, the other can connect to the db without a problem.  I'm about to test with a third app server running Windows 2000, in case it has something to do with Windows 2003, or something common to the setup of each of the current app servers.
chudlesAuthor Commented:
In the end we have had to roll back our app servers to Windows 2000.  Once we did this the problem went away.  Despite lots of effort we were unable to isolate what was causing the problem in 2003.
I just had the same issue today Windows 2003 Server IIS6.0, SQL 2000 SP3a. They were occasionally having connection issues very similar. It was set listen to only on TCP/IP. I was fiddling around in registry and noticed something for the connection, it was missing the port, because it was set to determine dynamically. I changed it to listen to 1433 just thinking that maybe it could be related to another issue I seen where it did not dynamically determone the port correctly. Well soon as I set it all has cleared up.
" it could be related to another issue I seen where it did not dynamically determone the port correctly. Well soon as I set it all has cleared up. "

Could be, but I don't think something working for a day necessarily means that was the cause.  We were having "general network errors" for the longest time and switched to named pipes--all worked well for a week and then the gremlin was back....
I'll tell you by tommorrow if it holds.   I'm beating Microsoft again included some extra featues included with Service Pack 3, 3a, and MDAC 2.8, specifically when it deals with dynamically determining the port number.
Remember, they don't include "extra features" in there service packs any more--only fixes :)

I think microsoft's whole network interaction with TDS needs to be redone.  Granted, I would still rather troubleshoot network problems with SQL Server than having to load a whole Oracle client just to attach to a database.

SQL Server has just always seemed proned to network instability.  2005 is nice, but not much has been done in this area....
I second that, most of my replication problems have been related to MDAC bugs. They really need to fix, test, test, test, and then release something alot more stable.
Pretty sure most people have seen this, but it's a pretty good power point on troubleshooting network problems:

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.