The steps for moving the system databases to a new location are documented in the following technical article:
http://msdn.microsoft.com/
However sometimes after the moving process is finished, though SQL instance started, SQL agent could not. It fails every time it is started .
You may get the following error in the Sql Server's Errorlog file:
Configuration option 'Agent XPs' changed from 0 to 0. Run the RECONFIGURE statement to install.
You may get also no error at all in the Sql Server's Errorlog file though you may get errors logged to the system Event viewer with one of the following texts on them:
a. [sqagtres] OnlineThread: ResUtilsStartResourceServi
b. Error 435
No error was logged to the sqlserverAgent's errorlog file called sqlagent.out which is located in general together with sql servers log in the same directory.
Looking at the dump path of SQL Agent showed in SQL Server Configuration Manager --> SQL Server Agent --> Properties --> Advanced --> Dump Directory, it was referring to the new location. So why the errors aren't logged to the agent's log or to the errorlog files? The solution for this failure is as follows:
Although the dump path of SQL Agent showed in SQL Server Configuration Manager --> SQL Server Agent --> Properties --> Advanced --> Dump Directory, was referring to the new location , but the Registry didn't get updated with this new location and it was pointing to the old location before the actual move of the system databases to new location.
Therefore you must change the following 2 registry keys to new location in order to get your Agent to work:
Registry = [HKEY_LOCAL_MACHINE\SOFTWA
Registry = [HKEY_LOCAL_MACHINE\SOFTWA
After registry manual update you can restart the sql server agent service and it will work
http://msdn.microsoft.com/
However sometimes after the moving process is finished, though SQL instance started, SQL agent could not. It fails every time it is started .
You may get the following error in the Sql Server's Errorlog file:
Configuration option 'Agent XPs' changed from 0 to 0. Run the RECONFIGURE statement to install.
You may get also no error at all in the Sql Server's Errorlog file though you may get errors logged to the system Event viewer with one of the following texts on them:
a. [sqagtres] OnlineThread: ResUtilsStartResourceServi
b. Error 435
No error was logged to the sqlserverAgent's errorlog file called sqlagent.out which is located in general together with sql servers log in the same directory.
Looking at the dump path of SQL Agent showed in SQL Server Configuration Manager --> SQL Server Agent --> Properties --> Advanced --> Dump Directory, it was referring to the new location. So why the errors aren't logged to the agent's log or to the errorlog files? The solution for this failure is as follows:
Although the dump path of SQL Agent showed in SQL Server Configuration Manager --> SQL Server Agent --> Properties --> Advanced --> Dump Directory, was referring to the new location , but the Registry didn't get updated with this new location and it was pointing to the old location before the actual move of the system databases to new location.
Therefore you must change the following 2 registry keys to new location in order to get your Agent to work:
Registry = [HKEY_LOCAL_MACHINE\SOFTWA
Registry = [HKEY_LOCAL_MACHINE\SOFTWA
After registry manual update you can restart the sql server agent service and it will work