Apr
16
2013

Fix: Issue with SQL Agent not coming up due to agent error log path incorrect

SQL Server Agent was unable to come up and no error was being reported (only error we found as “SQL agent services started and then stopped”, nothing was logged in Event viewer as well), hence was unable to identify the actual error with which agent was failing, following is done to fix it,

 

1. Ran following command to read agent error log using query analyzer,

                                EXEC sys.xp_readerrorlog 0,2

                Output was:

Msg 22004, Level 16, State 1, Line 0

xp_readerrorlog() returned error 3, ‘The system cannot find the path specified.’

 

2. Verified SQL Server registry settings and found that value set for SQL Server agent in following two registry keys is incorrect i.e.

                Registry Path:    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent]

               
 

                Registry Key 1: ErrorLogFile

                Value Found:     F:\MSSQL\abcd1234\Sysdb\MSSQL.1\MSSQL\Logs\

                Correct Value is:
G:\MSSQL\abcd1234\Data\SystemDB\

              
 

                Registry Key 2: WorkingDirectory

                Value Found:     F:\MSSQL\abcd1234\Sysdb\MSSQL.1\MSSQL\Jobs\

                Correct Value is:
G:\MSSQL\abcd1234\Data\SystemDB\Jobs\

 


 

3. As there was no Drive with letter F: on the server hence the registry values are updated to Correct Path as mentioned above in order to fix the issue.

4. After correcting registries, SQL Agent Services came up without any issue and all jobs were intact

 

Alternatively to find the error log path, you can also start SQL Server Configuration Manager and go to SQL Server Services, go to SQL Server Agent service, double click it, Go to Advanced tab and check the DUMP Directory location, it is agent error log path, verify if it exists and should have SQLAgent.out file present.

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

2 Comments + Add Comment

  • Thanks for the pointer to the issue. I hit a similar problem with the entry being blank, Agent was running but couldn’t look at it’s properties or browse the log. If you work in an environment which is change control hell and updating the registry is a serious offence you can run:

    USE msdb 
    GO 
    EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N’\SQLAGENT.OUT’ 
    GO

    This updates the registry for you.

  • That should be full path\SQLAGENT.OUT sorry.

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 174 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

May 2019
M T W T F S S
« Nov    
 12345
6789101112
13141516171819
20212223242526
2728293031  

View Post by Categories

%d bloggers like this: