join the MSSQLTips community

MSSQLTips.com - your daily source for SQL Server tips

Google
 
Web mssqltips.com

 
Best Practice - Increasing the Number of SQL Server Error Logs - MSSQLTips

MSSQLTips

MSSQLTips.com - your daily source for SQL Server tips
Welcome to MSSQLTips Sign in | Join | Help
in Search

Best Practice - Increasing the Number of SQL Server Error Logs

Last post 01-25-2010 3:45 PM by Antoine. 5 replies.
Page 1 of 1 (6 items)
Sort Posts: Previous Next
  • 09-11-2009 12:30 AM

    Best Practice - Increasing the Number of SQL Server Error Logs

    This post is related to this tip: Best Practice - Increasing the Number of SQL Server Error Logs

    http://www.mssqltips.com/tip.asp?tip=1835

  • 09-11-2009 5:38 AM In reply to

    • mharr
    • Top 75 Contributor
    • Joined on 10-23-2008
    • Posts 7

    Re: Best Practice - Increasing the Number of SQL Server Error Logs

    A good posting.  However, I would suggest taking it a step (or two) further.

    For my standard practice, in addition to scheduling the sp_cycle_errorlog to run every night at midnight, I also increase the number of logs to 30, so that I have 1 month of log files to be available.  I do not have any empirical evidence to suggest 30 is better than 10, it just seemed a decent number to keep.

    I also suggest that recycling the errorlog can increase performance.  In my travails as a consultant, I will often come across a client complaining of SQL Server performance issues.  Naturally, one of the first things to do is check the error log, and I will occasionally see errorlogs reach gigs in size.  Trying to open the file will take forever, so I run the sp_cycle_errorlog sysproc to cycle the file and copy it to my local system.  Almost immediatly, I will find a modest performance boost.  Usually there are more issues to take care of, and I don't claim it to be a best optimizer solution.  But I suspect that if we have a slow, long time opening the reading the errorlog file, SQL Server probably has a slow time opening the file to write to it.  I suspect that keeping the current file to a reasonable size helps to keep SQL Server running efficiently.

  • 09-13-2009 11:29 AM In reply to

    Re: Best Practice - Increasing the Number of SQL Server Error Logs

     A typical recommendation is to archive the SQL Server error logs (or any other error logs for that matter) in a database. You can use your own scripting methodology to do this or you can use LogParser with DTS or SSIS to import the data from the logs to the target database

  • 01-25-2010 3:09 PM In reply to

    Re: Best Practice - Increasing the Number of SQL Server Error Logs

    Very helpfull tip. 

    Is is possible to elaborate on the same concept for the agent log?  Is there a setting defining the quantity of agent log files or is it handled by other properties?

     Thanks.

  • 01-25-2010 3:19 PM In reply to

    • mharr
    • Top 75 Contributor
    • Joined on 10-23-2008
    • Posts 7

    Re: Best Practice - Increasing the Number of SQL Server Error Logs

    Yes. Use the msdb.dbo.sp_cycle_agent_errorlog procedure for Agent logs.
  • 01-25-2010 3:45 PM In reply to

    Re: Best Practice - Increasing the Number of SQL Server Error Logs

     Thanks Mharr,

    sp_cycle_agent_errorlog opens a new current log while pushing the previous one to Archive #1.  The problem is the Archive #9 is pushed out by the by the Archive #8.  Can I change the sql server settings to hold more than 9 archive files for the sql agent log?

     I'd like to run sp_cycle_agent_errorlog on a regular basis.  But before doing so, I'd like to increase the amount of agent log files.  Is this feasible?

Page 1 of 1 (6 items)