February 19, 2011 at 1:43 am
We do have two procedure listed below.
1) Reindex_Sunday - This should be put in a job and run weekly on Sundays
2) Reindex_EveryDay - This should be put in a job and run daily (except Sunday).
But we need one more script that can stop the above jobs by 8 AM if they are not completed already.
February 19, 2011 at 12:28 pm
You can use sp_help_job @execution_status = 0. This will return list of jobs that currently run. Save results to the Temp table and then stop jobs one by one with sp_stop_job.
February 19, 2011 at 1:02 pm
How about the "Intervention system for runaway jobs" : http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30713/
I haven't tested/modified it for SQL2008 features though.:blush:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2011 at 5:39 am
Just a side note, if your reindex jobs don't finish and you stop them, isn't the problem just going to spiral? Won't every night it get, maybe, just a little bit worse? Instead of looking to kill the job, I'd try to identify where the bottlenecks are and work around them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2011 at 9:00 am
Good point.
You can split your job to several jobs as one of the options. Run separate jobs for different databases or large tables.
Also, try to review jobs schedules. Reindex job can run longer if other jobs run at the same time (such as DB check or update statistics).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply