September 4, 2009 at 5:42 am
Hi all,
There is a problem with my company’s application. The problem is very predictable, it happens once every morning when we start sending transactions. It works fine all day until next morning when the first transaction goes wrong. We believe this to be a SQL or network problem but have no idea how to fix it. Does anybody have any ideas how to proceed?
We use two machines, one application server and one database server, both running Windows Server 2008. On the database server we have SQL Server 2008.
Here is the log where the problem occurs:
09-09-02 07:22:20 : PID=436 : Real=0975.990 : CPU=0.516 : mdd : Called from: 4103, SQL state: 01000, Native Error: 10054
09-09-02 07:22:20 : PID=436 : Real=0975.990 : CPU=0.516 : mdd : [DBNETLIB]ConnectionWrite (send()).
09-09-02 07:22:20 : PID=436 : Real=0975.990 : CPU=0.516 : mdd : Called from: 4103, SQL state: 08S01, Native Error: 11
09-09-02 07:22:20 : PID=436 : Real=0975.990 : CPU=0.516 : mdd : [DBNETLIB]General network error. Check your network documentation.
09-09-02 07:22:20 : PID=436 : Real=0975.991 : CPU=0.516 : mdd_ci : Spid:76 Line: 3143, : SELECT * FROM S09T1 WHERE [s09key] = 'trno ' ORDER BY 1 option(fast 1)
09-09-02 07:22:20 : PID=436 : Real=0975.991 : CPU=0.516 : mdd_sql_exec_direct : SELECT * FROM S09T1 WHERE [s09key] = 'trno ' ORDER BY 1 option(fast 1)
09-09-02 07:22:20 : PID=436 : Real=0975.991 : CPU=0.516 : mdd_sql_exec_direct : Spid:76 Line: 1659, : error executing
09-09-02 07:22:20 : PID=436 : Real=0975.991 : CPU=0.516 : mdd : Called from: 1661, SQL state: 08S01, Native Error: 0
09-09-02 07:22:20 : PID=436 : Real=0975.991 : CPU=0.516 : mdd : Communication link failure
09-09-02 07:22:20 : PID=436 : Real=0975.991 : CPU=0.516 : mdd_sql_xec_direct : Spid:76 Line: 1665, : SELECT * FROM S09T1 WHERE [s09key] = 'trno ' ORDER BY 1 option(fast 1)
09-09-02 07:22:20 : PID=436 : Real=0975.991 : CPU=0.516 : mdd_ci : Spid:76 Line: 3153, : Error executing statement
Best regards,
JÅ
September 4, 2009 at 7:24 am
It looks like something in the TSQL, not the network. Can you post the whole query?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 7, 2009 at 9:28 pm
What happens overnight? Nothing, no transactions?
The 10054 error (first line) is a Winsock error for connection reset. Does your app reconnect in the morning or does it just assume that the connection it's hung onto for the past few hours is still valid?
Is this a ASP.NET app?
Oliver
September 8, 2009 at 5:14 am
Sounds like a network error, like asked before, can you say what sort of application it is and is there any messages in the event logs for this time.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 8, 2009 at 5:34 am
Hi everyone,
Thank you for your replies. It is not an ASP.NET application, it is a C/C++ application.
I have looked through the event log on bothe the application server and the database server but have not found anything relevant to this problem.
I am actually not sure if the application reconnects or if it asumes a valid connection.
Best regards, JÅ
September 8, 2009 at 8:37 am
You usually see this when either of the following are true:
1) Something in the query hits a bug in SQL Server and causes the connection to abort. This would occur just about every time (if not every time) if that was the case.
2) The connection has been broken but the application doesn't know it. For instance, you can simulate this by connecting to a non-prod SQL Server, opening up a query window, executing a simple query, stopping and restarting the SQL Server, and then trying to re-execute the query in that same query window. SSMS will throw the error the first time around but is smart enough to reconnect so if you execute the query a third time, it actually works.
Are there any kind of network settings that have been applied to either system which closes orphaned connections or forcibly tears down idle connections? What about at the network layer?
K. Brian Kelley
@kbriankelley
May 22, 2016 at 2:01 am
K. Brian Kelley (9/8/2009)
You usually see this when either of the following are true:1) Something in the query hits a bug in SQL Server and causes the connection to abort. This would occur just about every time (if not every time) if that was the case.
2) The connection has been broken but the application doesn't know it. For instance, you can simulate this by connecting to a non-prod SQL Server, opening up a query window, executing a simple query, stopping and restarting the SQL Server, and then trying to re-execute the query in that same query window. SSMS will throw the error the first time around but is smart enough to reconnect so if you execute the query a third time, it actually works.
Are there any kind of network settings that have been applied to either system which closes orphaned connections or forcibly tears down idle connections? What about at the network layer?
Right on the money it was a Virtual server that i had resumed ....Restarted the service and all good on this error
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply