Post reply

Error Log Query

  • September 6, 2007 at 2:58 pm

    #176555

    Is there an easy query to figure out how long the error log is set to keep the data? Thanks

    -Kyle

  • September 7, 2007 at 1:50 am

    #732037

    SQl doesn't keep its error log data for a fixed time. Rather it keeps a certain number of historical error log files.

    A new log file gets created whenever SQL restarts of the sp_cycle_errorlog proc is run.

    By default, I think 6 files are kept.

    From what I can tell, the value is kept in the registry, not in a SQL config table. This should get it for you (registry location may differ for other than default instance)

    declare

    @NumErrorLogs int

    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT

    SELECT @NumErrorLogs

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 7, 2007 at 2:18 am

    #732045

    Kyle,

    do you mean how many error logs SQL Server will keep before deleting the last one? You can find out the currently set number by:

    declare @NumErrorLogs int

    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT

    select @NumErrorLogs

    It cycles them every time you restart the server or cycle them manually (or from a job). When this happens SQL Server deletes the last one (which has the number of the above setting minus one).

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • September 7, 2007 at 7:17 am

    #732127

    Thanks, this helps.

  • September 7, 2007 at 7:28 am

    #732131

    Whenever I try to use the above query, I only get a null column

    -Kyle

  • September 7, 2007 at 7:35 am

    #732134

    Null means that SQL Server is using the default value. It is 6, as Gail said. What I do not understand is how his post got there It has taken me 5 minutes to fire up profiler, and get the same query. Sorry for the redundancy, looks like the forum feels it is Friday

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

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

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