A few days ago I had a problem with a database present in SQL Server, restarting the instance and the SQL Server Agent I found myself faced with a fantastic surprise: the SQL Server Agent had been stopped and'Agent XPs disabled. Panic!
I tried to restart the service from SQL Server Management Studio and SQL Server Configuration Manager, but nothing to do.
Without SQL Server Agent it is not possible to access many SQL Server features such as processes, jobs, error logs, etc ... and it seems that this error appears when theconfiguration option advanced 'Agent XPs' is disabled e set to 0 for SQL Server configuration.
The option Agent XPs allows you to enable SQL Server Agent service extended stored procedures on the server. If this option is not enabled, the SQL Server Agent node will not be available in SQL Server Management Studio Object Explorer.
To solve the problem it is necessary set up Agent XPs to 1 and then run the RECONFIGURE to make everything work again.
Here are the steps to follow:
1. We launch sp_configure to check the value of 'Agent XPs'
EXEC SP_CONFIGURE 'Agent XPs'
2. The screenshot above shows that the advanced options are not enabled in this instance, so we must first enable the advanced option to see all the advanced configuration values.
EXEC SP_CONFIGURE 'show advanced options',1 GO RECONFIGURE GO EXEC SP_CONFIGURE 'show advanced options'
As you can see from the figure above Show Advanced Options is set to 1, this means that the advanced options are enabled and we can see them and change their values.
3. We launch again Run sp_configure to check the value of Agent XPs. As we can see the value is set to 0.
EXEC SP_CONFIGURE 'Agent XPs'
Now we have to change the setting from 0 to 1 and launch SQL Server Agent in SQL Server Management Studio.
EXEC SP_CONFIGURE 'Agent XPs',1 GO RECONFIGURE
4. We restart SQL Server Agent from SQL Server Configuration Manager and finally we will be able to access its content.
If you want to deepen the topic take a look at: SQL Server Agent Tips.
