I have an urgent problem that I hope you can help me with. I have a stored procedure (let's call it stored_proc_A) that runs twice a week via a SSIS package run through a scheduled SQL Server Agent job. For over a year, this has run fine. Normally the SQL Server Agent takes less than 2 minutes to run the SSIS package that includes stored_proc_A. This morning, the SQL Server Agent job ran for about an hour and a half before I eventually cancelled it. I tried restarting SQL Server and running the SSIS package manually in BIDS, but the same thing is happening. The step that runs stored_proc_A is still yellow after more than half an hour and is still running as I write this.
The stored_proc_A is a vital component of the SSIS package that produces a report, which must go out today. I've tried a few things to work out what's going on, including checking if the SPID is blocked (apparently not), whether there are any open transactions (apparently not), etc. I'm not experienced with this kind of problem so any assistance you can provide to help me get to the bottom of what's going on will be much appreciated.
As a point of note, I did some work related to stored_proc_A yesterday, which may be relevant (although I don't understand why). Within stored_proc_A, it calls another stored procedure called stored_proc_B. I created a table yesterday and populated it with a few records (about 50) that need to be excluded from the report in question. I then amended stored_proc_B (which excludes problem records from the report) so that in addition to its normal exclusion rules it also picks up records on this new table and excludes them from the main report. During testing yesterday, I inserted these problem records manually into the main report table and then ran stored_proc_B (in SSMS) successfully to verify that it correctly extracts these records on the exception report, which it did.
Nothing has changed in the stored_proc_A procedure, so I'm stumped. All I've been able to deduce so far is that the SPID associated with stored_proc_A is not blocked and continues to consume CPU and IO resource. When I filter the default SQL Profiler template on the SPID, I get no events at all apart from the iintial ExistingConnection details. The SQL Server Agent job history reports no error or warning messages and its the same in the Windows Application event log. It's as if the stored procedure is stuck in a loop doing nothing. Can someone please help?
Many thanks in advance.