Problem :
SQL Server Agent started but Agent XPs Disabled. What is the issue and how to fix this?
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:
if this query return error
it will show config_value to 0 and run_value to 0
now run
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
Now restart Sql Server Agent and refresh it. you can find all option related to SQl Server Agent
SQL Server Agent started but Agent XPs Disabled. What is the issue and how to fix this?
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'
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'
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
Now restart Sql Server Agent and refresh it. you can find all option related to SQl Server Agent
1 Comments
Launch SSMS and connect to the SQL Server instance where you want to start SQL Server Agent.
ReplyDeleteBest Software In the Object Explorer, connect to the SQL Server instance where SQL Server Agent is installed From the context menu.
thank you for your comment