Timeout Errors

[Silx]

Every so often, I'll get timeout errors on my site.. and they seem to be fairly random.

They'll occur when a query is being run, or purely just trying to connect to the database.

Here is an example of the error:

ASP Error occurred 6/15/2009 1:05:33 AM in Microsoft OLE DB Provider for ODBC Drivers
Error number: -2147467259 (0x80004005)
File: /inc_site_variables.asp, line 160
[Microsoft][ODBC SQL Server Driver]Timeout expired

In that example, the script is purely just trying to open a connection to the database.

I know it has nothing to do with server maintenance, as I've run into this error when browsing from my own machine at a time when I know no maintenance is occuring on the server.

Regarding open connections.. the nature of the pages are open connection, fetch data, close connection. Even if the user were to keep the page up, the connection to sql should be closed once the page has finished loading. The only connection at that point should be between the user and the web server. The connection between the web server and the sql server should be gone.

I have a ticket in to my host to check out the server settings and error logs. But I want to do all I can from my end too. Any and all suggestions are welcomed.

thanks,
-S

 

[Crowley16]

what level of access do you have to the server?

You can use activity monitor or sys.sysprocesses to try to identify if there are any blocks or waits causing these issues.

If just trying to connect to the database causes issues, then it's more likely to be a network or os issue rather than database.

Try a continuous ping test to the server while this happens...

--------------------
Procrastinate Now!

 

[Silx]

I don't have direct access, but I may be able to pull data from sys.sysprocesses.

Someone mentioned to me that this may just be an issue with microsoft's drivers, and classic asp and that it cannot be fixed because it's old technology. I'm having a hard time believing that though.

 

[gmmastros]

Can you show the connection string you are using?

Obviously, if there is a username/password, then replace that information with X's.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Silx]

Oh, absolutely. I'll post both that I've been using:

'C_STRING = "Driver={SQL Server};" & _ 
'"Server=xxxxx;" & _ 
'"Database=gb;" & _
'"Uid=xxxxx;" & _ 
'"Pwd=xxxxxx;" 

'Changed providers to try and fix timeout errors. 6/2009
C_STRING = "Provider=sqloledb;" & _ 
"Data Source=xxxxx;" & _ 
"Initial Catalog=gb;" & _
"Uid=xxxxx;" & _ 
"Pwd=xxxxxx;" 
	
set dataConn = Server.CreateObject("ADODB.Connection")
dataConn.ConnectionTimeout=0
dataConn.Open = C_STRING

Note that the connectiontimeout definition is new. I'm hoping to maybe at least get past the connection timeout to see another error from something behind it.. if that makes any sense..

 

[gmmastros]

I've never had the same type of problem you are having. I was just looking over my stuff, and here's what I have.

    Set DB = Server.CreateObject("ADODB.Connection")
    DB.CursorLocation = 3
    On Error Resume Next
    Call DB.Open("Provider=SQLNCLI.1;Persist Security Info=False;User ID=XXXX;Password=XXXX;Initial Catalog=XXXX;Data Source=XXXX;")
    On Error Goto 0

DB.CursorLocation = 3 means to use a client side cursor, where all of the data from a query is sent to the client machine at one time.

ASP Error occurred 6/15/2009 1:05:33 AM in Microsoft OLE DB Provider for [!]ODBC Drivers[/!]
Error number: -2147467259 (0x80004005)
File: /inc_site_variables.asp, line 160
[Microsoft][[!]ODBC SQL Server Driver[/!]]Timeout expired

Anyway.... what seems weird to me is.... your error messages indicate you are using ODBC, but your connection strings indicate you are using OLEDB. It's a bit confusing.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Silx]

The ODBC SQL server driver came from the first connection string, Driver={SQL Server}. My guess is, that 'driver' is ODBC?

Also, I've tried to use SQLNCLI, but the provider does not seem to be installed on my host machine. Is that 2005?

 

[gmmastros]

Is that 2005?

Yes. But.... you can use this provider to also connect to a 2000 database. But.... if you don't have the provider, then you can't use it, either.

As you can imagine, troubleshooting these type of issues can be a real problem. I suggest....

1. Try putting the CursorLocation in your code to see if it resolves the issue.

2. Try changing your connection string so that instead of using a computer name for the data source, use an IP Address instead. Ex: change Data Source=ComputerName o Data Source=1.2.3.4

I certainly do not guarantee that either of these will fix the problem, but it is what I would try next. Hope it helps.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom