Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    22

    Unanswered: SQL 2005 Log File Viewer Maintenance Plans

    I have got a backup maintenance plan, and I want to output to a file if it has been a success or fail. I was using sp_help_jobhistory and relying on the run_status to determine if it succeeded(1) or failed(0). I have found this to be unreliable compared with the report which is in the SQL Agent Log File Viewer.

    I assume this has to be stored some where, how do I access the table where this is stored? So i can return the results of my maintenance plan from SQL Agent.

    Any help would be great.

    Cheers
    Last edited by Rob1982; 03-28-07 at 17:18.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    that data is not stored in a table. it's stored in agent's log file, which is a flat file called SQLAGENT.OUT (or SQLAGENT.X where X is an integer, for archived log files. a new log file is created each time agent starts, and the old file is archived. only a certain number of archived logs are kept around, don't remember how many)

    Warning! read on at your own risk, the rest of this post talks about undocumented system procedures, and they could change in future releases or service packs!

    You can read the file from sql using the extended proc xp_readerrorlog.

    the first param says which file to read in terms of history. 0 specifies the current log file, 1 the last one, 2 the one before that, and so on.

    the second param says which type of file to read. 1 means sql server, 2 means sqlagent. so pass 2 here since you want to read agent's log

    examples:
    Code:
    exec xp_readerrorlog 0,2 -- read the current agent log file
    exec xp_readerrorlog 0,1 -- read the current sql server log file
    
    exec xp_readerrorlog 1,2 -- read the last archived agent log file
    exec xp_readerrorlog 1,1 -- read the last archived sql server log file

  3. #3
    Join Date
    Dec 2003
    Posts
    22
    Thanks for that however I want to read the Maintenance Plan Log Type, and these aren't recorded in the SQL agent file if it fails only if they succeed.

    If not is there any easier way to check if a backup has been a success?

    Thanks

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    whoops, i misread your original post. somehow I missed the fact that you were looking for maintplan logs, and not the logs for agent itself.

    the maintplan data is stored in a table, namely here:

    msdb.dbo.sysmaintplan_log
    msdb.dbo.sysmaintplan_logdetail
    msdb.dbo.sysmaintplan_subplans

  5. #5
    Join Date
    Dec 2003
    Posts
    22
    Thanks for the information

Posting Permissions

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