Job History and Mail Logs Housekeeping

  • Hi all,

    a) SQL agent jobs history
    b) Database mail history

    q1) Can I double confirm that these so call "logs" for the above 2 items are actually
    I) stored in tables inside msdb
    II) does not involve "actual" os level log files ?

    q2) To keep the above jobs and mails history storage size in place, the following can be done
    I) maintenance plan  (clean up history) --> which will clean up job history
    II) mail event/items logs --> create a job that run the following below

    sysmail_delete_mailitems_sp

    sysmail_delete_log_sp

    Am i right ? Or did i miss out anything ?
    Hope gurus here can shed some light.

    Thanks!
    Regards,
    Noob

  • szejiekoh - Sunday, January 22, 2017 10:31 AM

    Hi all,

    a) SQL agent jobs history
    b) Database mail history

    q1) Can I double confirm that these so call "logs" for the above 2 items are actually
    I) stored in tables inside msdb
    II) does not involve "actual" os level log files ?

    q2) To keep the above jobs and mails history storage size in place, the following can be done
    I) maintenance plan  (clean up history) --> which will clean up job history
    II) mail event/items logs --> create a job that run the following below

    sysmail_delete_mailitems_sp

    sysmail_delete_log_sp

    Am i right ? Or did i miss out anything ?
    Hope gurus here can shed some light.

    Thanks!
    Regards,
    Noob

    The job history table you're looking for is msdb.dbo.sysjobhistory and the MSDN page is at https://msdn.microsoft.com/en-us/library/ms174997.aspx.
    The mail items table is msdb.dbo.sysmail_mailitems and the page is  https://msdn.microsoft.com/en-us/library/ms175056.aspx

    I would opt for writing a stored procedure to do your maintenance.  There's a page at https://msdn.microsoft.com/en-us/library/ms189258.aspx that explains the how to archive and delete your mail items.

  • Ed Wagner - Sunday, January 22, 2017 5:37 PM

    szejiekoh - Sunday, January 22, 2017 10:31 AM

    Hi all,

    a) SQL agent jobs history
    b) Database mail history

    q1) Can I double confirm that these so call "logs" for the above 2 items are actually
    I) stored in tables inside msdb
    II) does not involve "actual" os level log files ?

    q2) To keep the above jobs and mails history storage size in place, the following can be done
    I) maintenance plan  (clean up history) --> which will clean up job history
    II) mail event/items logs --> create a job that run the following below

    sysmail_delete_mailitems_sp

    sysmail_delete_log_sp

    Am i right ? Or did i miss out anything ?
    Hope gurus here can shed some light.

    Thanks!
    Regards,
    Noob

    The job history table you're looking for is msdb.dbo.sysjobhistory and the MSDN page is at https://msdn.microsoft.com/en-us/library/ms174997.aspx.
    The mail items table is msdb.dbo.sysmail_mailitems and the page is  https://msdn.microsoft.com/en-us/library/ms175056.aspx

    I would opt for writing a stored procedure to do your maintenance.  There's a page at https://msdn.microsoft.com/en-us/library/ms189258.aspx that explains the how to archive and delete your mail items.

    Hi Wagner,
    Thanks for your reply. 

    q1) So can i say there are no logfiles (physical os files) involved for the for the job and mail history right ?

    q2) I read about the job history log from the SQL management studio
    Maximum job history log - size 1000
    So does that means even if I don't do any maintenance for the job history table, it will wrap by itself ?

    Regards,
    Noob

  • szejiekoh - Tuesday, January 24, 2017 11:25 PM

    Ed Wagner - Sunday, January 22, 2017 5:37 PM

    szejiekoh - Sunday, January 22, 2017 10:31 AM

    Hi all,

    a) SQL agent jobs history
    b) Database mail history

    q1) Can I double confirm that these so call "logs" for the above 2 items are actually
    I) stored in tables inside msdb
    II) does not involve "actual" os level log files ?

    q2) To keep the above jobs and mails history storage size in place, the following can be done
    I) maintenance plan  (clean up history) --> which will clean up job history
    II) mail event/items logs --> create a job that run the following below

    sysmail_delete_mailitems_sp

    sysmail_delete_log_sp

    Am i right ? Or did i miss out anything ?
    Hope gurus here can shed some light.

    Thanks!
    Regards,
    Noob

    The job history table you're looking for is msdb.dbo.sysjobhistory and the MSDN page is at https://msdn.microsoft.com/en-us/library/ms174997.aspx.
    The mail items table is msdb.dbo.sysmail_mailitems and the page is  https://msdn.microsoft.com/en-us/library/ms175056.aspx

    I would opt for writing a stored procedure to do your maintenance.  There's a page at https://msdn.microsoft.com/en-us/library/ms189258.aspx that explains the how to archive and delete your mail items.

    Hi Wagner,
    Thanks for your reply. 

    q1) So can i say there are no logfiles (physical os files) involved for the for the job and mail history right ?

    q2) I read about the job history log from the SQL management studio
    Maximum job history log - size 1000
    So does that means even if I don't do any maintenance for the job history table, it will wrap by itself ?

    Regards,
    Noob

    1. Yes.  I'm aware of no files in the file system for these.  They're maintained in database tables in msdb.
    2. Yes.  It will maintain itself if you configure the maximum job history log size.

  • Ed Wagner - Wednesday, January 25, 2017 8:23 AM

    szejiekoh - Tuesday, January 24, 2017 11:25 PM

    Ed Wagner - Sunday, January 22, 2017 5:37 PM

    szejiekoh - Sunday, January 22, 2017 10:31 AM

    Hi all,

    a) SQL agent jobs history
    b) Database mail history

    q1) Can I double confirm that these so call "logs" for the above 2 items are actually
    I) stored in tables inside msdb
    II) does not involve "actual" os level log files ?

    q2) To keep the above jobs and mails history storage size in place, the following can be done
    I) maintenance plan  (clean up history) --> which will clean up job history
    II) mail event/items logs --> create a job that run the following below

    sysmail_delete_mailitems_sp

    sysmail_delete_log_sp

    Am i right ? Or did i miss out anything ?
    Hope gurus here can shed some light.

    Thanks!
    Regards,
    Noob

    The job history table you're looking for is msdb.dbo.sysjobhistory and the MSDN page is at https://msdn.microsoft.com/en-us/library/ms174997.aspx.
    The mail items table is msdb.dbo.sysmail_mailitems and the page is  https://msdn.microsoft.com/en-us/library/ms175056.aspx

    I would opt for writing a stored procedure to do your maintenance.  There's a page at https://msdn.microsoft.com/en-us/library/ms189258.aspx that explains the how to archive and delete your mail items.

    Hi Wagner,
    Thanks for your reply. 

    q1) So can i say there are no logfiles (physical os files) involved for the for the job and mail history right ?

    q2) I read about the job history log from the SQL management studio
    Maximum job history log - size 1000
    So does that means even if I don't do any maintenance for the job history table, it will wrap by itself ?

    Regards,
    Noob

    1. Yes.  I'm aware of no files in the file system for these.  They're maintained in database tables in msdb.
    2. Yes.  It will maintain itself if you configure the maximum job history log size.

    Thank you Ed so much for your reply and so sorry for coming in late !

  • szejiekoh - Monday, January 30, 2017 1:51 AM

    Ed Wagner - Wednesday, January 25, 2017 8:23 AM

    szejiekoh - Tuesday, January 24, 2017 11:25 PM

    Ed Wagner - Sunday, January 22, 2017 5:37 PM

    szejiekoh - Sunday, January 22, 2017 10:31 AM

    Hi all,

    a) SQL agent jobs history
    b) Database mail history

    q1) Can I double confirm that these so call "logs" for the above 2 items are actually
    I) stored in tables inside msdb
    II) does not involve "actual" os level log files ?

    q2) To keep the above jobs and mails history storage size in place, the following can be done
    I) maintenance plan  (clean up history) --> which will clean up job history
    II) mail event/items logs --> create a job that run the following below

    sysmail_delete_mailitems_sp

    sysmail_delete_log_sp

    Am i right ? Or did i miss out anything ?
    Hope gurus here can shed some light.

    Thanks!
    Regards,
    Noob

    The job history table you're looking for is msdb.dbo.sysjobhistory and the MSDN page is at https://msdn.microsoft.com/en-us/library/ms174997.aspx.
    The mail items table is msdb.dbo.sysmail_mailitems and the page is  https://msdn.microsoft.com/en-us/library/ms175056.aspx

    I would opt for writing a stored procedure to do your maintenance.  There's a page at https://msdn.microsoft.com/en-us/library/ms189258.aspx that explains the how to archive and delete your mail items.

    Hi Wagner,
    Thanks for your reply. 

    q1) So can i say there are no logfiles (physical os files) involved for the for the job and mail history right ?

    q2) I read about the job history log from the SQL management studio
    Maximum job history log - size 1000
    So does that means even if I don't do any maintenance for the job history table, it will wrap by itself ?

    Regards,
    Noob

    1. Yes.  I'm aware of no files in the file system for these.  They're maintained in database tables in msdb.
    2. Yes.  It will maintain itself if you configure the maximum job history log size.

    Thank you Ed so much for your reply and so sorry for coming in late !

    No problem.  Thank you for the feedback.

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

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