Post reply

Add alarms to database when a query, stored procedure, SSIS package hangs

  • June 22, 2024 at 12:38 pm

    Go to Answer

    #4430735

    Hello SSC,

    I am a SQL Server developer that has some DBA skills. I need to add alarms to an instance or database when a query, stored procedure, or SSIS package hangs for longer than a few hours. I did not see anything in the security or maintenance drop down in SSMS.

    Any assistance would be greatly appreciated!

    Dave

    The are no problems, only solutions. --John Lennon

  • June 23, 2024 at 1:10 pm

    #4430860

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • June 23, 2024 at 4:12 pm

    Answer

    #4430881

    I don't believe such an alert exists.  My recommendation would be to make an SQL Server Agent Job that checks for such things and schedule it to run on a regular basis.  I would also have it save the information for a bit of "proof" history.

    A good start would be to search for "WhoIsActive" by Adam Machanic and go from there.  Brent Ozar also has some similar code in his "Blitz" offerings.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 24, 2024 at 3:47 pm

    #4431098

    A few years ago I created a job that sent me an email if any job ran 'X' times longer than it's average run time. Maybe that would work ?

    I lost the code when I changed jobs, so can't post it.

Viewing 4 posts - 1 through 3 (of 3 total)

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