Monday, June 6, 2011

SQL Server Agent Job issues-2

I'm continuing my experiences with managing jobs using SQL Server Agent. Couple of days back we faced a strange issue in SQL Server agent. The scenario was we trying to test run set of jobs we've set in QA before deployment onto production machine. Checking the activity in activity monitor revealed that quite a few jobs failed with a strange error message as below

Waiting for worker thread

We analysed and found that this is caused due to fact that lot of jobs are running in parallel. There is a maximum number of concurrent job setting known as max_worker_thread and this determines the level of concurrency. Changing this setting will enable you to control number of jobs concurrently executing in SQL Server agent. This is a registry setting. More details on this can be seen in below KB link.