SQLAgent.out and SQLagent error log files has filleup the drive

Recently i faced an issue, SQL Agent files (SQLAgent.out,SQLAgent1,SQlAgent2,SQlAgent3,SQlAgent4,SQlAgent5,SQlAgent6,SQlAgent7) has occupied almost whole C Drive(40 GB), so No one was able to RDP and no memory was available and no users were able to run queries or perfrom any transactions.

Ans:

Please make sure take the backup error log files before if you need this for reference in future.

What is the SQL Server Error Log?

SQL Server maintains its own error logs that contain messages describing informational and error events. The SQL Server error log is a great place to find information about what is happening on your database server. Each SQL Server Error log will have all the information related to failures / errors that has occurred since SQL Server was last restarted or since the last time you have recycled the error logs.

By default, there are six achieved SQL Server Error Logs along with the ERRORLOG which is currently used. However, it is a Best Practice to increase the number of SQL Server Error Logs from the default value of six. In this tip, you will see the steps which you need to follow to increase the number of SQL Server Error Logs.

A new SQL Server error log file will be created when one of two things happens:

1. The SQL Server service is started
2. sp_cycle_errorlog is called

Best Practice – SQL Server Error Log

1. Ensure you Error log directory is backed up regularly, with windows OS backup
2. Increase the number of SQL Server Error Logs from the default value of six.
3. Make a schedule job to Recycle the Error on a specified schedule to ensure, you log size in control as large files takes time to read data
4. Check SQL Server Error log on daily basis as all important / critical messages and warnings are logged in SQL Server Error Log

How to specify number of log files to be maintained by SQL Server

SQL Server Management Studio -> Management -> SQL Server Logs
Right click -> Configure

Same way, we can configure the number of SQL Server Agent Logs too.

SQL Server Management Studio -> SQL Server Agent-> Server Logs
Right click -> Configure

How to Start a New SQL Server Error Log

sp_cycle_errorlog, stored procedure is used to recycle the SQL Server error log

How to Start a New SQL Server Agent Log

USE MSDB
GO
EXEC dbo.sp_cycle_agent_errorlog
GO

OR, Agent Log can be cycle graphically also

Notes

1. By default, SQL Server maintains a minimum of 6 Error Log Files.
2. Each time the SQL Server is restarted, the Current Active Log File is recycled and new one created.
3. The Error Log Files are stored in the “Microsoft SQL Server\MSSQL.1\MSSQL\LOG\” Directory.
4. It stores, security related login information, Logins info such as Failure Logins or Failure and Success Logins
5. Error Logs also stores, changes in Database Settings, Database backup related information; both successful backups and failures are reported.
6. If case SQL Services failed to start, SQL Server Error Log is the first thing, which shuld be looked at to figure out the reason, why its failing.
7. sp_cycle_errorlog, can only be executed by members of sysadmins

 

To automate this process across servers, reference the sp_set_sqlagent_properties system stored procedure.  Below outlines a sample execution.

USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @errorlogging_level=7 GO