Post reply

Restricting SQL Server Logs Size

  • August 2, 2007 at 4:44 am

    #117674

    I have noticed under SQL Server Logs that my current log is 16910689 Bytes and takes an age to let me read the log, I want to try and restrict the size of each log file, then create a new log file when it reaches say 4 Mb, anyone know how I can do this?

  • August 2, 2007 at 6:50 am

    #724145

    why r u not using auto shrink?

  • August 2, 2007 at 6:55 am

    #724147

    no, don't know of the option, this is for the SQL server logs not the transaction logs, can you tell me if the option is for these log files and how to enable it.

  • August 2, 2007 at 7:23 am

    #724155

    This is from BOL

    "Every time SQL Server is started, the current error log is renamed to errorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes errorlog.3, and so on. sp_cycle_errorlog enables you to cycle the error log files without stopping and starting the server."

    Scheduling sp_cycle_errorlog to run periodically will reduce the size of each of the files without requiring you to restart SQL Server. If you do this however you should also make sure that you have the appropriate number set for the number of error log files that are saved before they are recycled. Otherwise you run the risk of overwriting logs that you may need to help you troubleshoot problems. This can be done via EM by right clicking on SQL Server Logs and selecting Configure.

    Good Luck.

  • August 2, 2007 at 7:25 am

    #724156

    Thanks you're a star

  • August 3, 2007 at 3:26 am

    #724433

    We have done what #1CoolGuy suggests as part of our standard build...  We set the number of log to 30, and have a job that runs at midnight each day to cycle the Errorlogs.  Even on days where we do server maintenance (which cycles the logs whenever SQL is started) we have the best part of 1 month's Errorlog.  By cycling at midnight it is easy for us to pick the file we need to look at.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 6 posts - 1 through 5 (of 5 total)

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