SSIS 2012 Deadlock When Running SSIS from SQL Agent Job
Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Saint Augustine, FL. Bradley is a former consultant, trainer, and has coauthored 6 SQL Server and Power BI books, most recently Learn Microsoft Fabric. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. Bradley gives back to the community through speaking at events such as the SQLBits, Fabric Community Conference, PASS Community Data Summit, SQL Saturdays, Code Camps, and user groups across the country including locally at the Jacksonville SQL Server User Group (JSSUG). He is a contributor on SQLServerCentral.com and blogs on his personal site, BradleySchacht.com.
The error message text read as follows: Executed as user: . Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 1:00:01 AM Failed to execute IS server package because of error 0x80131904. Server: , Package path: SSISDB.dtsx, Environment reference Id: NULL. Description: Transaction () was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Source: .Net SqlClient Data Provider Started: 1:00:01 AM Finished: 1:00:09 AM Elapsed: 8.331 seconds. The package execution failed. The step failed. The reasoning behind this failure is the stored procedure in the SSISDB catalog.create_execution. If multiple calls are made with that stored procedure at the same time then a deadlock could occur. Essentially that is what happens when you schedule two SSIS packages at the same exact time in SQL 2012, the procedure is run for each job that starts and causes the deadlock error seen above. [notice]The following information is a direct copy from