The job of a consultant is always interesting – sometimes one becomes very busy and at times, over busy. I have been overwhelmed with recent performance tuning engagements. In one of the recent engagements, a large number of errors were found in the server. I noticed that their error log filled up very quickly. I also noticed a very interesting action by their DBA. I observed that after we make some changes in the server to avoid the errors, the DBA restarted the server. I asked him the reason for doing so. He explained every time that when he restarts the server, a new error log file is created. The current log file is renamed as errorlog.1; errorlog.1 becomes errorlog.2, and in a similar way, it continues. This way, after making some change, we can watch the error file from the beginning.
However, there is no need to restart the server to create a new log file or recycle the log file. You can run sp_cycle_errorlog and achieve the same result. Here is an example of what I am suggesting.
Before sp_cycle_errorlog
Executing sp_cycle_errorlog
EXEC sp_cycle_errorlog GO
After sp_cycle_errorlog
You can also create a new log for the agent in the same way after running sp_cycle_agent_errorlog.
Do you use this feature on your production server? I am interested to learn about this.
Reference: Pinal Dave (https://blog.sqlauthority.com)
