How to start SQL Server Agent when Agent XPs show disabled

By:   |   Comments (50)   |   Related: 1 | 2 | > SQL Server Agent


Problem

The other day we found that SQL Server Agent was stopped with the following message in SSMS "SQL Server Agent (Agent XPs disabled)" for one of our SQL Server instances, but the service was running according to the services console.  I tried to start the service from SQL Server Management Studio, but this did not work.  What is the issue and how do I fix this?

Solution

The other day I changed an implementation on one of our SQL Server instances. After the change, I started all of the SQL Server services from the services.msc console and everything started successfully. But when I launched SQL Server Management Studio, SQL Server Agent showed that is was not running with 'Agent XPs disabled' message as shown below. I tried to restart the service from SSMS, but it did not work. The interesting thing was that the service showed as 'started' when I was looking at it in the services.msc console.

SQL Agent was stopped with Agent XPs disabled

As SQL Server Agent was not running in SSMS, we were not able to access any of the contents like jobs, error logs, etc... 

It appears, that this issue appears when 'Agent XPs' advance configuration option is disabled and set to 0 for the SQL Server configuration..

Agent XPs is an advanced configuration option which enables the SQL Server Agent extended stored procedures on the server.  When this is not enable SQL Server Agent will not be active in SSMS. Most of the time when you start the SQL Server services it automatically enables 'Agent XPs', but sometime it fails to enable or sets the value to 0 and then this issue will appear.

To fix this issue we should first set the 'Agent XPs' to 1 and then run RECONFIGURE to bring it into effect.

Step 1. 
Run sp_configure to check 'Agent XPs' value.

EXEC SP_CONFIGURE 'Agent XPs'

Run SP_configure to check Agent XPs value

Step 2
The above screenshot shows that advanced options is not enabled on this instance, so we must first enable advanced option to see all of the advanced configuration values.

EXEC SP_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE 'show advanced options'

Enable all advanced options in sp_configure

You can see 'show advanced options' is set to 1 this means that advanced options are enabled and we can see and change the values.

Step 3
Run sp_configure again to check the value for Agent XPs. Here we can see the run value is set to 0.

check_Agent_XPs value

Now we need to change this setting from 0 to 1 to run SQL Server Agent in SQL Server Management Studio.

EXEC SP_CONFIGURE 'Agent XPs',1
GO
RECONFIGURE

Enable Agent XPs advanced options in sp_configure

Step 4
Now restart your SQL Server Agent service from SQL Server Configuration Manager. This time the service should come up and we can successfully access all of the content for SQL Server Agent.

SQL Server agent started
Next Steps
  • Always use SQL Server Configuration Manager to start SQL Server services
  • If you get this message in SQL Server, check the configuration settings to enable the Agent XPs
  • Read the additional articles on SQL Server Agent


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, July 3, 2020 - 9:49:16 PM - NARENDRA Back To Top (86086)

Thanks, it was useful for me

Sunday, June 28, 2020 - 9:05:49 PM - Jorge Bojorquez Back To Top (86063)

I tried all the instructions, but it failed. The service started and stopped. Then i found:

SQL Server Agent has to log on as Local System Account. Open up services console (start > run > type services.msc) and locate "SQL Server Agent" service and double click on it. Go to "Log On" tab and ensure "Local System Account" is selected. After confirming, you should be able to start this service. You can also use "net start SQLAgent$SQLEXPRESS" command.

So I changed the logon account to "Local System" and it worked.

Friday, May 31, 2019 - 6:13:11 AM - DanB Back To Top (81272)

Thank you. This was very helpful

Tuesday, February 19, 2019 - 5:26:04 AM - ToVa Back To Top (79060)

 Hi,

I installed SQL server with default settings and with all components, just changed path for database.

Then I relaized, that I need to change the instance name (which is not possible). I uninstalled "database", but leave all other components as is.

And installed database/instance back with correct name (but to the same existing destination).

