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/)