Solved

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

Posted on 2004-09-07
18
884 Views
Last Modified: 2012-06-27
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

Debugging:
- 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!

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

DATABASE SCRIPT
=========
CREATE TABLE [dbo].[CauseTimeout] (
      [IdentityId] [int] IDENTITY (1, 1) NOT NULL ,
      [OtherId] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CauseTimeout] WITH NOCHECK ADD
      CONSTRAINT [PK_RISK_Value] PRIMARY KEY  CLUSTERED
      (
            [IdentityId]
      )  ON [PRIMARY]
GO
ASP CODE
=========
<%@ LANGUAGE="VBSCRIPT" %>
<%option explicit%>

<%

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

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

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

%>
<HTML>
<BODY>
<%
      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)
            rsTemp2.Close

            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
                        response.end
                  end if
            end if

            rsTemp.MoveNext
      wend

      Call WriteStatus("done")


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

%>
</BODY>
</HTML>

0
Comment
Question by:chudles
  • 6
  • 4
  • 3
  • +2
18 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 100 total points
Comment Utility
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?
0
 

Author Comment

by:chudles
Comment Utility
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.
0
 
LVL 10

Expert Comment

by:AustinSeven
Comment Utility
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.

AustinSeven
0
 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 100 total points
Comment Utility
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.
0
 

Author Comment

by:chudles
Comment Utility
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.

Hmmm.
0
 

Author Comment

by:chudles
Comment Utility
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.
0
 
LVL 10

Expert Comment

by:AustinSeven
Comment Utility
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.

AustinSeven
0
 

Author Comment

by:chudles
Comment Utility
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.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 10

Accepted Solution

by:
AustinSeven earned 300 total points
Comment Utility
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.

AustinSeven
0
 

Author Comment

by:chudles
Comment Utility
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.
0
 

Author Comment

by:chudles
Comment Utility
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.
0
 
LVL 5

Expert Comment

by:MichaelSFuller
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
" 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....
0
 
LVL 5

Expert Comment

by:MichaelSFuller
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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....
0
 
LVL 5

Expert Comment

by:MichaelSFuller
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Pretty sure most people have seen this, but it's a pretty good power point on troubleshooting network problems:

http://www.perfect-computing.com/network.ppt

Brett
0

Featured Post

IT, Stop Being Called Into Every Meeting

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!

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 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

11 Experts available now in Live!

Get 1:1 Help Now