Everything was ok, but only SQLAgent wont run.

The problem looks like yours, because Agent XPs show disabled nad your solution helps only a bit. (After showing properties of SQLAgent in SSMS and clicking OK, the icon changed to "undefined state" and "Agent XPs .." was cleared. But SQLAgent still did not run anad every start changed state to "Agent XPs.." back

But the problem was logfile. The logfile already exists and was created by previous SQLAgent$INSTANCE user. The new user (after reinstal instance with correct name) was unable to modify SQLAGENT.OUT file.

The solution is just delete this file. (I dont need them, it was created by previous SQL server which I uninstalled.) Than you can start the service. It is better to restart SSMS.

T. 

Thursday, May 31, 2018 - 1:09:28 PM - Ashok Back To Top (76064)

Great, Thank you so much it helps a lot.

Monday, February 12, 2018 - 7:15:36 AM - ayca Back To Top (75184)

 Thank you so much!!! I have been looking for a solution for 1 day, finally I fixed the problem! 

 

Friday, December 8, 2017 - 11:47:05 AM - Eddie Back To Top (73778)

We have a 2 node cluster Windows 2012.

We have already an existing instance that is working perfecly - SQL 2014

We are now adding a brand new second instance of - SQL 2014

The Microsoft SQL server 2014 setup is in progress and towards the end of the setup a pop up window appears with the following message:

 The following error has occurred:

The cluster resource 'SQL Server Agent (CTRM)' could not be brought online.  Error: There was a failure to call cluster code from a provider. Exception message: Generic failure . Status code: 5023. Description: The group or resource is not in the correct state to perform the requested operation.
.

Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.

 

Looking at the Windows services: SQL is running

Looking at the Failover Cluster Manager > Roles > SQL server status is Failed

Looking at the > Other Resources > SQL Server Agent status is failed, I tried to Bring it Online but keep failing.

 

Friday, November 24, 2017 - 7:18:51 PM - afun monica Back To Top (70179)

please my Step 3
Run sp_configure again to check the value for Agent XPs. Here we can see the run value is set to 0

my run value still remains 1,what do i do.

Tuesday, September 26, 2017 - 1:10:01 PM - Ildephonse Back To Top (66629)

 

 I case this error persists and you are doing a fresh installation or re-installation, check permission of the service account at the folder level where your .mdf, .ldf and backup files are stored. Steps would be set permission, then configure the XPs to 1.

Tuesday, April 11, 2017 - 6:11:29 AM - Fatou Back To Top (54648)

 Great,, this saved my day! Realy thanks :)

 

Monday, December 12, 2016 - 8:36:31 PM - Andy Back To Top (44952)

 

Nice man, thanks for this solution. It help me so much.

Wednesday, September 14, 2016 - 3:23:17 PM - James Back To Top (43320)

Minor followup (I know this is old, but I hope this helps because the problem obviously still happens).

I ran your reconfiguration scripts (side note: make sure they are ran using the master database (put "USE master" without the quotes above the script) and restarted it from SSMS, and it still came up with (Agent XPs Disabled). After trying a few things that didn't work I stopped the service (SQL Server Agent (MSSQLSERVER)) from the Services MMC and then restarted the SQL Server Agent from SSMS. The error went away.

I hope somebody finds that useful. :)

Thanks for the writeup! People like you makes administrating SQL possible.

Saturday, July 9, 2016 - 4:35:05 PM - Jeremy Back To Top (41851)

 

 I just wanted to say thanks because it's been helping me !

 

Have a good day =-)

Friday, April 1, 2016 - 4:31:33 AM - Joe Lehane Back To Top (41113)

This explained all about how to start it and now all is good.

My only question now is "What is SQL Server Agent?"

Friday, November 6, 2015 - 4:42:59 PM - Christian Back To Top (39038)

you can also just go to Windows services and start the Agent service.

