Post reply

SQL Server Logs - Setting Max Size

  • October 22, 2003 at 2:47 pm

    #65561

    Hi all,

    I am looking for a way to set a maximum log file size for the SQL Server Logs. Currently it seems that there is no such max size, and the only time a new file is created is when the service is restarted. This makes it awfully difficult to open the files locally using EM or notepad due to potentially large file sizes.

    I would like to force SQL Server to use a new log file whenever a set size limit has been reached. Has anyone else done this?

    As always, any and all help is appreciated!

    Thanks,

    Dan B.

  • October 22, 2003 at 3:04 pm

    #479190

    Setting a max size probably isn't feasible, but perhaps you could cycle the logs on a regular basis? Brian Knight wrote about some undocumented tricks for logs:

    http://www.sqlservercentral.com/columnists/bknight/readpurgelog.asp

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • October 22, 2003 at 4:10 pm

    #479194

    Thanks Brian for the link! (and Brian for the article!)

    A little more work and voila! Here is a sample proc that could be used with a scheduled job to regularly recycle the current log should it grow beyond a specified size.

    I wonder about the field sizes though... Is there anyway to find out what these are in fact?

    Anyway,

    Thanks again!

    Dan B

    

    Create Procedure procErrorLogMaintenance

    (

    @MaxFileSizeint = 10240000

    )

    AS

    

    Declare @CurrentLogFileSize bigint

    

    create table #LogFileInfo

    (

    [Archive #] tinyint,

    [Date] datetime,

    [Log File Size (Byte)] bigint

    )

    

    insert #LogFileInfo 

    exec xp_enumerrorlogs

    

    Select @CurrentLogFileSize = [Log File Size (Byte)]

    From #LogFileInfo

    Where [Archive #] = 0

    

    Select @CurrentLogFileSize

    If @CurrentLogFileSize > @MaxFileSize

    exec sp_cycle_errorlog

    

    Drop table #LogFileInfo

Viewing 3 posts - 1 through 2 (of 2 total)

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