You are here:Home » SQL Server Problems » Agent XPs Disabled - SQL Server

Agent XPs Disabled - SQL Server

I notified so many times by the Object Explorer when I go to check the SQL Server Agent jobs. The notification is “Agent XPs disabled”.

SQLServerAgent-1

Then I need to run the following queries to enable SQL Server Agent XPs,
EXEC SP_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
GO

EXEC SP_CONFIGURE 'Agent XPs',1
GO
RECONFIGURE
GO

EXEC SP_CONFIGURE 'show advanced options',0
GO
RECONFIGURE
GO
After running above script it will be enabled,

SQLServerAgent-2

I think you were getting this notification many times. So please share me your experience as “Why this SQL Agent XPs disabled sometime automatically after enabling it?”.

4 comments:

  1. Hey Paresh - What version of SQL are you running? Are you sure there are no SQL policies in place the try to conform your servers to a baseline on a scheduled basis?

    ReplyDelete
  2. No, you have it backwards!!

    Don't enable Agent XPs via sp_configure. You need to start the SQL Server Agent and it will enable them itself. The SQL Agent enables this setting when it starts up and disables it when it stops!

    ReplyDelete
  3. Hello DaveDBA,

    I am running SQL server 2008 and SQL server 2011 ctp1.

    ReplyDelete
  4. Check your service is set to automatically start up. If it is set to Manual then you will see the Agent XPs Disabled message every time you restart the computer.

    ReplyDelete