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

• Lord Slaagh

  SSCertifiable

  Points: 5766

  More actions

  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

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  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.

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  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)
  

• homebrew01

  SSC Guru

  Points: 55549

  More actions

  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.