Trasport level Error

Question

Post reply

Trasport level Error

Prabu Krishnamoorthy

Valued Member

Points: 52
More actions
October 5, 2010 at 7:01 am

#224717

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?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

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"

Prabu Krishnamoorthy Valued Member Points: 52 More actions · Answer 2

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.

SkyBVI SSCoach Points: 15128 More actions · Answer 3

Often, it's the database being offline/closed or the SQL Instance being restarted/offline

Check it.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Prabu Krishnamoorthy Valued Member Points: 52 More actions · Answer 4

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.

SkyBVI SSCoach Points: 15128 More actions · Answer 5

@ prabuk

Check in SSMS, server, properties, connections, wat is the remote query timeout

?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Prabu Krishnamoorthy Valued Member Points: 52 More actions · Answer 6

Prabu Krishnamoorthy

Valued Member

Points: 52

October 5, 2010 at 10:04 am

#1231862

600 seconds

SkyBVI SSCoach Points: 15128 More actions · Answer 7

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

Adiga One Orange Chip Points: 27284 More actions · Answer 8

Is the "AutoClose" option enabled for that database?

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

ChiragNS One Orange Chip Points: 26137 More actions · Answer 9

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"

adalberto-339588 SSC Veteran Points: 261 More actions · Answer 10

Hi there.

Turn off the firewall on both the Server/Client Computer and Include in your query EXECUTE AS

and Let me know.

jdk4 SSC Rookie Points: 28 More actions · Answer 11

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.