Recently I faced an issue where SQL agent jobs executing SSIS packages were failing with error: “Failed to execute IS server package because of error 0x80131904. Description: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”
As per above error message it is evident that SSIS package was not getting executed, hence no execution report was available. Verified that SQL server integration services were up and running fine. Even through SQL agent job step was being retried several times, it was still failing with same error.
Below was the troubleshooting done to fix the issue.
Checked application event logs and found that below error was being reported repeatedly.
Log Name: Application
Source: SQLISServer
Date: 02/01/2023 12:15:00
Event ID: 1
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: abc.domain.com
Description:
Service Control
Although above message does not seem helpful, but when I checked the XML view in details tab, below error message was reported.
The SSIS Execution Process could not write to the IS catalog: ABC:SSISDB Error details: Unable to add new messages to the table of operation messages.:Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Operation cancelled by user.
With this error message it was clear that SSIS process is not able to add any new message to operations table. Further checked and found that “SSIS Server Maintenance Job” was running for long time which was holding a exclusive lock on the table [internal].[operations] due to which it was not allowing SSIS packages to log into SSIS Catalog.
Job was running against SPID 67 and below process was running against it.
DELETE TOP (@delete_batch_size)
FROM [internal].[operations]
WHERE ( [end_time] <= @temp_date
OR ([end_time]
IS NULL AND [status] = 1
AND [created_time] <= @temp_date ))
Further found that as per job history “SSIS Server Maintenance Job” was running for long durations due to which above process was holding the exclusive lock on [internal].[operations] table and not allowing SSIS job related processes to add messages in the table and thus causing non execution of package due to timeout.
SQL server version was SQL 2012 SP2 and as per KB2829948 [support.microsoft.com], slow performance of SSIS maintenance job was fixed under CU7 for RTM version and CU4 for SP1 but still “SSIS Server Maintenance Job” was slow and long running.
Further found that as per KB2972285 [support.microsoft.com], issue was fixed in SQL 2012 SP2 if we change operation log’s encryption level to PER_PROJECT (2) from the default of PER_EXECUTION (1) by following the steps mentioned in KB2972285 .
Once we applied the recommendations given in KB2972285 , issue got fixed and run time of “SSIS Server Maintenance Job” reduced from average of 10 hours to 1.5 hours average which allowed other SSIS processes to run properly and avoid error 0x80131904.
Hope this article will help you to troubleshoot long running “SSIS Server Maintenance Job” and SSIS package execution failure issues with error 0x80131904 in your environment.
Happy Learning!!