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.

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


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 John Eisbrener on 3/22/2017 at 8:58 AM
Anyone able to identify what connect item was the "duplicate" of this issue? Would love to see other workarounds as well if they're out there.
Posted by AndreasS on 4/11/2016 at 4:24 AM
Hi SSIS Team,

we experience the same issue with SQL Server 2014. The workarounds do not work for us either.
Can you please point me to the 'duplicate' of this bug? Since this one here is 'closed as duplicate'.
I hope to find the solution there?

Posted by Matt M14 on 9/29/2015 at 12:04 PM
SSIS Team,
Please hear us!!!
This issue needs to get corrected. We are running SQL 2014 Enterprise and are still receiving this error from time-totime. There needs to be a fix in SP2 to finally correct this.
Is the sp_applock really necessary? Is there a better way to make this issue go-away?

Please help.
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.

SSISDB deadlock and cleanup performance improvement

        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.