April 13, 2012 at 4:28 am
My brain is fried after bouncing from one project to another, so I just need a second pair of eyes to help me think this through. Any help would be appreciated.
I have an SSIS package with a web service call (only) in it (I did not design it). The web service executes a proc that loads a table from 3 other tables. Then it pulls the resulting data into a feed that goes to another system using (I think) BAPI(?) calls. Anyway, the majority of the web service code appears to be creating SQL statements within C# to do all the work on the db.
Our architecture has the packages and the jobs that run them on an application server, which reaches out to the database server to do its magic. FYI: I cannot change this hardware / package configuration, so putting the package elsewhere is not an option.
The package has worked fine over the past 3 months. Now it is experiencing SQL timeout errors when the job kicks it off. A second, manual, run of the package works fine. I am fairly certain this is not a permissions issue.
Timeout Error
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at XXXX.YYYY.DataLoad.Host.MyService.CreateFile()
I have previously checked out other connections, looked for other blocking processes and other running processes. We have tried rescheduling this job at all sorts of odd times (5:38, 6:13, etc.) to see if maybe it was that a bunch of jobs were hitting at the same quarter hour time frame. The Event Logs aren't showing any specific errors.
My next thought is that maybe I have indexing issues with the tables. So I'm planning on looking at the proc's execution plan and the other T-SQL statements execution plans to see if anything hokey is going on there. This is the part I'd like to verify. If you had this problem, would this be your next troubleshooting step? Or did I miss something that I should be looking at before this?
As a last resort, I'm thinking of redesigning the package so all the SQL work is in Execute T-SQL or Data Flow tasks. Then have a simpler web service call at the end. But before I go to this extreme, I want to try all other options first.
Thoughts?
April 16, 2012 at 9:16 am
> The package has worked fine over the past 3 months. Now it is experiencing SQL timeout errors when the job kicks it off.
Sounds like the same problem I encountered months back resulting in having to make a full refund to my customer because the customer cannot wait for me to resolve the problem, the software being too critical - a retail management system.
This problem is in fact a bug in the ODBC SQL Server driver or the OLE DB provider in MDAC as confirmed on this MS page: http://support.microsoft.com/kb/957585
It said "Microsoft has confirmed that this is a problem in the Microsoft products..." The shocking thing is that this bug been around for 10 years or longer without being resolved.
I tried everything recommended on forums including:
:: setting the connection commandtimeout to a higher value like 600 both in the program code and SQL server
:: using SQLOLEDB instead of MSDASQL in the connection string; both still use the same ODBC driver
The error persisted.
Lately I tried closing and then re-opening just before executing a sql update string that triggered the error. I found that it resolved the error.
However to use this trick one may need to ensure that any values retrieved from the database prior to that point be stored in variables for them to persist. Otherwise the closing and re-opening of the connection cause them to be lost.
I know this is not the real solution but given that Microsoft has not been able to resolve this long-standing bug, this is worth trying.
--------
I also found this page
The developer states that the error "... I suspect, is a permanent feature of MSSQL, but does not manifest on smaller tables because the malfunction does not cause a timeout on smaller tables,.."
I think he is right. My own customer was running my pos system for over a month without a hitch. However when the sale table grew in size, the error started popping up making it impractical to continue with the system.
Qing
April 16, 2012 at 9:19 am
Sorry a typo error:
"Lately I tried closing and then re-opening just before executing a sql update string that triggered the error. I found that it resolved the error."
should read:
"Lately I tried closing and then re-opening THE CONNECTION just before executing a sql update string that triggered the error. I found that it resolved the error."
April 16, 2012 at 10:35 am
Actually, I found a solution. Sort of.
The queries touch 4 tables. 1 has 2 indexes (1 clustered, 1 non), and the other 3 only had clustered indexes on the PK. Nothing on JOIN clause columns or WHERE statement columns.
I added a few NC indexes, ran UPDATE STATISTICS on the tables, and for the past three days, the job has run fine. (knock on wood). We will see how long this lasts.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply