Home » CodeProject » How to find/modify SQLServer Agent logfile location?

How to find/modify SQLServer Agent logfile location?

1. To get the location of SQLServer Agent log file, the log file is called SQLAGENT.out

DECLARE @AGENT_ERRORLOG NVARCHAR(255)

EXECUTE MASTER.DBO.XP_INSTANCE_REGREAD N’HKEY_LOCAL_MACHINE’,

N’SOFTWAREMICROSOFTMSSQLSERVERSQLSERVERAGENT’,

N’ERRORLOGFILE’,

@AGENT_ERRORLOG OUTPUT,

N’NO_OUTPUT’

SELECT @@SERVERNAME SERVERNAME, @AGENT_ERRORLOG AGENTERRORLOGLOCATION

This command will work for both default & named instance.

2. To modify location and name of SQLServer Agent log file

USE [MSDB]

GO

EXEC MSDB.DBO.SP_SET_SQLAGENT_PROPERTIES @ERRORLOG_FILE=N’C:TEMPSQLAGENT.OUT’

GO

3. To recycle SQLServer Agent log file

EXEC MSDB.DBO.SP_CYCLE_AGENT_ERRORLOG

When SQLServer Agent recycles the log file, SQLAGENT.out will be SQLAGENT.1 & SQLAGENT.1 will be SQLAGENT.2 and so on.

If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)


2 Comments

  1. Dushyant says:

    Hello,
    I’ve an issue with my SQL Server Agent it’s saying (Agent Xps disabled) I tried lot of way but still not able to start…

    I used your 1st step query to find out the log file is called SQLAGENT.out and returning with my server name on servename column and NULL in agenterrorloglocation.. Can you guide me what to do on my issue?

Leave a Reply

Join us on Facebook

microsoftcommunitycontributor
%d bloggers like this: