Here i have shared quick tips to find details or do operations related to sql server agent error log
1. How To Find SQL server Agent Error Log Location using T-SQL
/**************************************/
sp_configure 'show advanced options' ,1
GO
reconfigure with override
GO
sp_configure 'Agent XPs' , 1
GO
reconfigure with override
GO
EXEC msdb..sp_get_sqlagent_properties
GO
/**************************************/
2. How To View SQL Server Agent error log Location using SSMS
- In Object Explorer, click the plus sign to expand the server that contains the SQL Server Agent error log that you want to view.
- Click the plus sign to expand SQL Server Agent.
- Click the plus sign to expand the Error Logs folder.
- Right-click the error log you want to view and select View Agent Log.
3. How To Change \ Modify SQL Server Agent log file location.
/*******************************************************************/
sp_configure 'show advanced options' ,1
go
reconfigure with override
go
sp_configure 'Agent XPs' , 1
go
reconfigure with override
go
EXEC msdb..sp_get_sqlagent_properties
GO
EXEC MSDB.DBO.SP_SET_SQLAGENT_PROPERTIES @ERRORLOG_FILE=N'C:\SQLAGENT.OUT'
GO
EXEC msdb..sp_get_sqlagent_properties
GO
/*******************************************************************/
4. How To Recycle SQL Server Agent Log File
• SQL Server Agent can maintain up to nine SQL Server Agent Error Logs. The currently used SQL Server Agent Error Log will be named SQLAGENT.OUT
• There is no way you can increase this number
• By default, the SQL Server Agent Error log is located in "Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\SQLAGENT.OUT".
Syntax:
EXEC msdb.dbo.sp_cycle_agent_errorlog;
Go
• There is no way you can increase this number
• By default, the SQL Server Agent Error log is located in "Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\SQLAGENT.OUT".
Syntax:
EXEC msdb.dbo.sp_cycle_agent_errorlog;
Go
5. Where to find SQL Server Agent exe (SQLAGENT.EXE \ Binary path)
- Open sql configuration manger
- Goto properties
- See in Binary path
6. How To Start SQL server agent from Command prompt
- Open command prompt as administrator
- Goto location of sql server agent exe (mention above how to find same)
> SQLAGENT.EXE” -i MSSQLSERVER
Here
- SQLAGENT.EXE: exe to start sql agent
- -i: Refrencing to pass instance name
- MSSQLSERVER: is for default instance (for named instance pass name of instance)
7. How to start sql server agent in console and verbose mode
- Open command prompt as administrator
- Goto location of sql server agent exe (mention above how to find same)
> SQLAGENT.EXE” -c -v -i MSSQLSERVER
Here
-C: Stands for Console mode
-v: stands for verbose mode
Here
-C: Stands for Console mode
-v: stands for verbose mode
8. Errors While starting SQL server agent
StartServiceCtrlDispatcher failed (error 0).
SQL Server Agent Not Starting -- FIX
No comments:
Write commentsPlease do not enter spam links