Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2016
    Posts
    2

    Answered: Sql server agent logs as a table

    2008

    Go to management studio > sql server agent > Error logs > current > Sql server

    We see the logs that records warnings and errors, job output , backups etc

    Is there a way to query that log as a table?

  2. Best Answer
    Posted by Pat Phelan

    "Keep in mind that this uses undocumented extended stored procedures. No one (not me, not Microsoft, not anybody) guarantees this will work in any particular way at any particular time under any particular conditions.
    Code:
    DECLARE
       @ArchiveID   INT = 0                 -- 0 for ERRORLOG, 1 for ERRORLOG.1
    ,  @LogType     INT = 1                 -- 1 for SQL Server, 2 for SQL Agent
    ,  @Filter1     NVARCHAR(4000) = NULL   -- First  required string
    ,  @Filter2     NVARCHAR(4000) = NULL   -- Second required string
    ,  @BeginDate   DATETIME = NULL         -- Final date to fetch
    ,  @EndDate     DATETIME = NULL         -- Beginning date to fetch
    ,  @SortOrder   NVARCHAR(4) = N'DESC'   -- 'ASC' or 'DESC' for sort order
    
    CREATE TABLE #foo (
       LogDate      DATETIME
    ,  ProcessInfo  NVARCHAR(25)
    ,  [Text]       NVARCHAR(3950)
    )
    
    INSERT INTO #foo
    EXECUTE xp_readerrorlog @ArchiveID
    ,  @LogType, @Filter1, @Filter2
    ,  @BeginDate, @EndDate, @SortOrder
    -PatP"


  3. #2
    Join Date
    Jan 2016
    Location
    San Jose
    Posts
    4
    Quote Originally Posted by silverc View Post
    2008

    Go to management studio > sql server agent > Error logs > current > Sql server

    We see the logs that records warnings and errors, job output , backups etc

    Is there a way to query that log as a table?
    They're not tables but text files - right-click on error logs and choose Configure, to find the path to the log files. You can open in a text editor or a spreadsheet & do sorting and filtering if that's all you need (the viewer also lets you filter and export), or import it. You can also define reports (MS Report Writer .rdl files) and apply them to any of the log archives. Whichever you're comfortable with.

  4. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Keep in mind that this uses undocumented extended stored procedures. No one (not me, not Microsoft, not anybody) guarantees this will work in any particular way at any particular time under any particular conditions.
    Code:
    DECLARE
       @ArchiveID   INT = 0                 -- 0 for ERRORLOG, 1 for ERRORLOG.1
    ,  @LogType     INT = 1                 -- 1 for SQL Server, 2 for SQL Agent
    ,  @Filter1     NVARCHAR(4000) = NULL   -- First  required string
    ,  @Filter2     NVARCHAR(4000) = NULL   -- Second required string
    ,  @BeginDate   DATETIME = NULL         -- Final date to fetch
    ,  @EndDate     DATETIME = NULL         -- Beginning date to fetch
    ,  @SortOrder   NVARCHAR(4) = N'DESC'   -- 'ASC' or 'DESC' for sort order
    
    CREATE TABLE #foo (
       LogDate      DATETIME
    ,  ProcessInfo  NVARCHAR(25)
    ,  [Text]       NVARCHAR(3950)
    )
    
    INSERT INTO #foo
    EXECUTE xp_readerrorlog @ArchiveID
    ,  @LogType, @Filter1, @Filter2
    ,  @BeginDate, @EndDate, @SortOrder
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #4
    Join Date
    Jan 2016
    Posts
    2
    I have multiple instances and multiple database flavors, oracle, mysql, SQL server. I have management metric views configured on my other databases that I push out over a combined html report, but I need to manually get the data for SQL server each morning through the management studio so exporting the data would just be replicating that manual step which Im trying to avoid.

    A odbc call to a table on SQL server would allow me to push the data out with the other reports in same format

    Pat, thanks for that, I'll try it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •