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',
@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)