August 21, 2016 at 5:12 am
Hi All,
This is related to timeout troubleshooting inside SQL Server. I have below scenario in my environmen.
3 servers are involved.
Srv1 - here jobs and ssis pkgs are resided.
Srv2 - Source server from where data is pulled from
Srv3 - is the Destination Server where data has to get loaded.
Note: all 3 are SQL2014 servers.
Scenario is: On Srv1 there is a job which is scheduled to run every 4 hours. When the job runs, it calls an SSIS package. Within the SSIS package, there is script task which has some .net code which basically connects to Source i.e. Srv2 server,
pulls data and stored it in a dataset and then connects to destination server (Srv3) and loads data into it.
Issue is the when the job is kicked off on Srv1 server, sometimes it runs fine but many times it fails with timeout issues. But I am not sure it is query timeout or login timeout. In the job history it just says query terminated with timeout.
Information I am looking for?
1. How to get the exact error message of the timeout?
2. How to narrow down where exactly ( i mean, on which of the 3 sql server's) timeout is occuring?
3. Does the login time or query timeout gets logged in SQL server ERRORLOG or any connectivity DMV's/ring buffer?
4. Can we write of log timeout error number,error message and servr name where timeout has occured using custom logging to windows log or any text log file?
Thanks,
Sam
August 21, 2016 at 9:36 am
There are "internal" timeouts inside SQL Server, but they are invariably not raised to any client by SQL Server. Instead, when an internal timeout does not result in SQL Server retrying (internally), the client will be sent a high severity error that cancels execution. Not all errors have a high enough severity level to be logged to SQL Server's errorlog. You can find these non-cancelling internal timeouts only by tracing (and I suppose, using extended events with) SQL Server. I do not recommend starting here.
The vast majority of timeouts are raised by a client of SQL Server. SSIS is an example of a client. When a client (SSIS) raises a timeout, it is up to the client to log a timeout, including its message. For example, if custom .Net code raises a timeout, it is the responsibility of that .Net code to properly raise (and log) a timeout. Last I checked, SSIS has the ability to log its own timeouts, but you may need to increase SSIS's logging to see more details. On the other hand, if .Net code running within an SSIS package raises a timeout, the .Net code must log the timeout.
SQL Server responds to a client's query/command timeout with an Attention event, which can be traced or captured as an event on SQL Server. You will also need to capture the statement starting. If the client (SSIS) raises a query/command timeout and SQL Server does not handle Attention, you are looking at an intermittent connectivity error (likely to be one that lasted for seconds), while the query was running. Code that raises a custom timeout error is not useful or (in my opinion) reliable. Even so, a custom timeout message may be useful for certain client-server systems, such as web clients where exposing a timeout might reveal more to the public than is desirable.
What the above means:
1. Your SSIS package is raising a timeout, and it needs to be logged in more detail. If the .Net code within the SSIS package is raising a timeout, and it needs to be logged in more detail.
2. The SSIS package's custom timeout message appears to concern a query timeout, which must happen after a login is successful, but does not preclude there being an intermittent connectivity error between SSIS and the server (which the package's custom timeout message does not identify).
3. You could try to capture the Attention event on both of SSIS's SQL Servers, but without proper logging in the SSIS package you can easily attempt to resolve performance concerns upon both SQL Servers that are irrelevant to the SSIS package's timeout.
4. You could inspect both SQL Server errorlogs for high severity errors, but without proper logging in the SSIS package you can easily attempt to resolve an error that is irrelevant to the (supposed) query timeout that you see being raised by the SSIS package. Still, a SQL Server error with a high enough severity (I believe it is about severity level 18 or higher) is a serious problem that needs to be addressed on the logged SQL Server (and I would not expect a client to log a query timeout for such high severity errors).
5. While checking errorlogs (4) is always a good thing, you can become extremely busy checking 2 SQL Server errorlogs every time an SSIS package (and perhaps other clients) raises a poorly logged error message. It is far better to start with 1. Of course, if every client to SQL Server raises a timeout at the same time, you should run to the SQL Server errorlogs (and engage your network admin), but at very least it still would be "nice" to know _which_ (per 1) SQL Server needs to be inspected. Knowing the exact statement that the SSIS package was running would be even better.
August 21, 2016 at 10:54 am
Thanks for the inputs. Will try the server side trace on both Srv1 and Srv3 and look at the data.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply