<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Moving system databases: sqlserverAgent fail to start: Error 435

Published on
10,969 Points
4,969 Views
Last Modified:
Approved
mordi
Senior Oracle and SQL Server DBA for the last 20 years.
Database Administrator in a wide variety of business applications, Platforms and OS
The steps for moving the system databases to a new location are documented in the following technical article:
http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx.

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: ResUtilsStartResourceService failed (status 435)
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\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.<instance number>\SQLServerAgent\ErrorLogfile
Registry = [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.<instance number>\SQLServerAgent\WorkingDirectory

After registry manual update  you can restart the sql server agent service and it will work
0
Comment
Author:mordi
0 Comments

Featured Post

CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month