Post reply

Rolling over SQL Error Logs

  • October 22, 2012 at 1:51 pm

    #264541

    I have a SQL 2008 production server that does not seem to be rolling over its error log (SSMS -> Management -> SQL Server Logs)

    Their is almost a years data in the current log and it has a lot of rows that take a long time to load every time I want to view.

    Is their any way and safe way to force a rollover of this log? The server has definitely been rebooted during the last year.

    thanks

  • October 22, 2012 at 2:04 pm

    #1551628

    EXEC sp_cycle_errorlog ;

    GO

  • October 22, 2012 at 2:19 pm

    #1551633

    thanks for the reply. From your experience this is safe to use and has not caused any issues that you know of?

  • October 22, 2012 at 2:23 pm

    #1551637

    It is a system stored procedure and I've never had any issue executing it to reduce the size of the current error log. You can read more about it if you like.

    http://msdn.microsoft.com/en-us/library/ms182512.aspx

  • October 22, 2012 at 2:44 pm

    #1551643

    For anybody else, as was suggested by the previous poster this works fine.

    Takes a second to run.

  • October 22, 2012 at 3:10 pm

    #1551653

    If you haven't already:

    Include the code in a weekly job, reconfigure Sql Server logs and select an appropriate number like say 20.

    Now you will cycle error log every week and keep 20 weeks worth of logs before they get over written.

  • October 22, 2012 at 4:07 pm

    #1551673

    We include this as a post SQL Server installation configuration by creating a scheduled job that executes the following:

    EXEC sp_cycle_errorlog

    GO

    EXEC sp_cycle_agent_errorlog

    GO

    It runs weekly on every server in our enterprise and the only issue we have ever had is a failure in the scheduled job if the file is open by some other process.

    You can modify the number of files SQL Server retains for the error log by right clicking on the SQL Server Logs folder in SSMS, selecting configure, checking the box next to "Limit the number of error log files before they are recycled" and changing the number from the default of 6 to a value between 6 and 99.

  • October 22, 2012 at 4:15 pm

    #1551676

    thanks for the help. Even after doing this job for a while I am still learning simple things.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply