To get the location of SQLServer Agent log file, the log file is called SQLAGENT.out
DECLARE @oem_errorlog nvarchar(255)
EXECUTE master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’,
N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’,
N’ErrorLogFile’,
@oem_errorlog OUTPUT,
N’no_output’
PRINT @oem_errorlog
Sample Output: C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT
To modify location and name of SQLServer Agent logfile
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N’C:\temp\SQLAGENT.OUT’
GO
To recycle SQLServer Agent logfile
EXEC msdb.dbo.sp_cycle_agent_errorlog
When SQLServer Agent recycles the logfile, SQLAGENT.out.1 is the 2nd most recent logfile and so on.