May 23, 2008 at 2:37 pm
Hi,
SQL Server 2005 enterprise edition, after applying Service pack 2, (9.00.3054.0) we started getting intermittent but repeatable GNE's from mdac 2.7 in VB6. They are happening in different places in the code, not always the same statements.
I can't see anything on the SQL server logs, or in the windows event viewer to give me an idea what is going on. Does anyone have any suggestions to help troubleshoot this?
TIA,
--deb
June 5, 2008 at 1:40 pm
Hello,
Wanted to let everyone know that we found the root of our GNE. After calling Microsoft, we nailed it down to many TCP/IP ports being left in a time_wait state and our process running out of usable ports to open recordsets on.
Our process was a report that was aggregating daily data up to monthly data for up to 20 past months. On SQL Server 2000, it never had a problem running, but on 2005, the speed of the report was cut in half. Since it was running much faster, we were running out of ports.
By default, tcp/ip ports will wait 240 seconds after they are closed before they die. Also by default you have 4000 available ports to work with. We added two new parameters to the TCP/IP parameters section of the registry under
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
One to bump up the ports: MaxUserPort and one to decrease the time of the TIME_WAIT state,
TcpTimedWaitDelay.
These are described here:
http://support.microsoft.com/kb/319502
We probably need to change that report to use connection pooling...but that will be for a future redesign.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply