| Author |
Topic |
|
SLKRR
Starting Member
10 Posts |
 Posted - 2006-05-17 : 15:28:03
|
| For the past couple of days, we have been receiving the "timeout expired" message intermittently when connecting to our SQL Server database. This occurs using ODBC and when connecting with Enterprise Manager, even when running EM on the server box itself. When it occurs, occasionally one can keep trying and successfully connect, but at other times the system has to be restarted/rebooted. After a reboot, the problem may go away for a few minutes, but very quickly reappears. The message occurs both at login and when users are running an insert or update statement.I have seen various people post here with a similar situation as this, but none of the solutions posted for them have yet worked for us.-The database has been running well for over a year, and no changes were made to the system before this problem began to show up-Network connectivity is fine, and overall server traffic is quite low-There is plenty of memory on the server-Queries returning errors are simple and short-Raising the timeout limit has no effect (it just takes longer to get the error message back)-There is no firewall on the serverAny thoughts on this one?Simeon |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-17 : 15:42:43
|
| Are you using a port different from 1433? Or you using the ServerName/Instance or an IP address to connect? Does a ping from the client to the database server respond fast?Tara Kizeraka tduggan |
 |
|
|
SLKRR
Starting Member
10 Posts |
Posted - 2006-05-17 : 15:55:04
|
| Port is 1433. Connection using servername or IP has the same problem. Ping is fast - only SQL Server is slow. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-17 : 16:02:42
|
| Try connecting with ServerName,1433. Same problem?Tara Kizeraka tduggan |
|
|
|
SLKRR
Starting Member
10 Posts |
Posted - 2006-05-17 : 16:21:01
|
| How do I specify the port number explicitly when connecting with Query Analyzer or Enterprise Manager?When it connects (or commits data) it is almost instantaneous. If it delays at all, then it ends up failing after the timeout expires. Here is what comes back from ODBC Administrator when I attempt to test the connection (sorry, some of it's in Portuguese):Falha de conexão:SQLState: '01000'Erro do SQL Server: 10060[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (PreLoginHandshake()).Falha de conexão:SQLState: '08001'Erro do SQL Server: 11[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Erro geral de rede. Verifique a documentação da rede.Simeon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-17 : 16:27:51
|
| You just type in ServerName,PortNumber in Query Analyzer or Enterprise Manager. If this method works, then I can help you add an alias so that you don't need to do it this way.But I'm guessing that this won't work and that the problem is outside of the SQL Server.Do you have access to a network sniffer? If so, that would help you troubleshoot this.When it fails from Query Analyzer or Enterprise Manager, what error are you getting?Tara Kizeraka tduggan |
|
|
|
SLKRR
Starting Member
10 Posts |
Posted - 2006-05-17 : 16:48:17
|
| Same error with port number specified. Here is what is received from Query Analyzer. The error message is not always the same each time, however, I have noticed. Sometimes it returns the PreLoginHandshake message, other times just this:Unable to connect to server \\CELLPLAN3,1433:ODBC: Msg 0, Level 16, State 1[Microsoft][ODBC SQL Server Driver]Timeout expiredEDIT: I will look at what network sniffing resources we have available here.Simeon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-17 : 16:51:46
|
| Since Query Analyzer and Enterprise Manager return "Timeout expired", I'd bet that this problem exists outside of the database server. You aren't even making it to the database server when you get this error. So the problem is somewhere beteen the client and the database server but not on the database server.Tara Kizeraka tduggan |
|
|
|
SLKRR
Starting Member
10 Posts |
Posted - 2006-05-17 : 16:58:02
|
| If the problem is outside of the server, any idea why it would occur when running Enterprise Manager on the server itself? It's got us stumped here. Also, when running the SQL Server Profiler, it shows a connection being made (on the server side) and then immediately disconnected. After this, the timeout message appears on the client.Simeon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-17 : 17:12:37
|
| When running on the server, it actually does go out to the network and back in as it has to resolve the ServerName. It might not do this if you specify (local) although I'm not sure.I'm surprised that SQL Profiler is showing a connection. Are the users connecting with SQL or Windows Authentication? If Windows Authentication, then the problem might be with the database server checking the credentials with the domain. I would suggest trying SQL authentication to verify this.Tara Kizeraka tduggan |
|
|
|
SLKRR
Starting Member
10 Posts |
Posted - 2006-05-17 : 17:40:05
|
| We are already using SQL authentication to log in. I am going to see what we can do to track the network traffic. Would it be helpful to get the latest SP installed?Simeon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-17 : 17:44:39
|
| What service pack are you running? We are running sp3a plus security patch 818 and have not had this problem. If you are pre-sp3a, then I'd say definitely yes. If you are at sp3a with 818, then I'd say maybe. Do you have a support contract with Microsoft? If so, they might be able to help you fix this problem faster as it'll probably take in depth analysis to figure this out.Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-18 : 01:28:45
|
| Is your server open to the world (you said no firewall)? If so could port scanners be trying to login and slowing it down? (Turning on logging for failed logins would show you periodic high levels of failed logins for accounts like SA, ADMIN and so on in the SQL Log)Kristen |
|
|
|
SLKRR
Starting Member
10 Posts |
Posted - 2006-05-18 : 09:39:14
|
| We are running version 7.00.623, which means we don't have any service packs installed at all. I will install SP4 and let you know if that corrects the problem.Simeon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-18 : 12:25:37
|
| SQL Server 7.0 with no service packs! Shame! There was a huge memory leak in sp1 or sp2 (can't remember exactly, it's been over 4 years since I used 7.0). sp4 probably includes a bunch of performance improvements as well.Tara Kizeraka tduggan |
|
|
|
SLKRR
Starting Member
10 Posts |
Posted - 2006-05-18 : 20:03:18
|
| We installed SP4, but the problem did not go away. We then added SP1 to Microsoft Windows Server 2003, and are encountering a different problem:SQLState: '42000'Erro do SQL Server: 18460[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed. The maximum simultaneous user count of 1 licenses for this 'Standard Edition' server has been exceeded. Additional licenses should be obtained and installed or you should upgrade to a full version.This only happened after the upgrade to Windows. --arrrgh-- |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-18 : 20:05:44
|
| How many Standard Edition licenses did you purchase? How many did you tell the initial SQL Server installation that you had?BTW, SQL Server 7.0 without any service packs isn't supported even on Windows 2000, so I doubt that it was supported on Win2k3. sp4 was the service pack to be supported on Win2k. You might want to check the requirements of 7.0 to see if you environment is supported.Tara Kizeraka tduggan |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-19 : 05:01:39
|
| If I remember, we used to get the preloginhandshake problem because of a dodgy port on a switch. See if a direct connection works.-------Moo. :) |
|
|
|
SLKRR
Starting Member
10 Posts |
Posted - 2006-05-19 : 10:02:55
|
| License problem fixed, first problem remains. We are trying some other things today to attempt to resolve this from the network side. |
|
|
|
SLKRR
Starting Member
10 Posts |
Posted - 2006-05-22 : 12:26:22
|
| Just to let you know, we finally solved this problem. We discovered that large queries were getting stuck in wait state "EXCHANGE" and that after this happened, anyone else trying to log in would get the PreLoginHandshake error message. We reset the max degree of parallelism to 1 and now the queries are running through fine, although a bit slower than they had in the past. Apparently this is a known issue with 7.0. We are planning now to upgrade to SQL Server 2005 to avoid this problem in the future. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-22 : 12:50:20
|
| I remember that issue in SQL 7 now you mention it. We could predict particular User IDs that would report the problem when they tried to register on our web site!!Kristen |
|
|
|
Next Page
|
Timeout expired - PreLoginHandshake