SQL Server Agent Quick Tips

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

1. In Object Explorer, click the plus sign to expand the server that contains the SQL Server Agent error log that you want to view.
2. Click the plus sign to expand SQL Server Agent.
3. Click the plus sign to expand the Error Logs folder.
4. 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

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

8. Errors While starting SQL server agent

StartServiceCtrlDispatcher failed (error 0).

SQL Server Agent Not Starting -- FIX