locked
SSISDB Catalog package fails with "created execution" RRS feed

  • Question

  • Not sure if this is the right forum given the issue.

    Environment: SQL 2014 EE SP2 

    We have 2 packages that are failing in SQL Agent with "Created Execution". The packages are cataloged in SSISDB in their own folder.

    One package runs every 5 minutes, the other every 4 hours between 8AM and 6PM. Each package has failed with this error at random times through the day.

    These same packages are executed via file share on other instances on the same box and do not encounter this error.

    The limited information I could find online suggested 
    1. Missing indexes in SSISDB/Deadlocks
    2. High CPU utilization preventing executable from starting/completing.

    Any thoughts on how to find/fix this issue? There were posts related to SQL 2012 but nothing for SQL 2014.

    Thanks,

    Morgan

    Monday, November 28, 2016 2:49 PM

Answers

  • This is contention.

    Apply any outstanding updates (service packs) and post here if the issue persists. 


    Arthur

    MyBlog


    Twitter

    • Proposed as answer by Eric__Zhang Tuesday, November 29, 2016 3:53 AM
    • Marked as answer by mfearskp Tuesday, November 29, 2016 4:48 PM
    Monday, November 28, 2016 6:47 PM
  •  

    Hi mfearskp,

    Try to apply the latest service pack for SQL Server 2014 Enterprise edition. 

    Check this KB You experience a deadlock condition when you run multiple SSIS packages in SQL Server 2012. It is actually a fix to the related creation execution and deadlock description. According to the KB description, it seems an issue specifically when using the SSIS.catalog.create_execution stored procedure. If this is your case and applying SP doesn't work, then try to avoid using the forementioned stored procedure, and use the Dtexec.exe workaround instead.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Eric__Zhang Tuesday, November 29, 2016 4:07 AM
    • Marked as answer by mfearskp Tuesday, November 29, 2016 4:48 PM
    Tuesday, November 29, 2016 3:56 AM

All replies

  • how does the server load looks like at the time the packages fail?

    Arthur

    MyBlog


    Twitter

    Monday, November 28, 2016 3:13 PM
  • Hi Arthur,

    The "idle" CPU utilization on this host is ~50%, so I would guess with a couple of other jobs running for other instances at/around the same time the host is probably 80-90% utilization.

    Thanks,

    Morgan

    Monday, November 28, 2016 3:26 PM
  • You need to investigate, mobile the PerfMonitor to log the machine vitals.

    Please post the error message in its entirety.


    Arthur

    MyBlog


    Twitter

    Monday, November 28, 2016 3:52 PM
  • Thanks Arthur.

    Beyond the "created execution" status from SSIDB report, SQL Agent reported it was in fact deadlocked.

    \RecSecToCommon_Import.dtsx, Environment reference Id: NULL.  Description: Transaction (Process ID 52) 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:  12:10:00 PM  Finished: 12:10:02 PM  Elapsed:  2.266 seconds.  The package execution failed.  The step failed.

    Monday, November 28, 2016 6:09 PM
  • This is contention.

    Apply any outstanding updates (service packs) and post here if the issue persists. 


    Arthur

    MyBlog


    Twitter

    • Proposed as answer by Eric__Zhang Tuesday, November 29, 2016 3:53 AM
    • Marked as answer by mfearskp Tuesday, November 29, 2016 4:48 PM
    Monday, November 28, 2016 6:47 PM
  •  

    Hi mfearskp,

    Try to apply the latest service pack for SQL Server 2014 Enterprise edition. 

    Check this KB You experience a deadlock condition when you run multiple SSIS packages in SQL Server 2012. It is actually a fix to the related creation execution and deadlock description. According to the KB description, it seems an issue specifically when using the SSIS.catalog.create_execution stored procedure. If this is your case and applying SP doesn't work, then try to avoid using the forementioned stored procedure, and use the Dtexec.exe workaround instead.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Eric__Zhang Tuesday, November 29, 2016 4:07 AM
    • Marked as answer by mfearskp Tuesday, November 29, 2016 4:48 PM
    Tuesday, November 29, 2016 3:56 AM
  • Thanks everyone for the insight. We're patched to SP2 but not the CU(s) for SP2.

    The KB article is for SQL 2012 and we're still experiencing this with in 2014 SP2. I take it this isn't going to be addressed in SQL 2014?

    I found an article with suggested indexes for the SSISDB and I applied those. So far, the errors appear to have subsided.

    The DTExec workaround pretty much negates the ease of use for use package deployments when you have to revert to CLI execution.

    Thanks again for the assistance.

    Morgan

    Tuesday, November 29, 2016 4:53 PM
  • Hi mfearskp,

    May you share the link?
     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 30, 2016 1:14 AM
  • Hi Eric,

    Sure, not sure why I can't paste the URL

    http://www.ssistalk.com/2013/01/31/ssis-2012-catalog-indexing-recommendations/

    It appears they rewrote the procedure  and altered the lock escalation on all the tables. I haven't gone that far.. would like to be able to call for MS support and not be on an adulterated version of the SSISDB.

    Thanks!

    Wednesday, November 30, 2016 6:23 PM
  • The recommendations in the script didn't resolve our deadlock situation, but it got me on the right track.

    I added the   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to [catalog].[set_execution_parameter_value] and everything is running well now.

    Thanks


    Friday, October 5, 2018 4:29 PM