Friday, October 2, 2015 - 8:05:04 PM - Bharat Back To Top (38818)

That was a complete magic,

I have very little idea about SQL but in my lab i was facing this issue and this magic was great and resolved the issue.

Thanks a ton.

Sunday, September 13, 2015 - 5:03:28 AM - Jay Back To Top (38665)

Hello Manavendra,

Thanks for this simple write up. I am not an SQL admin but had to look at this error in an urgent situation where a DBA was not available and your steps made the fix in 2 minutes.

Thanks

Jay

Wednesday, July 29, 2015 - 2:49:13 PM - Nitin Naik Back To Top (38316)

I can not start SQL Server Agent service in sql server 2012 enterprise edition

 

Following messages in windows App. Log

 

. Windows detected your registry file is still in use by other applications or services. The file will be unloaded now.

 

The applications or services that hold your registry file may not function properly afterwards. No user action is required. 

 DETAIL -
 1 user registry handles leaked from \Registry\User\S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430_Classes:
Process 736 (\Device\HarddiskVolume2\Windows\System32\svchost.exe) has opened key \REGISTRY\USER\S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430_CLASSES

    

Windows detected your registry file is still in use by other applications or services. The file will be unloaded now. The applications or services that hold your registry file may not function properly afterwards. No user action is required. 

 DETAIL -
 1 user registry handles leaked from \Registry\User\S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430:
Process 736 (\Device\HarddiskVolume2\Windows\System32\svchost.exe) has opened key \REGISTRY\USER\S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430

 

 

Please help me via e-mail so that I can start my sql server agent service

Friday, July 17, 2015 - 5:57:09 AM - ruchi Back To Top (38231)

I am trying to take backup of  my database but its not working .... lemme  know if this problem arises due to sqlserver agent disabled.

Query is executing but .bak file is not created. will u please tell me the solution....

Tuesday, April 28, 2015 - 12:13:28 PM - Juan Back To Top (37052)

SERIOUSLY!!! THANK YOU !!!  Extremely easy to follow. Your the best!

Thursday, April 23, 2015 - 7:11:59 AM - Kelly Back To Top (37006)

Worked perfectly.  Thanks!

Thursday, April 23, 2015 - 6:56:24 AM - Neal Back To Top (37005)

Worked a treat. Thank you

Wednesday, March 25, 2015 - 11:53:58 AM - Pauly Walnuts Back To Top (36687)

Awesome. Exactly what I was looking for.

Saturday, August 30, 2014 - 8:00:40 AM - anish Back To Top (34333)

realy vary usefull content.

your solution fixed my problem.

 

Thanks  

Friday, August 8, 2014 - 6:17:31 AM - siva Back To Top (34058)

Hi All

 

The above option was available in SQL SERVER 2012 or not ?

As per my knowledge in 2014 This option was not available if i m wrong please correct me.

Friday, February 14, 2014 - 2:53:05 PM - balakrishna Back To Top (29459)

 

not highted start option in sqlserver agent 

Sunday, January 19, 2014 - 12:11:15 AM - Abdirahaman Back To Top (28134)

Thank You for your valuable effort

this post helped and every thing is ok 


 thank you again & again .....................

Tuesday, August 20, 2013 - 2:31:59 AM - shafiq Back To Top (26383)

very nice post...

Thanks.

Friday, June 28, 2013 - 4:38:51 PM - INS Back To Top (25621)

 

I had same issue after bounced SQL instance. The only reason i have to bounced this DR instance because there were couple sesssion were hung as killed/Rollback. To clear these sessions I had to restart SQL instance. but SQL agent did not stated. it saw Agent Xp was disabled ... 

 

 

 

sp_configure 'show advanced options',1

 

go

 

reconfigure with override

 

go

 

sp_configure 'Agent XPs',1

 

go

 

reconfigure with override

 

go

 

sp_configure 'show advanced options',0

 

go

 

reconfigure with override

 

go

 

 

 

