Sql Server Error Logs
When doing troubleshooting of a problem as database administrator, we mostly starts to look at the logs firstly to define the problem and find out where to start.
Error logs contains many details like Sql Server restart time, database recover informations, failed or successul logins, backup failure&success, IO errors and many more things.
In Sql Server , there are logs for Sql Server agent as well as Sql Server Database Engine and many of the job errors can be detected by this logs.
Although catching and keeping every logs to detect problems is good, reading from error logs can take a long time to read if the Sql Server error log is huge. That’s why some adjustments must be made to overcome the it.
Controlling Logs
To prevent having a single huge sql server log, we need to keep it under control and divide it into smaller pieces. This is where the cycling the error log comes into play. When cycling the error log occurs, the current log file is closed and a new log file is created and starts to write into new file.
This cycling occurs in two situation;
- When Sql Server is restarted
- When sp_cycle_errorlog stored procedure is executed
Rotating Sql Server Error Logs
Sql Server Error logs can be rotated periodically by setting up a Sql Server Job that executes below code.
EXEC sp_cycle_errorlogSo that you can have log files in a specified period. (For example weekly)
For Sql Server Agent logs, there is also a stored procedure for the same needs that named as sp_cycle_agent_errorlog. By dividing into smaller pieces of logs will make the finding problem easier.
In some environments, this stored procedure works well but some environments that have a lot of jobs working frequently or run everytime jobs like replication, causes error like below;
SQLServerAgent Error: The process cannot access the file because it is being used by another process. [SQLSTATE 42000] (Error 22022). The step failed.
Limiting the size of history log can be a solution. There should not be a need for rotating the logs if the log history size is enough to catch up errors.
Configuring Logs
By default, Sql Server Database Engine stores up to 6 log files and it can be changeable easily.
To change the number of files, you can follow the below path;
- Open the Management Folder
- Right click on Sql Server Logs and click the configure button
- Click the “Limit the number of error log files before they are recycled” button
- Change number of the log file how much you need.
In sql server agent side, we can configure the limitation size of history log and deleting the agent history.
- Right click on Sql Server Agent and Click “Configure”
- Choose History tab
- Click checkbox “Limit size of job history log”
- Click checkbox “Remove agent history”
- and Change the numbers according to your needs.
Cycling and configuring the log history is easy and and important to follow up.
Hope this post will be helpful.
Feel free to contribute to make my writings get better and useful.
