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 (http://blog.SQLAuthority.com)
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]
[...] on Errors: Explanation of TRY…CATCH and ERROR Handling Create New Log file without Server Restart Tips from the SQL Joes 2 Pros Development Series – SQL Server Error Messages I encourage you to [...]
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
[...] on Errors: Explanation of TRY…CATCH and ERROR Handling Create New Log file without Server Restart Tips from the SQL Joes 2 Pros Development Series – SQL Server Error Messages I encourage you to [...]
[...] Recycle Error Log – Create New Log file without Server Restart [...]
Thanks ,This is very helpful.
[...] Recycle Error Log – Create New Log file without a Server Restart During one of the consulting assignments I noticed DBA restarting server to create new log file. This is absolutely not necessary and restarting server might have many other negative impacts. There is a common sp_cycle_errorlog which can do the same task efficiently and properly. Have you ever used this SP or feature? [...]
[...] Recycle Error Log – Create New Log file without Server Restart Once I observed a DBA to restaring the SQL Server when he needed new error log file. This was funny and sad both at the same time. 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. [...]
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.