Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server Agent - SSIS Package - Error 0x80131904 - Timeout expired

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.
like image 293
Ryan Avatar asked Jul 16 '14 13:07

Ryan


1 Answers

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.

like image 178
Dave Cullum Avatar answered Sep 23 '22 17:09

Dave Cullum