We have a SQL Server 2005 Scheduled Job that runs every hour, every day of the week. The purpose of the Job is to import data from external systems (as input files text, csv, excel etc.). The job has been running for a number of years without specific incident. The Job is made up of 31 steps, each step runs a SSIS Package which imports various data into various Databases within the Instance. Since about the 4th of July 2011 we have had intermittent problems where the Job keeps hanging. Having investigated what appears to be going on when it hangs, the following has been determined: · The Job does not hang on the same Step each time. · If the Job is stopped and restarted, it will continue and either finish or hang on another step. · Each step runs a SSIS Package. We have run each SSIS Package in BIDS (i.e. on its own) and found that there is has been no problem. · It appears to be at Job Level that it is hanging. · Running SQL Server Profiler while the Job is running has shown that when the Job is hanging it is repeatedly executing the following: exec msdb.dbo.sp_help_job SELECT sv.name AS [Name], sv.category_id AS [CategoryID] FROM msdb.dbo.sysjobs_view AS sv WHERE (sv.name=N'MIS Import Manager - Master Job' and sv.category_id=N'100') SELECT null AS [CurrentRunStatus], CAST(sv.job_id AS nvarchar(100)) AS [job_id] FROM msdb.dbo.sysjobs_view AS sv WHERE (sv.name=N'MIS Import Manager - Master Job' and sv.category_id=N'100') · In some case the step has completed (i.e. the SSIS Package has finished) and the Job is waiting to go to the next step. If anyone has had a similar experience then your import would be greatly appreciated.

Hello Patarnold, how about instead of waiting for somebody to share their story we dig a tad in? I assume that the following could happen: there is some blocking and/or contigency on the SQL Server side ( I assume you run your jon using the SQL Agent). This contingency has gradually increased over the years as more jobs were added. If so, and you agree, how about you schedule this job to run a different time? And one more item, could you make sure the environment is up to date (SP/CUs - wise, and that the TEMP dir is not too littered?Arthur My Blog

have you got maintenance plan in place to Clean up Maintenance Plan History. as your msdb jobs history tables are not clearing up regularly. please configure maintenance job to do that... here is steps to setup maintenance plan http://www.sqldbadiaries.com/2011/03/16/clean-up-maintenance-plan-history/ hope it helps you http://uk.linkedin.com/in/ramjaddu

Hi patarnold, Please refer to the following article and see if it could help you. Troubleshooting SQL Server Jobs: http://www.mssqlcity.com/Articles/Adm/jobs_troubleshooting.htm If you have any more questions, please feel free to ask. Thanks, Eileen