Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 check time run on a sp

Author  Topic 

allan8964
Posting Yak Master

249 Posts

Posted - 2013-10-08 : 16:37:56
Hi there,

I have 20 stored procedures running about 2 hours totally. Some of them take long time like 10 or 20 minutes. Now I need check #11 and #13, for example, to know if they finished or stuck somewhere during whole process. If I know #11 runs about 10 min. so I can write something to check if #11 runs over 10 min. If it runs already 15 min and still not done then I know something not right there. I run all these in a job. So If I can find some proc runs over time then I may get email telling me that.
So is there any way I can check if the proc runs over time?
Thanks in advance.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-08 : 17:50:16
You can set a query timeout, but that is sort of destructive - i.e., if it were to successfully run in 11 minutes if you had done nothing at all, and you set the timeout to 10 minutes, you will get no results at all. http://technet.microsoft.com/en-us/library/ms189040.aspx

If you are using any third party schedulers - such as Active Batch, Skybot, JAMS etc. - they should have features that notify you when a job takes longer than expected in a non-destructive fashion.

You could perhaps do a two-tier systerm - i.e, if you expect it to run in 10 minutes, set the timeout to say 25 minutes, and then have it notify you with the time it took. You can use database mail to notify you. To find the elapsed time, save the start time at the beginning of the stored proc and calculate the difference between current time and the start time at the very end of the stored proc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 23:51:24
quote:
Originally posted by allan8964

Hi there,

I have 20 stored procedures running about 2 hours totally. Some of them take long time like 10 or 20 minutes. Now I need check #11 and #13, for example, to know if they finished or stuck somewhere during whole process. If I know #11 runs about 10 min. so I can write something to check if #11 runs over 10 min. If it runs already 15 min and still not done then I know something not right there. I run all these in a job. So If I can find some proc runs over time then I may get email telling me that.
So is there any way I can check if the proc runs over time?
Thanks in advance.



Are all these procedures running as different steps within same job or are they separate jobs?
What you could do is to have a control table which logs the start and end times of jobs. Use a separate job to check if time elapsed between start and getdate() has crossed the threshold time (15 min in your case) for jobs which has end time as NULL (still running) and without any failure info. Then call sp_stop_ob to stop these jobs.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -