How to find/modify SQLServer Agent logfile location?

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s