Search

Monday, March 9, 2015

Modify / Find SQL Server Agent log file

Use below queries to find the location of 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

Use below queries to modify the location and Name of SQLServer Agent log file

USE [MSDB]
GO
EXEC MSDB.DBO.SP_SET_SQLAGENT_PROPERTIES @ERRORLOG_FILE=N'D:TEMPSQLAGENT.OUT'
GO

Use below queries to recycle the SQLServer Agent log file

EXEC MSDB.DBO.SP_CYCLE_AGENT_ERRORLOG

No comments:

Post a Comment