You can create a custom profiler only for Stored Procedures which will tell you when a particular stored procedure will get called and finishes.

Tushar

It seems like I have figured out the problem.  We had the Auto_Close dboption set to TRUE. So for every step of the SQL Agent, the db connection has to...

It executes bunch of stored procedure within the database.  But at one point it executes xp_cmdshell with 'BCP' statement with username and password required to access the file in a...