October 6, 2015 at 12:59 pm
SSIS package jobs in SQL 2012 failing with deadlocks. Execution Report of SSIS package has “ Created Execution” status
Issue: SQL Agent jobs running an SSIS packages failed with below deadlock message.
Executed as user: Domian\xxx. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5582.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 11:53:04 AM Failed to execute IS server package because of error 0x80131904. Server: SQL1, Package path: \SSISDB\<Folder>\<Project>\<SSIS Package Name>.dtsx, Environment reference Id: 10017. Description: Transaction (Process ID 64) 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: 11:53:04 AM Finished: 11:53:13 AM Elapsed: 9.641 seconds. The package execution failed. The step failed.
Execution report of the SSIS packages showed the status of “Created Execution”. There are no messages in the reports to explain this issue.
Cause: This issue occurs because of a problem in the [SSISDB].[catalog].[create_execution] stored procedure. When multiple calls are made to the [SSISDB].[catalog].[create_execution] stored procedure at the same time, the requested locks may form a deadlock.
https://support.microsoft.com/en-us/kb/2699720
Alternate resolutions: For SQL severs with versions lower than SQL 2012 SP2 CU5, staggering the SSIS jobs to run at different schedules helps alleviate the problem.
Prakash B
October 6, 2015 at 2:30 pm
If this is reproducible, fire up profiler to look for deadlocks and get the deadlock graph while running the package. Then you'll see why.
Gerald Britton, Pluralsight courses
October 7, 2015 at 7:36 am
Prakash.Bhojegowda (10/6/2015)
SSIS package jobs in SQL 2012 failing with deadlocks. Execution Report of SSIS package has “ Created Execution” statusIssue: SQL Agent jobs running an SSIS packages failed with below deadlock message.
Executed as user: Domian\xxx. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5582.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 11:53:04 AM Failed to execute IS server package because of error 0x80131904. Server: SQL1, Package path: \SSISDB\<Folder>\<Project>\<SSIS Package Name>.dtsx, Environment reference Id: 10017. Description: Transaction (Process ID 64) 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: 11:53:04 AM Finished: 11:53:13 AM Elapsed: 9.641 seconds. The package execution failed. The step failed.
Execution report of the SSIS packages showed the status of “Created Execution”. There are no messages in the reports to explain this issue.
Cause: This issue occurs because of a problem in the [SSISDB].[catalog].[create_execution] stored procedure. When multiple calls are made to the [SSISDB].[catalog].[create_execution] stored procedure at the same time, the requested locks may form a deadlock.
https://support.microsoft.com/en-us/kb/2699720
Alternate resolutions: For SQL severs with versions lower than SQL 2012 SP2 CU5, staggering the SSIS jobs to run at different schedules helps alleviate the problem.
It's a bug you have to upgrade to latest service pack or like you said schedule at different times.
October 7, 2015 at 7:47 am
Alex,
Upgrade to latest SP/CU did not fix the issue. The only work around was to stagger the schedule of SSIS package-jobs.
Prakash B
October 7, 2015 at 7:49 am
Is there anything updating\Inserting or deleting from the db at the same time
October 7, 2015 at 7:50 am
Cause: This issue occurs because of a problem in the [SSISDB].[catalog].[create_execution] stored procedure. When multiple calls are made to the [SSISDB].[catalog].[create_execution] stored procedure at the same time, the requested locks may form a deadlock
Prakash B
October 7, 2015 at 7:58 am
oK
October 7, 2015 at 8:00 am
AlexSQLForums (10/7/2015)
Prakash.Bhojegowda (10/6/2015)
SSIS package jobs in SQL 2012 failing with deadlocks. Execution Report of SSIS package has “ Created Execution” statusIssue: SQL Agent jobs running an SSIS packages failed with below deadlock message.
Executed as user: Domian\xxx. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5582.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 11:53:04 AM Failed to execute IS server package because of error 0x80131904. Server: SQL1, Package path: \SSISDB\<Folder>\<Project>\<SSIS Package Name>.dtsx, Environment reference Id: 10017. Description: Transaction (Process ID 64) 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: 11:53:04 AM Finished: 11:53:13 AM Elapsed: 9.641 seconds. The package execution failed. The step failed.
Execution report of the SSIS packages showed the status of “Created Execution”. There are no messages in the reports to explain this issue.
Cause: This issue occurs because of a problem in the [SSISDB].[catalog].[create_execution] stored procedure. When multiple calls are made to the [SSISDB].[catalog].[create_execution] stored procedure at the same time, the requested locks may form a deadlock.
https://support.microsoft.com/en-us/kb/2699720
Alternate resolutions: For SQL severs with versions lower than SQL 2012 SP2 CU5, staggering the SSIS jobs to run at different schedules helps alleviate the problem.
It's a bug you have to upgrade to latest service pack or like you said schedule at different times.
Do you have a link to the MS documentation for the bug?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 7, 2015 at 8:01 am
October 7, 2015 at 8:03 am
[/quote]
Do you have a link to the MS documentation for the bug?[/quote]
https://support.microsoft.com/en-us/kb/2699720
October 7, 2015 at 10:33 am
Thanks for the links, folks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply