Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to start SQL Server Agent when Agent XPs show disabled


By:   |   Read Comments (42)   |   Related Tips: 1 | 2 | More > SQL Server Agent

Attend these FREE SQL Server 2017 webcasts >> click to register


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


Last Update:


signup button

next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 

 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

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

 


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

 

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


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

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 09, 2016 - 4:35:05 PM - Jeremy Back To Top

 

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

 

Have a good day =-)


Friday, April 01, 2016 - 4:31:33 AM - Joe Lehane Back To Top

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 06, 2015 - 4:42:59 PM - Christian Back To Top

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


Friday, October 02, 2015 - 8:05:04 PM - Bharat Back To Top

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

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

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

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

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


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

Worked perfectly.  Thanks!


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

Worked a treat. Thank you


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

Awesome. Exactly what I was looking for.


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

realy vary usefull content.

your solution fixed my problem.

 

Thanks  


Friday, August 08, 2014 - 6:17:31 AM - siva Back To Top

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

 

not highted start option in sqlserver agent 


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

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

very nice post...

Thanks.


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

 

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

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

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

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

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 07, 2013 - 3:19:46 AM - Shahid Back To Top

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


Wednesday, February 06, 2013 - 9:57:42 PM - Henry Back To Top

you are the man, it works.

 


Monday, October 01, 2012 - 12:29:48 PM - Ryan Back To Top

You rock! This worked perfectly. Thanks for sharing!


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

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 03, 2012 - 3:11:17 PM - Jon Morisi Back To Top

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

 

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

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

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

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

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

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

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

 

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

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

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

Manvendra, Again one more nice article from you.

Thanks,

Prasad


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

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


Learn more about SQL Server tools