Recent

6/recent/ticker-posts

Header Ads Widget

How to start SQL Server Agent when Agent XPs show disabled

Problem :
SQL Server Agent started but Agent XPs Disabled. What is the issue and how to fix this?

How to start SQL Server Agent when Agent XPs show disabled

Solution:
I started to search for services related to SQL in windows services and started all services but SQL Server Agent when Agent XPs show disabled. I restart my computer too.
As SQL Server Agent is not running in my SQL Server Management Studio. I am not able to create a job as well as not able to access error, log, etc.
after research and googling I found that the Agent Xps advance configuration option is disabled and set to 0 for SQL Server Configuration.

Agent XPs is an advanced configuration that enables the SQL Server Agent extended stored procedure on the server. If this does not enable SQL Agent will not active. Most of the time
it automatically enables 'Agent Xps' but sometimes it fails to enable or set the value to 0

To fix this issue we should first set the Agent XPs to 1 and then run reconfiguration to bring on effect

Steps:

Exec SP_configure 'Agent Xps'
How to start SQL Server Agent when Agent XPs show disabled

if  this query return error

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

it will show config_value to 0 and run_value to 0

now run
Exec SP_configure 'Agent Xps'
How to start SQL Server Agent when Agent XPs show disabled

 Again it will show config_value to 0 and run_value to 0

Now we need to change this setting from 0 to 1 to run Sql Server Agent
for this run
EXEC SP_CONFIGURE 'Agent XPs',1
GO
RECONFIGURE
How to start SQL Server Agent when Agent XPs show disabled


Now restart Sql Server Agent and refresh it. you can find all option related to SQl Server Agent


How to start SQL Server Agent when Agent XPs show disabled

Post a comment

0 Comments