September 9, 2011 at 7:26 am
Hi all -
I have a job that updates statistics nightly. Whenever it encounters a deadlock and is chosen as the deadlock victim, it runs for a couple of days before finally sending me the "failure" notification. I guess I'm wondering what is the best way to handle this? Set up the job to automatically kill itself if it is running for longer than a few hours? Set up the job to email me when the job runs for an extended period of time, like found here? Re-write the job to handle deadlock situations better? Change the time the job runs?
This problem only occurs in "one-off" instances, so maybe once every few months.
Thanks for your help in advance!
September 9, 2011 at 7:50 am
try using a try - catch scenario. That is, do the update statistics in the try and in the catch detect the 1205 error and retry if it indicates the 1205. Why it is running for hours before notifying you is interesting although it is rolling back due to the 1205.
How long does it take normally to update the statistics? Why do you think you need to do this every day?
Another option is make some of the other processes more intelligent... set a flag in a control table indicating maintenance job in process. The other processes can quit and try later or use wait-for until the your update statistics completes.
Anyway, it would be interesting to understand more about this system to find out why this is happening and just fix it.
The probability of survival is inversely proportional to the angle of arrival.
September 9, 2011 at 7:59 am
sturner (9/9/2011)
try using a try - catch scenario. That is, do the update statistics in the try and in the catch detect the 1205 error and retry if it indicates the 1205. Why it is running for hours before notifying you is interesting although it is rolling back due to the 1205.How long does it take normally to update the statistics? Why do you think you need to do this every day?
Another option is make some of the other processes more intelligent... set a flag in a control table indicating maintenance job in process. The other processes can quit and try later or use wait-for until the your update statistics completes.
Anyway, it would be interesting to understand more about this system to find out why this is happening and just fix it.
It only usually takes 2 minutes or less to update the stats nightly on the server. That's why I'm confused as to why it is taking over 2 days when the the update stats statement encounters a deadlock situation.
It is a third party app, so I can't touch the code to make the non-deadlock victim processes smarter.
September 9, 2011 at 8:12 am
okay about you not being able to do anything with the rest of the code. I am still curious as to why you need to update stats every day... usually indicative of an issue with the database or process architecture.
You should enable the 1204 & 1222 trace flags that will give you some information in the SQL server logs that may provide insight into why this is happening. This is fixable in any number of ways, even it is 3rd party code. Also use try - catch to handle and report and/or retry this error in your code.
Beyond that I can't give any more insight as to why it hangs for two days when it is chosen as the deadlock victim. There will be some rollback time involved but two days is ridiculous.
The probability of survival is inversely proportional to the angle of arrival.
September 9, 2011 at 8:16 am
I also update stats daily because
#1 - I can 😀
#2 - I have tones of date columns with today() as value. Those stats need at least daily updates
#3 - The default 20% of data change is too much for our system on many tables
#4 - I have the window to do a hammer update and fullscan all the tables (20 minutes needed out of 8 hours window)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply