SSIS package fails to start - application lock timeout in SSISDB.catalog.create_execution - by GreenanT

Status : 

  Duplicate<br /><br />
		This item appears to be a duplicate of another existing Connect or internal item.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


45
0
Sign in
to vote
ID 783291 Comments
Status Closed Workarounds
Type Bug Repros 25
Opened 4/9/2013 10:58:32 AM
Access Restriction Public

Description

I've recently upgraded my SSIS packages from SQL2005 to SQL2012 and I'm using SQL Agent to execute the pacakges. The jobs are failing about 20% of the time with a timeout error. I ran a trace to see what was causing the problem and the error occurs in the SSISDB.catalog.create_execution procedure when it calls sp_getapplock.

EXEC @lock_result = sp_getapplock 
@Resource = 'MS_ISServer_Create_Execution',
@LockTimeOut= 5000, 
@LockMode = 'Exclusive'

The timeout occurs in sp_getapplock when it calls sys.xp_userlock, the error number is 27195.

exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout

I have 11 packages that are all executed every minute so I suspect that this is 'causing' the problem. I had no issues when I execute 11 packages concurrently in SQL2005 or SQL2008.

This is a significant blocking issue preventing us from upgrading to SQL2012 - it should be able to handle executing 11 packages per minute!

I'm running SQL2012 SP1 CU 3 - 11.0.3350.0 (x64)
Sign in to post a comment.
Posted by mbourgon on 6/26/2014 at 8:10 AM
For what it's worth, all, there may be a fix in 2012 SP2.
from http://support.microsoft.com/kb/2958429

SSISDB deadlock and cleanup performance improvement

    Introduction
        Deadlock in SSISDB database on concurrent calls to SSISDB.catalog.create_execution stored procedure
            A fix has been done for SSISDB concurrences and deadlock issue. However, this does not resolve all problems. Current workaround is staggering the jobs by a few seconds. But you may not use this workaround.
            Poor performance on SSISDB maintenance job
        A hotfix is available for SSISDB maintenance job.
Posted by dragonspeare on 10/25/2013 at 3:38 AM
The workaround worked for me and the problem identification is spot on - problem is that we're running this altered procedure in a production environment now. Really hope to see a hotfix issued by Microsoft for this issue - Enterprise customers all run more than 10 SSIS packages concurrently, please MS - we need some action on this!
Posted by Mark Tassin on 4/25/2013 at 4:19 AM
Half of the problem is that whoever designed the database forgot to index the most commonly used tables.

The other half is the maintenance plan using referential on delete constraints to purge all of the tables.

Even with applying recommended indexes and modifying the plan though, in a relatively busy SSIS system, I still have jobs that timeout getting the parameters.