Read SQL Error logs

  • vasanking

    SSC Enthusiast

    Points: 142

    Comments posted to this topic are about the item Read SQL Error logs

  • vivekmanutd

    SSC Rookie

    Points: 30

    Thank you very much for the info!!! :-):-):-)

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for this script.

  • Lee Linares

    SSCrazy

    Points: 2668

    Vasan,

    Thanks for taking the time to share this script. Very useful. If I could make a suggestion, would it not be easier to use xp_enumerrorlogs to find the number of error logs instead of xp_dirtree?

    Maybe something like this:

    ------------------------------------------------------------

    -- Create temp table to hold results from xp_enumerrorlogs

    ------------------------------------------------------------

    CREATE TABLE #ErrLogsDL

    (

    [Archive #] INT,

    [Date] VARCHAR(255),

    [Log File Size (Byte)] BIGINT

    )

    ---------------------------------------------------------------

    -- Populate the temp table with

    -- the data returned by executing xp_enumerrorlogs

    ---------------------------------------------------------------

    INSERT #ErrLogsDL([Archive #], [Date], [Log File Size (Byte)])

    EXEC master.dbo.xp_enumerrorlogs

    Thanks again.

    Lee

  • vasanking

    SSC Enthusiast

    Points: 142

    Sir,

    Thanks for your worthy suggestion. xp_enumerrorlogs works really good. Actually i am not aware on this thats what i prefered xp_dirtree option.

    Now code has been updated as per your sugession.

    Thanks a lot again ! 🙂

    Thanks,

    Vasan

  • Trond Lind

    Valued Member

    Points: 71

    It is also easier to use a local table instead of a # or ## temp table

    Example to get log entries for today sorted by date/time descending:

    DECLARE @tmpTab TABLE (

    LogDate DATETIME,

    ProcessInfo VarChar(50),

    [Text] VarChar(1000)

    )

    INSERT INTO @tmpTab EXEC sp_readerrorlog 0

    SELECT * FROM @tmpTab

    WHERE LogDate > DATEADD(d,0,DATEDIFF(d,0,GETDATE()))

    ORDER BY LogDate DESC

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

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