SQL Server – Agent Shows Disabled After Migration

migrationsql serversql-server-2008-r2sql-server-agent

I am migrating our old server to a more reliable and powerful new server. SQL Server edition and build number stays the same (SQL Server 2008 R2 Standard). For this I followed the steps given in this article. This is what I have done so far in summary:

  1. Backed up the master db of the old machine,

  2. Noted down the system db's data and log files' paths on the old server and created them on the new one,

  3. Stopped the SQL services on the old machine and copied the data and log files of msdb and model db's of the old machine over to the new one and placed them exactly on their original paths (drive letters are same as well),
  4. Started SQL Server on the new machine in single user mode and restored the backup of old machine's master db.
  5. Backed up the service master key file on the old machine and restored on the new one in order for the linked servers to work,
  6. Changed the server name of the new machine in system tables to the old one.

Everything looks like fine except a glitch. SQL Server Agent in the SSMS shows as "Agent XPs disabled". However, I can confirm that it is not. Agent service can successfully be started and run. It is whenever the agent service is stopped that the run_value of sp_configure 'Agent XPs'changes to 0. Whenever the agent service is started, it is set automatically back to 1.

What might be causing this and does it pose a risk?
Thank you

Best Answer

It is whenever the agent service is stopped that the run_value of sp_configure 'Agent XPs'changes to 0. Whenever the agent service is started, it is set automatically back to 1.

This is expected behaviour. When you STOP sql server agent, the congiguration option Agent XPs changes the config_value and run_value to 0. When you start sql agent, then the config_value and run_value for Agent XPs changes back to 1.

It is important to run reconfigure with override when you enable the configuration option for the first time using sp_configure 'Agent XPs', 1

enter image description here

Related Question