版本是SQL SERVER2008R2, SQL AGENT日志里的错误如下
Message
[398] The job (Distribution clean up: distribution) has been queued because the maximum number of working threads (100) are already running. This job will be executed as soon as one of the working thread finishes execution.
我已经更新了表msdb.dbo.syssubsystems的max_worker_threads的值,连服务器都重启过了
Date 8/7/2014 8:39:24 AM
Log SQL Server Agent (Current - 8/7/2014 8:43:00 AM)Message
[398] The job (Distribution clean up: distribution) has been queued because the maximum number of working threads (100) are already running. This job will be executed as soon as one of the working thread finishes execution.
我已经更新了表msdb.dbo.syssubsystems的max_worker_threads的值,连服务器都重启过了
但是显然并没有应用这个更改后的值,SQL AGENT依然使用的是100这个默认值。
最后google找到解决方案,参考网址http://mssqlwiki.com/sqlwiki/sql-server-agent/sql-agent-maxworkerthreads-and-agent-subsystem/
这个问题的原因是SQL AGENT的max worker thread的默认值是CPU*100,我的虚拟机只有1个CPU,所以最多只能同时运行100个作业。
syssubsystems里面是某种代理可以运行的最多线程数,直接点说可以理解成某种代理作业数,例如复制分发代理,或者cmdexec代理。这个系统表里的值应该也和cpu数量有关,但是可以自行更改。
修改SQL AGENT的max worker thread的默认值,需要通过修改注册表
我测试的服务器是WIN SERVER208R2
KEY_LOCAL_MACHINE->SOFTWARE->Microsoft->Microsoft SQL Server->MSSQL版本号.实例名->SQLServerAgent
在这个目录下增加一个DWORD键值,名字是MaxWorkerThreads,键值是16进制的,可以根据你的需求更改。
重启SQL AGENT然后就OK了