September 15, 2004 at 9:53 am
We have been experiencing connection timeout errors when trying to connect to our production sql server machine. These errors a hit and miss. Meaning when we try to connect a second time we get connected successfully. Now I'd like to blame this connectivity issue on the network, but of course first I need to rule out a problem with the SQL Server machine. Now comes my question. What should I, and how should I be looking for indicators on the SQL machine that would cause connection time out errors? Should I be reviewing performance monitor? If so what counters are important, and what numbers would indicate a problem.
Any help in ruling out SQL Server would be greatly appreciated.
Gregory A. Larsen, MVP
September 15, 2004 at 10:52 am
Below is a script which you can schedule locally from within SQL Server and see if you run into any issues when running locally also. I ran this script every 1 minute.
dim cn
set fs = createobject("Scripting.filesystemobject")
set output = fs.opentextfile("connection.txt",8,1)
on error resume next
set cn = createobject("ADODB.CONNECTION")
cn.ConnectionTimeout = 5
cn.commandtimeout = 5
cn.open "Provider=SQLOLEDB;Server=Server_Name;uid=uid;pwd=pwd;"
if err.number <> 0 then
output.writeline (date & " " & TIME & VBTAB & VBTAB & Err.description)
else
output.writeline(date & " " & time & VBTAB & VBTAB & "Success")
END IF
'wscript.echo "connection successful"
dim rs
set rs = createobject("adodb.recordset")
rs.open "select count(*) from syslogins",cn
if err.number <> 0 then
output.writeline (date & " " & TIME & VBTAB & VBTAB & Err.description)
else
output.writeline(date & " " & time & VBTAB & VBTAB & "Success")
END IF
rs.close
cn.close
set cn = nothing
set rs = nothing
I know this is not one an easy one but some of the other places i try to look apart from SQL Server are
1) BIOS/Firmware Versions
2) NIC Drivers
3) Check with your network team if they can see any errors being generated from the port on the switch. In our shop, they generally clear up all the counters and monitor them for a day to see if they see any new errors.
Good Luck
September 15, 2004 at 1:21 pm
It could well be a network bottleneck especially if the behavior is inconsistent as you stated. Are there other instances of SQL Server that you connect to and if so, do they connect without problems? If so something definitely is up with that box. What about connecting during off-peak hours when there's little or no network activity?
One thing worth looking at...we were troubleshooting what we thought was an application performance issue with a vendor for a couple of weeks before I (admitedly somewhat sheepishly) finally discovered that the NIC had been logging Receive Underruns and CRC errors for some time. Replacing the NIC in this instance did the trick. Hope this helps.
My hovercraft is full of eels.
September 16, 2004 at 11:35 am
Hi,
It looks that we face the same problem, check the WINS server configuration at your work stations. You probably must declare the WINS Server ip address directly in the TC/IP configuration of your nick card.
September 16, 2004 at 2:06 pm
Thanks for the tips. It doesn't appear to be the nic or at least I don't see any event log messages. It does appear to be a single machine, so I'm guessing it is not a network issue. Also since multiple people are complaining, it doesn't make since the everyones WINS configuration on the work station is incorrect. Also it is very intermitten. I like the script that tries to log on every minute or so.
Gregory A. Larsen, MVP
September 16, 2004 at 2:50 pm
Dang...just lost my whole post.
Anyway what I wanted to say is that NIC errors usually won't show up in the event log unless the connection drops entirely. If you have an admin account, you can check NIC stats on the server from Control Panel. Excessive overruns, underruns and especially errors are indicative that a replacement is in order. Also you can ask your network guys to analyze traffic to/from the server to see if they see anything suspect. If it's not the NIC, it still could be something simple like a fried port or patch cord.
Another thought. Are your client connections using server aliases? We had a poorly behaved app who's performance was vastly improved by opening Client Config, deleting the alias names and making sure that TCP/IP was enabled. Something to try anyway. Hope this helps.
My hovercraft is full of eels.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply