We have SQL Server 2005. We have several scheduled jobs using SSIS with numerous stored procedures running both in line and parallel. Some of these jobs have single steps (not SSIS) that execute stored procedures.
Using sys.dm_exec requests, I can identify the SPid that is blocking. The login is a service account and not identified to a particular user, but mostly used for the scheduled jobs and some other processes not for a particular user.
Is there a way to identify the actual query/Stored Procedure/SSIS procedure that is causing the block?
Thanks in advance.