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)
21 Comments. Leave new
Thanks Pinal but what if I want to get red of all old error log files. The reason you know that error log filled very quickly and consume a lot of space.
Can you tell me how to do it.
Hi Pinal,
I have used many time in production server when error log very full and consume very space in drive over in GB.
Good Stuff , Simple things we tend to over see
Thanks Dave
Its quite interesting dave… Thanks for this knowledge…
Pinal Dave,
Please suggest on some software presentation tools or utilities which you use most.
As part of my weekly maintenance, I run the sp_recycle_errorlog. I have also increased the number of errorlogs from the default of 6, to 26. So, once a week, I get a new log, and anything older than 26 weeks is deleted. Of course a server reboot gives you a new one, so that counts against my 26, but you can adjust to what you need by right clicking on SQL Server logs, and select the Configure option to set the amount of logs you want. I suppose you could recycle the logs every night or once a month, depending on your needs. Also, see sp_cycle_agent_errorlog to recycle the agent errorlog
Hi Dave,
I am using this feature but in a different way.
Instead of EXEC sp_cycle_errorlog I run a DBCC command to cycle the Error Log.:
Exec (‘DBCC ErrorLog’)
Actually I created a monthly job that will re-create the SQL Log file. This way I avoid filling the log file up to a huge size being very hard to use it.
In our system
We take full backups daily and Transaction log updates every 15 minutes or up to 96 in a day. Each one is logged in the log file so I developed the following best practices.
1. Set the number of log files to 18 from the default of 6 to do this:
a. SQL 2005 – Right click on SQL Server logs folder in Managment studio and choose Configure from the pop up menu.
b. Click the Check box Limit the number of errorlogs before they are recyled and we set it to 18 to allow us to keep potentially useful data if we have to do multiple re-starts.
c. Click Ok
2. Create a SQL Job that executes
EXEC sp_cycle_errorlog
on a daily basis.
Tahir, to get rid of the old logs, you can just keep running EXEC sp_cycle_errorlog and they will go away, but if you follow the above best practices you might not need to do that.
Brian M.
Hi Pinal,
Is “SQL Server” allow users to connect to the database server during recycling process?
i had created the job previously but now some errors are comes log failed. what is the reason for this error
Hi Pinal,
I want a procedure that will insert data into Log table.
Log table structure :
Id, LogMessage, ErrorProcedureName, ErrorMessage,InsertingRowNumber, AddedOn
Condition :
If any error come while inserting row into Base table, then this row should be added into the log table with log error msg but not in Base table.
If no error come then insert row into Base table and one sucessfully msg insert into log table.
Please help me out.
Regards
[email removed]
Dear Pinal ,
This is rajendiran, i am novice to sql server dba. apart from this concept .i have tremendous doubt in mirroring concept could u clarify it. i configured A,B and C three server i mean principal , mirror and witness . if i take manual log backup on principal server with Truncate_only option in 2005 . it will delete or not without sending to mirror server…please reveal me [email removed]
Thanks,
M.Rajendiran
Thanks ,This is very helpful.
I wish someone had mentioned that sp_cycle_agent_errorlog is in the msdb system stored procedures. I guess everone knows sp_cycle_errorlog is in master.
Is there any harm to delete
ErrorLog.1
ErrorLog.2
ErrorLog.3
ErrorLog.4
ErrorLog.5
ErrorLog.6
Can i delete these file as these have occupied a lot of space
Hi Dave,
Is there any way that I could shrink the file size of the Error Log. I do not want to create a new error log at the same time. I have scripts to minimize the size of Transaction Log. Could you please provide a solution in a similar way for Error Log as that of Transaction Log.
Thanks.
helped. thank you.
I am glad @kushannshah
@PD
This helped me out. I haven’t used this function before, but want to start a new error log file when turning on the traces for deadlock troubleshooting.
Hi Pinal,
I have used this sp_cycle_errorlog twice in alwayson environment.
But i have noticed that after using this, automatic failover and failback occurs with a lease timeout error.
Prior to running this, failover has never occurred automatically. But twice in 2 months i have recycled error log with this sp and failover failback occured automatically after that.
Kindly help me with this. want to know if this sp is causing the issue.
Hi Pinal,
Recently, observed a very unusual issue with one if db sever. Issue was server had High CPU and memory and in the Task manager we could see the nagios script.ps1 the culprit. There were multiple instances of check_logfiles.exe which was trying to scan the sql error log.
But we found the error log was 20gb and as per Sql there might be a lock on this error log. They are unable to identify where the issue is. Please share your thoughts