<

Moving system databases: sqlserverAgent fail to start: Error 435

Published on
10,868 Points
4,868 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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month