then again I restarted SQL instance twice with same issue and saw another error 

 

database 4 (MSDB)cannot be autostarted during server shutdown.. 

 

 

 

In my case, I had checked FT service was running and taking most of CPU usage. I killed msftsql.exe from windows task manager and start again from serives. After this exercise it was working fine .. 

 

 

 

In my case, FT services was culprit ... 

 

 

Wednesday, June 19, 2013 - 11:08:54 AM - Harish Back To Top (25488)

Hi All,

I just started my career as DBA, I installed the sql server 2008 R2 on to local machine, but unable to start the SQL server Agent, tried the above steps and many other ways didn’t worked it out.

After I done with the above steps and went to configuration manager and was trying to start the Agent I was timed out.

 

                “The request failed or the service did not respond in a timely fashion.

Consult the event log or other applicable error logs for details “

 

Can someone please help me in sorting out this issue, as I do all my practice on local machine before implementing on to Client machines.

 

 

Thanks,

Friday, June 14, 2013 - 7:35:36 AM - M. van der Heide Back To Top (25435)

Hi,

Found the problem for the '-1' run_value. This value means that the Agent XPs have been permanently disabled. Most of the time this is caused by active traceflag 3608. The following steps worked for us:

Check the traceflag status:

DBCC TRACESTATUS(-1)

If the value of the Status field is set to '1' it means that traceflag 3608 is active, to get the Agent XPs running, we have to switch the traceflag off:

DBCC TRACEOFF(3608,-1)

Also check to see if the traceflag is set on the command line as well (SQL Server Configuration Manager --> Properties of the Instance --> Advanced Tab --> Startup Parameters). If you see that the traceflag is started automatically '/T3608', please remove it.

Next restart the instance.

Now you can follow the steps of the original tip at the top of the page.

Kind Regards,

Marco van der Heide

Friday, June 14, 2013 - 3:02:08 AM - M. van der Heide Back To Top (25432)

Hi,

We have the same problem, but cannot resolve it with running the SP_CONFIGURE statement. Somehow the run_value is set to '-1'.

name          minimum    maximum    config_value    run_value
Agent XPs     0                1                1                   -1

Do you have an idea on how to solve this?

Thanks in advance!

Wednesday, March 20, 2013 - 6:34:42 AM - Neal Back To Top (22894)

At step 4, when I restart the sql server agent, the Agent XP is still disabled and the run time goes back to 0. Any ideas?

 

Thursday, March 7, 2013 - 3:19:46 AM - Shahid Back To Top (22621)

Really nice article at the time when I was in full stress. Thanks Author.

Wednesday, February 6, 2013 - 9:57:42 PM - Henry Back To Top (21957)

you are the man, it works.

 

Monday, October 1, 2012 - 12:29:48 PM - Ryan Back To Top (19757)

You rock! This worked perfectly. Thanks for sharing!

Tuesday, September 18, 2012 - 9:52:57 AM - Mark Arnold Back To Top (19547)

Thank You for sharing. Took me a while to get the right keywords into Google but eventually I got led to you and all is now well!!

Cheers.

Friday, August 3, 2012 - 3:11:17 PM - Jon Morisi Back To Top (18917)

Even with 'Agent XPs' disabled, if you log onto you server in SSMS as a sys admin (sa), you will have the neccessary permissions to see SQL Server Agent in SSMS and do a restart that way.

Tuesday, July 17, 2012 - 2:57:04 AM - Ganesh Back To Top (18564)

 

Please refer to the following KB article on Agent XPs:  http://support.microsoft.com/kb/2163980

Monday, July 16, 2012 - 8:51:04 AM - Tony Back To Top (18540)

I don't mind if you contradict. Yes, clustered systems and services.msc don't go - you're right

Tony

Monday, July 16, 2012 - 7:19:43 AM - Manvendra Back To Top (18539)

Thank You all for your valuable inputs.

