locked
SQL Server 2008 - Alerts for Long Running Jobs RRS feed

  • Question

  • All,

    Back in SQL 2000, we had crazy queries that captured and notified long running jobs.

    What is your solution for SQL 2008? Is there anything out of the box or should we still develop our own, using DMVs?

    My company just got rid of SCOM, which had an alert for long running jobs right out of the box, and I need to create a new solution for this issue.

    Let me know how you're handling this on your environment.

    Thanks in advance for the help.


    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com
    Tuesday, November 2, 2010 5:08 PM

Answers

  • Hi,

    According to my research, I think we can use the stored procedure sp_help_job to get information about jobs that are used by SQL Server Agent, but maybe we need to do some addition processing link sending a database mail. Please refer to the following steps:

    1.     Create a function named udf_SysJobs_GetProcessid, please see:

    CREATE FUNCTION dbo.udf_SysJobs_GetProcessid(@job_id uniqueidentifier)

    RETURNS VARCHAR(8)

    AS

    BEGIN

    RETURN (substring(left(@job_id,8),7,2) +

                            substring(left(@job_id,8),5,2) +

                            substring(left(@job_id,8),3,2) +

                            substring(left(@job_id,8),1,2))

    END

     

    2.     Schedule a job to execute the following statements, please see:

     

      EXEC msdb.dbo.sp_send_dbmail

                 @profile_name = 'TestProfile',

                 @recipients = 'v-aiqi@microsoft.com',

                 @query = 'DECLARE @MaxMinutes int

                                                                   SET @MaxMinutes = 30

                                                                   SELECT          p.spid,j.name,p.program_name, isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) [MinutesRunning],

                                                                   last_batch FROM master..sysprocesses p

                                                                   JOIN msdb..sysjobs j ON dbo.udf_sysjobs_getprocessid(j.job_id) = substring(p.program_name,32,8)

                                                                   WHERE program_name like ''SQLAgent - TSQL JobStep (Job %''

                                                                   AND isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) > @MaxMinutes' ,

                 @subject = 'Find long running jobs',

                 @attach_query_result_as_file = 1

     

     

    I also perform a test with the above statements,it works fine. Please note than the mail address is fiction.

     

    For more information, please see:

     

    sp_send_dbmail (Transact-SQL)

     

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AgentLongRunning&referringTitle=Home

     

    Thanks,

    Ai-Hua Qiu

     


    Constant dropping wears away a stone.
    • Marked as answer by Ai-hua Qiu Wednesday, November 10, 2010 9:02 AM
    Friday, November 5, 2010 7:49 AM

All replies

  • Try using RedGate.... It has great tool called SQL Response.

    You can also create Alerts in SQL 2008.


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, November 2, 2010 5:15 PM
  • Hi. Just to declare my interest - I work for Red Gate so obviously think our tools are great too. But I thought I ought to mention - we've renamed SQL Response to "SQL Monitor" and released a new version (v2) literally 17 hours ago! In fact if you want to see an online demo (without even installing anything), you can go to:

    http://www.thefutureofmonitoring.com

    and click on the red "Try online now" banner - this is a live version of SQL Monitor watching our SQL Server Central databases..

    You can also download a fully functional trial version to try it for yourself of course.

    Regards

    Ben Rees, Red Gate Software

    Friday, November 5, 2010 7:05 AM
  • Hi,

    According to my research, I think we can use the stored procedure sp_help_job to get information about jobs that are used by SQL Server Agent, but maybe we need to do some addition processing link sending a database mail. Please refer to the following steps:

    1.     Create a function named udf_SysJobs_GetProcessid, please see:

    CREATE FUNCTION dbo.udf_SysJobs_GetProcessid(@job_id uniqueidentifier)

    RETURNS VARCHAR(8)

    AS

    BEGIN

    RETURN (substring(left(@job_id,8),7,2) +

                            substring(left(@job_id,8),5,2) +

                            substring(left(@job_id,8),3,2) +

                            substring(left(@job_id,8),1,2))

    END

     

    2.     Schedule a job to execute the following statements, please see:

     

      EXEC msdb.dbo.sp_send_dbmail

                 @profile_name = 'TestProfile',

                 @recipients = 'v-aiqi@microsoft.com',

                 @query = 'DECLARE @MaxMinutes int

                                                                   SET @MaxMinutes = 30

                                                                   SELECT          p.spid,j.name,p.program_name, isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) [MinutesRunning],

                                                                   last_batch FROM master..sysprocesses p

                                                                   JOIN msdb..sysjobs j ON dbo.udf_sysjobs_getprocessid(j.job_id) = substring(p.program_name,32,8)

                                                                   WHERE program_name like ''SQLAgent - TSQL JobStep (Job %''

                                                                   AND isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) > @MaxMinutes' ,

                 @subject = 'Find long running jobs',

                 @attach_query_result_as_file = 1

     

     

    I also perform a test with the above statements,it works fine. Please note than the mail address is fiction.

     

    For more information, please see:

     

    sp_send_dbmail (Transact-SQL)

     

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AgentLongRunning&referringTitle=Home

     

    Thanks,

    Ai-Hua Qiu

     


    Constant dropping wears away a stone.
    • Marked as answer by Ai-hua Qiu Wednesday, November 10, 2010 9:02 AM
    Friday, November 5, 2010 7:49 AM
  • I posted solution today that doesn't require need for function. Our solution was actually inspired/directed by Ai-hua's http://sqlchicken.com/2012/07/identify-and-alert-for-long-running-agent-jobs/

    Jorge Segarra
    SQLChicken.com || Follow me on Twitter! || SQL University
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Naomi N Monday, July 9, 2012 11:48 PM
    Monday, July 9, 2012 11:15 PM