How I can know last maintenance job date and status(Success or Failed) by Sql Server Management Object

  • Hi,

    How I can get the last maintenance action information for a database instance programmaticall by SMO. The last maintenace action may be log backup, full database backup, etc. What I mean is that I want to know what kind of action the last maintenance is and it is success or failed.

    Any suggestion would be high appreciated,


  • Are you running these maintenance tasks as jobs in SQL Agent?

    If so, you can right-click a job and check its history. (Or query it from msdb, I believe.)

    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thank you very much for your information. However some tasks may be done not by Agent. Also I have to grab those information programatically via SMO (Sqlserver Management Object). Could you please give me more information about that?

    And you just mentioned that I can get those information by querying against database, then which table contains those information, and do you have any query on your hand? If so, could you please post the query?

    Thanks again,


  • You can use the properties 'LastBackupDate' (date of last full backup) and 'LastLogBackupDate' (date of last log backup).

    I don't see anything available to identify a differential backup though, if you need that.


    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thanks a lot. The information is so helpful for me.

    One more question, if the last maintenance task is not Backup how can I exactly know it's detail? Can I programmatically get the last task's information from somewhere like log file? If so, could you please let me know which class or property I should use to parse log file.

    Thank you once again,


  • I am not sure what you are asking for. I don't know of any properties that keep track of maintenance plans in SMO. You could look at Microsoft.SqlServer.Management.Smo.Agent to see if there is anything there, but this would not get you anything related to a task in a maintenance plan.

    With that said - you could always run a query to get that information:

    ;WITH LogDetail (task_detail_id, row)

    AS (

    SELECT task_detail_id

    ,ROW_NUMBER() OVER(ORDER BY start_time DESC)

    FROM msdb.dbo.sysmaintplan_logdetail


    SELECT m.line1











    FROM msdb.dbo.sysmaintplan_logdetailm

    INNER JOIN LogDetaill ON l.task_detail_id = m.task_detail_id

    WHERE row = 1;



    Edit: tried to fix the spacing in the code block. Cut & Paste just doesn't seem to work right all the time 😀

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • You can query msdb..backupset for information about what table has bene backed up when.

    select Database_Name, Physical_Device_name, Case When type = 'D' then 'Full'

    When type = 'L' Then 'log'

    When type = 'I' then 'Differential' end as Database_Type,



    from msdb..backupset a

    join msdb..backupmediafamily b

    on a.media_set_id = b.media_set_id

    where Backup_finish_date > getdate() - 1

  • Thank you guys' replys very much, I hope I can get one more chance to state my intention. Sorry for the confusions.

    I am implementing a web application (back-end is SqlServer) that needs below informations got presented on a page:

    1. Last Database Maintenance Job Date and Status (Success or Failed)

    2. Drive Space Available (Drive where Data File is located)

    3. Percent(%) of Drive Space Free

    For the last two items, I guess I can get them done by using WMI or entended Stored Procedures. But for the first one, I have no idea so far. I didn't find any useful information from SMO document(I was suppose that I can get it done by using SMO).

    The complexity comes from the fact that I cannot know exactly what kind of action the last maintenance is. Because "Last Database Maintenance" may refer to a action like Server Memory Change, databse backup or log backup ... . I am imaging all maintenance actions would be put into somewhere by Sqlserver, and the only thing I should do is to grab the last record from there then show the record out.

    Hope you guys can understand me more this time. Any help would be high appreciated. 🙂


  • Hi Can i get a script for to all the Database Maintenance Jobs in sql which stop executing after 8 am .

Viewing 9 posts - 1 through 8 (of 8 total)

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