As per technet:

Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Other tools such as the Windows Services Control Manager can change the account name but do not change associated settings. If the service cannot access the SQL Server portion of the registry the service may not start properly.

In the similar way when we start the sql server services from configuration manager it also set all required settings whihc is needed for sql server like whihc we manual set in above tip.

Friday, July 13, 2012 - 4:17:46 PM - Vikrant S Patil Back To Top (18505)

and BTW a very good article by Manvedra, Thanks for sharing.

Friday, July 13, 2012 - 4:16:04 PM - Vikrant S Patil Back To Top (18504)

My objective is not to contradict anything else that has been mentioned earlier in the various comments but from my perspective you should altogether avoid using services.msc for starting and stopping SQL Services and configuring them as well

This is because using services.msc accidently to star\ stop a SQL service on a SQL server cluster can end up in a major disaster.

Therefore it would be a good idea to not even lurk near the services applet when it comes to SQL Server services, especially for folks who work with SQL clusters, 

There is nothing that you cannot do without services.msc as we all know the rules change when it comes to a SQL Cluster.

 

 

Friday, July 13, 2012 - 4:06:41 AM - Tony Back To Top (18492)

I was essentially trying to move the emphasis away from the stopping and starting of the SQL Server services needing Configuration Manager.

From a Windows perspective the SQL Server services are no more special than any other Windows service. There is nothing detrimental with respect to SQL Server by stopping and starting any Windows services using the Windows services.msc applet. The link in the last post said the 'proper way' is to use config manager. I'm not sure what is meant by 'proper' but it has no special meaning from a Service perspective. The SQL Server services don't know how they were stopped or started, or what tool was used. You're right. It does make sense to do all maintenance with one tool - Configuration Manager - but it doesn't mean that one should *deliberately* avoid using other methods if *all* you're doing is starting/stopping the services.

However, when making changes to the SQL Server service accounts Configuration Manager is the officially stated way to do it because using other tools can lead to negative ramification to SQL Server.

HTH

Tony

Thursday, July 12, 2012 - 11:36:16 AM - Steve Vassallo Back To Top (18477)

Tony makes sense and I was aware of using config manager when changing accounts etc.. though someone else shared my training experience here

http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/ce6ae7ea-5db4-4b17-a98d-e4f40ce76111/

But I will still think any maintenance etc you are doing.. you should be starting and stopping via Config manager.

Thursday, July 12, 2012 - 9:25:58 AM - Ameena Back To Top (18474)

 

I received the similar error when our storage admin move the data disk to SAN. Everything else worked fine without any hitch. In sql server log only message I found related is this “Attempting to load library 'xpsqlbot.dll' into memory”. I don't understand what is the actual cause of it?

 

Thursday, July 12, 2012 - 9:03:43 AM - Tony Back To Top (18472)

The instructor should have explained that the issue is not about starting and stopping the services per se but about having the permissions properly set for any changes to the SQL Server Service account. There is no issue for example stopping and starting them from say the command prompt or as part of a maintenance batch process.

Configuration Manager properly sets the usage permissions for the SQL Server service account while the services.msc applet does not.

Tony

Thursday, July 12, 2012 - 8:50:03 AM - Steve Vassallo Back To Top (18471)

Just completed SQL 2012 Admin course and the instructor was adament about never starting and stopping SQL services via the services.msc.   You should always use SQL Configuration Manager.   This was also true going back to SQL 2005.  So the question begs.. why were you starting anything for SQL via the Services.msc?

Steve

 

Thursday, July 12, 2012 - 3:06:33 AM - Prasad Back To Top (18461)

Manvendra, Again one more nice article from you.

Thanks,

Prasad

Thursday, July 12, 2012 - 2:24:13 AM - Plahanov Back To Top (18459)

thankx friend,

the same problem happend to me also. Sql server workign perfectly on the server system but cannot access it from the network systems. 

Plahanov














get free sql tips
agree to terms