3

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

1

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

  • Thanks Kin. However, if I stop the agent service, SQL Server Agent's tree on SSMS won't expand (because it is in Agent XPs disabled) How can I then view, for example Error Logs or Job Activiy Monitor? Does it become impossible to check the log on SSMS if agent service is stopped or edit the jobs via GUI? – Stackoverflowuser Dec 10 '15 at 15:03
  • 1
    SQLServer logs are in Management->SQL Server Logs. You can check there. – Kin Shah Dec 10 '15 at 15:10
  • So SSMS won't show Job Activity Monitor when the Agent service is stopped? – Stackoverflowuser Dec 10 '15 at 15:15
  • 1
    @Stackoverflowuser no .. since Job Activity Monitor is under SQL Server Agent. But you can use T-sql - DMVs or sp_whoisactive to monitor the job or just query the msdb database for the job status. HTH. – Kin Shah Dec 10 '15 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.