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

Question

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.

Thompson

What more info are you looking for? I gave some examples of things I've tried, the frequency of the error, the fact that it doesn't occur every time or always to the same job. I also included the full error message that I'm receiving. What more could I add? – Thompson

That's just it though. The SSIS never even executes to begin with, so the code is irrelevant. It also happens on several very different SSIS packages. I guess that's one thing I could have mentioned above, I'll add it in. – Thompson

Did you try with increasing the timeout on the Connection Manager? Also it depends on what's your package is doing. – Bethanie

0x80131904 - is a credentials error. check your credentials still work on the remote box... also, did you save the password in a config file (or some other means of saving it). – Artificiality

Everywhere I can find a timeout, it's been set to 0 (No timeout), and it doesn't execute the package, so I'm not sure it does depend on what the package is doing. – Thompson

@JiggsJedi - The error isn't related to credentials because the same job unmodified worked 7/10 times. It will only produce this error on average once or twice a week. The password is not saved, it's all NT authentication using the SQL Server agent account. – Thompson

@Thompson Were you able to find a solution to your problem? I'm in the same boat here! – Istle

@Thompson Any solution to this problem? Same scenario – Sunless

Answer 1

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.

Artificiality

Thanks, I actually did notice that bug report and almost included it in my description, but there's nothing on that bug report that actually gives this error code and I didn't want to throw people in the wrong direction. I did however, attempt one of the workarounds (making sure the server wasn't busy autogrowing the DB), but it didn't work. Also, that one also implied that SP2 was going to fix the problem, but it hasn't. Thanks though :) – Thompson

Answer 2

Check what else is/was running on the instance at the time of the package failures (e.g. a database integrity check or similarly intensive operation).

The SQL Agent is timing out talking to its own SSIS catalog (a 30 second timeout). It's not actually executing the packages, so it's nothing to do with the packages themselves and everything to do how busy the instance is at the time of the execution.

(Answering this question since it comes up in a Google search)

Compressive

Answer 3

I know this is an older question. but I'm having the same problem and this doesn't have an accepted answer.

The job fails in 1.5 seconds so I believe it is NOT a timeout issue.

I can confirm 0x80131904 is (or can be) a permissions issue. I had my SSIS package running under a SQL Agent job just fine with sysadmin and network admin privileges. when i switched it to an account with fewer permissions, i get this error.

For me, the problem was because i was not assigning permissions in all the correct places. I already set Read/Execute permissions in the Project Properties. Then (this is the step I didn't do) I had to assign Read permissions on the folder containing Projects and Environments.

Hope this helps someone.

Rivy

Answer 4

We have experienced this error when attempting to start several SSIS packages at the same instant. Service packs were supposed to fix it, but have not. We have implemented a staggered schedule for SSIS packages so only one package is starting at any given moment.

Conservative

Answer 5

We also experienced the same bug. As a workaround, we created the following stored procedure. If you put this into a job that runs every f.e. 10 minutes, it makes sure that if there are random failures, the job gets restarted continuously until you reach an occurence without timeout failure.

USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[usp_StartTimedOutJob]
AS

DECLARE @jobid NVARCHAR(100)
    , @jobname NVARCHAR(250)
    , @stepname NVARCHAR(250)
    , @varMail VARCHAR(MAX)

DECLARE cJobs CURSOR FOR 

-- CTE selects all jobs that are currently not running and orders them by most recent
WITH CTE_NotRunning AS (
    SELECT S.job_id
        , S.step_name
        , S.[message]
        , rownum = ROW_NUMBER() OVER (PARTITION BY S.job_id ORDER BY S.run_date DESC, S.run_time DESC)
    FROM msdb.dbo.sysjobhistory AS S
    LEFT OUTER JOIN (SELECT DISTINCT ja.job_id 
                    FROM msdb.dbo.sysjobactivity ja 
                    LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
                    JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
                    JOIN msdb.dbo.sysjobsteps js
                        ON ja.job_id = js.job_id
                        AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
                    WHERE
                      ja.session_id = (
                        SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC
                      )
                    AND start_execution_date is not null
                    AND stop_execution_date is NULL) AS R
                        ON S.job_id = R.job_id
    WHERE R.job_id IS NULL)

-- only select the jobs into the cursor set for which the most recent job had a timeout issue
SELECT job_id
    , step_name
FROM CTE_NotRunning
WHERE [message] LIKE '%0x80131904%time%out%' -- error message that corresponds to timed out jobs, error code: 0x80131904
    AND rownum = 1

OPEN cJobs

    FETCH NEXT FROM cJobs 
        INTO @jobid, @stepname

    WHILE @@FETCH_STATUS = 0
        BEGIN

            -- for each of the timed out jobs in the cursor, start the job again from the step that caused the timeout

                SET @jobname = (SELECT [name] FROM msdb.dbo.sysjobs WHERE job_id = @jobid)

                EXECUTE dbo.sp_start_job @job_id = @jobid, @step_name = @stepname 

        END

CLOSE cJobs

DEALLOCATE cJobs

GO

Affer

Answer 6

I had this exact same issue. SQL Agent was running SSIS Jobs perfectly fine then suddenly I came across this error. Spent about an hour looking for a fix online. Found out the server admin had installed new windows updates.

I simply restarted the Server (which hosts the SSIS catalog and SQL Server/Agent). After server restart jobs ran fine again.

Hope server restart works for the next person that goes through this.

Boult

Answer 7

Sometimes this kind of error occurs when the package is deployed twice under SQL Integration Service Catalogs. You also may have changed the package name but there are other related auto-generated configurations are unique like the Environment reference Id and others .

So if you have a scheduled job, you will need to create a new one and point it to the .

Good Luck

Shulins

Answer 8

I had the same problem and error message on SQL Server 2017.

My problem was on the SSISDB database, that was too big and had to be maintained (no more space available). After having cleaned up the SSISDB database, the jobs ran well again on this server.

Vaccinia