By: Manvendra Singh |Comments (51) | 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.
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'
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',1GORECONFIGUREGOEXEC SP_CONFIGURE 'show advanced options'
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.
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',1GORECONFIGURE
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.
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
About the author
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
Wednesday, May 29, 2024 - 6:42:51 AM - MoE | Back To Top (92274) |
What a clean walktrough. if only everyone could write instructions like that <3 |
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 myStep 3 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 afresh 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 yourreconfiguration 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 Agentfrom SSMS. The error went away. I hope somebody finds that useful. :) Thanks for the writeup! People like you makes administrating SQLpossible. |
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 - 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 - 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 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 accidentlyto 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 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 |