There's been a string of random occurrences of the following error in the SQL Server Agent scheduled jobs lately that I have been unable to find a solution to.
The error occurs infrequently, but usually once a week for a daily scheduled job, but in any number of different jobs and not always the same one. Each job shares the fact that it executes an SSIS package from the same server that is running the job. It also always runs for almost exactly 30 seconds elapsed time, which I guess is the timeout threshold. I'm not sure why it would timeout if the server is just connecting to its own SSIS catalog. Also of note is that it never actually gets to the point where it executes the SSIS package, and this occurs regardless of which package is trying to be executed.
During my research I came across many people suggesting that simply updating SQL Server 2012 to the latest CU* or SP2 would solve the problem. However, upgrading the server to SP2 has not.
One solution tried (which admittedly was ugly) was to simply have a single retry upon failure of the job step, which actually did solve the problem in about 30% of the cases.
I would welcome anyone who has experience with this error, or anyone who has any suggestions.
The error message is as follows:
Date 16/07/2014 6:00:11 AM
Log Job History ({$jobname})
Step ID 1
Server {$productionserver}
Job Name {$jobname}
Step Name {$stepname}
Duration 00:00:31
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: {$user}.
Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved.
Started: 6:00:11 AM Failed to execute IS server package because of error 0x80131904.
Server: {$productionserver},
Package path: {$packagepath},
Environment reference Id: NULL.
Description: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Source: .Net SqlClient Data Provider
Started: 6:00:11 AM Finished: 6:00:42 AM
Elapsed: 31.122 seconds. The package execution failed. The step failed.
Try this:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
And this
https://connect.microsoft.com/SQLServer/feedback/details/783291/ssis-package-fails-to-start-application-lock-timeout-in-ssisdb-catalog-create-execution
Looks like its a known bug.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With