Thursday, January 17, 2008

Fixing Startup Failure of SQL Server Agent Service in SQL Server 2005

I recently set up a new SQL Server 2005 + service pack 2 installation in a Windows 2003 Server environment. The services were configured to start using the network service account (NT AUTHORITY\NetworkService). All of the services started successfully, except for SQL Server Agent.

Symptoms

A warning dialog will appear with the following error message:

The SQL Server Agent (MSSQLSERVER) service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.


In Event Viewer, an error event is logged with the following description:

SQLServerAgent could not be started (reason: Error creating a new session).


The following appears in the SQL Server Agent log file (located at C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT on my system, but this will depend on the particulars of your installation):

2008-01-17 11:04:40 - ! [298] SQLServer Error: 15247, User does not have permission to perform this action. [SQLSTATE 42000] (DisableAgentXPs)
2008-01-17 11:04:40 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_has_server_access', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-01-17 11:04:40 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_get_startup_info', database 'msdb', schema 'dbo'. [SQLSTATE 42000]
2008-01-17 11:04:40 - ! [298] SQLServer Error: 229, The INSERT permission was denied on the object 'syssessions', database 'msdb', schema 'dbo'. [SQLSTATE 42000]
2008-01-17 11:04:40 - ! [000] Error creating a new session
2008-01-17 11:04:41 - ? [098] SQLServerAgent terminated (normally)
Cause

The account under which the SQL Server Agent runs does not have the permissions required to start the service successfully.

Solution

On my system, SQL Server Agent is configured to run as NT AUTHORITY\NetworkService. You will need to identify the user that runs SQL Server Agent on your system. To do this, open the Services administrative tool, right click on the SQL Server Agent (MSSQLSERVER) service, and choose properties. Click on the Log On tab, and note the user:


I gave this account the sysadmin server role in SQL Server. To do this, open SQL Server Management Studio and log in to the database engine. Drill down into Security, and then Logins. Right click on the user that runs SQL Server Agent, and choose properties. Under Select a page in the top left corner of the properties window, click on Server Roles. Enable the sysadmin role by clicking on the check box in the Server Roles pane, and then click on OK.


You should now be able to start SQL Server Agent successfully.

22 comments:

Don said...

This is a good explanation.

Im getting the same error with SQL 2008 Express edition.

Finally found the reason!!

Agent log says...

This installation of SQL Server Agent is disabled. The edition of SQL Server that installed this service does not support SQL Server Agent.

Cheers
-- Eranga Priyakara

David said...

Spot on Don. Great little piece of advice - thanks heaps !

Alex said...

For work with sql files advise use-repairing sql server,tool helped me not once and as far as i know it is free,program can help with this problem and retrieve the data, that was considered to be lost,mwill extract housekeeping data from the source database and preview the data, that can be recovered,this tool is a good solution to recover data from corrupted databases in MS SQL Server format,restore databases represent files, like any other documents, they can be easily corrupted by viruses, all sorts of malware, hard drive failures, file system errors, incorrect user actions, etc,supports both data extraction to your hard drive as scripts in SQL format and data export directly to a database in MS SQL Server format.

rajat said...

Looking for this post very badly.
Thanks a lot .

VoonChong said...

I have got the same problem in SQL 2008 Express edition and I have check my settings. the settings is already as what you describe here.

i noticed that :
1. Not able to start SQL Agent when SQL server is started
2. trigger SQL Agent to start will start SQL server also, however, the started SQL Agent will then stopped automatically after SQL server is started.

could it be SQL agent service is disabled for SQLExpress 2008?
any comment?

Alexis said...

To my mind sql files are very important in my PC.Because I work very often with it.And there is tool which works with it very good-mdf repair.It is free as far as I know,besides that it helped me twice.In addition program can too compatible with all supported operating systems, designed by Microsof.

krrish said...

Hi VoonChong,

SQL Server Express edition does not come up with SQL Server agent..

bastianonm said...

this solution in not valid for win 7 x64 and sql server 2008 sp1..

saroj said...

Really appreciate..

Thanks a lot.

Scott G said...

Actually, I think the Agent is expected to run as the Local System Account. This already is already a member of the sysadmin role.

Thanks for pointing me in the right direction.

Javi said...

Thanks for this. Very helpful!

Sevina said...

I was struggling to start SQL Server Agent for past 2 days browsing thru blogs and, finally, I resolved this issue thank to your your posting!
I simply checked the check box for 'sysadmin' role in SQL Server Agent Property window and it worked fine!
Thanks a lot!

shawn said...

double-check that you are trying to connect to the correct db type (SQL vs SQL Express. When you create a new database and you select server in drop down, type \SQLExpress after server name. If db is created then you are using SQLExpress and so can ignore disabled agent service as not required.

கணேசன் said...

Thanks..Setting the sysadmin role for SQL agent logon account has worked. thanks a lot for this post.

Engr Muzammal Naseer Janjua said...

Excellent work..

Thanks alot for the help

Shashi said...

I had the same settings in my sql 2012. but this solution didnt help

Admin said...

Thank you VV Much for this explanation

sp_sqlagent_update_agent_xps

Minh Ngô Hoàng said...
This comment has been removed by the author.
Minh Ngô Hoàng said...

Seo tool: http://iclick.skyit/vn

senasana said...

thanks, you save my life :D

Basem Saabneh said...

Solution
go to control panel , administrative tools , serivces
or
start - run
write services.msc
and
search for Error Reporting Service - double click and set startup type to automatic , and start it
search for Event Log - double click and set startup type to automatic , and start it

Felix Hernandez said...

OMG. I looking for this answer for a lot time. Thanks bro.