Script that fixes the “sql server agent service won’t start” problem

Usually if you are pretty sure that the service account running sqlserveragent has sufficient rights and this service still refuses to start up, it is most of the time becuase of either the misconfigured "Agent XPs" or inaccessible sql server agent errorlog file. The following script fixes both issues.

Note: change the followng path for errorlog to a correct path according to your local server's setting.

Author: Ning Xu
Date: 2011-02-17
Applied version: 2005, 2008, 2008 R2
*/

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'Agent XPs', 1;
GO
RECONFIGURE with override
GO

USE msdb
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'D:\Programs\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT'
GO