October 5, 2010 at 7:01 am
Hi All,
I m using SQL server 2005 Enterprise edition in our environment.Currently I m getting connectivity issue while executing SQL query from client machine(SSMS). The error is,
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Any idea why are we getting TCP error? I searched in many forums and microsoft website however i couldn't get the proper workaround for this issue.
Can anyone help me?
October 5, 2010 at 7:05 am
Are you getting this error every time when you connect from your client machine or only some times?
If you are getting this error some times most likely it is a issue with your network.
"Keep Trying"
October 5, 2010 at 7:09 am
All users in our network getting this error while executing the query at first time.
Each and every time we are getting this error at the first attempt of query execution. Next time executing the same query works fine.
October 5, 2010 at 9:42 am
Often, it's the database being offline/closed or the SQL Instance being restarted/offline
Check it.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 5, 2010 at 9:53 am
No. Database is not restarted/offline. we are getting this error every 15mins once if the connection was idle. I have tried so many server parameters and database settings but nothing woks fine.
October 5, 2010 at 10:00 am
@ prabuk
Check in SSMS, server, properties, connections, wat is the remote query timeout
?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 5, 2010 at 10:04 am
600 seconds
October 5, 2010 at 10:43 am
there might be some network issues then.
try pinging the sql server from the cmd prompt
telnet servername 1433
If there is blank screen and nothing happens, that means connection is fine , otherwise
theres some netwrok problem
ask your network administrator to check the settings of DNS ,firewalls etc.
aslo check the sql server configuration manager -- network configuration, prtocols are enabled or not?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 6, 2010 at 4:18 am
Is the "AutoClose" option enabled for that database?
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
October 6, 2010 at 5:57 am
Prabu Krishnamoorthy (10/5/2010)
All users in our network getting this error while executing the query at first time.Each and every time we are getting this error at the first attempt of query execution. Next time executing the same query works fine.
just a thought..
if you are sure it happens only the first time a query is executed possible reason could be that the query is being recompiled and because of recompiling it is taking a lot of time and getting timed out.
Check on this but looks more like a network issue.
"Keep Trying"
October 7, 2010 at 6:02 am
Hi there.
Turn off the firewall on both the Server/Client Computer and Include in your query EXECUTE AS
and Let me know.
October 8, 2010 at 11:31 am
We had almost identical experience. The first time it was run, the query itself exceeded the standard timeout period, (middle tier, not db-level); involved some heady I/O & CPU. Once the results were in the query cache, however, subsequent queries obtained results from cache and therefore ran quickly and successfully. Look at your cache hits, if you can, comparing successful vs unsuccessful attempts. (Similar idea to the post regarding recompiles / procedure cache. If it were intermittent timeout, I'd even nose around for parameter-sniffing issues. But that's not what your symptoms sound like, here.)
In our case, we had static data that was dynamically re-calculated. We refactored a stored proc to pull from a table where the data was prepopulated and well-indexed; problem solved. Trade-off is extra db tasks to populate that table. A different problem involving on-the-fly data aggregation was solved similarly: a lag time of hours was deemed acceptible, so data was periodically pre-aggregated to a separate table with indexed dimensioned fields and aggregated totals; (kind of a poor man's OLAP when analy serv's aren't an option). Not first choice; rather, last resort when run out of tuning & refactoring options.
If the queries are identical and clustered around a predictable time, perhaps you could make a 'pre-emptive' strike: set up a scheduled process or job that runs the query against the db to get the data cached just before the end users start querying for it. That's surely not ideal, but it might provide a quick and dirty band-aid until you can implement a more permanent solution.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply