SQL SERVER – Recycle Error Log – Create New Log file without Server Restart

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)

About these ads

21 thoughts on “SQL SERVER – Recycle Error Log – Create New Log file without Server Restart

  1. 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.

    Like

  2. 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

    Like

  3. 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.

    Like

  4. 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.

    Like

  5. 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]

    Like

  6. Pingback: SQL SERVER – T-SQL Errors and Reactions – SQL in Sixty Seconds #002 – Video « SQL Server Journey with SQL Authority

  7. 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

    Like

  8. Pingback: SQL SERVER – T-SQL Errors and Reactions – Demo – SQL in Sixty Seconds #005 – Video « SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – A Quick Look at Logging and Ideas around Logging « SQL Server Journey with SQL Authority

  10. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #002 « SQL Server Journey with SQL Authority

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  12. 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.

    Like

  13. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s