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.
| Author |
Topic |
|
beyonder422
Posting Yak Master
124 Posts |
 Posted - 2005-09-26 : 09:46:30
|
| Anyone have any suggestions on how to troubleshoot jobs that are randomly failing due to to blocking?I've identified a job that is randomly failing due to blocking, but I'm not really sure how to proceed to address the issue.The obvious is to trace it, but it happens so infrequently and randomly, I'm not sure how to trap it. I'm looking into lock escalation and blocking, etc. to possibly re-write the code, but am not quite sure what to implement.any suggestions? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-26 : 11:07:07
|
| Why not reapply the current transaction log onto a spare DB...and step forward through the transactions, until the blocking time. Blocking surely only occurs when a DB DELETE/UPDATE is in progress?? |
 |
|
|
beyonder422
Posting Yak Master
124 Posts |
Posted - 2005-09-26 : 11:33:18
|
| Never done that before. How do I do that? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-26 : 12:09:31
|
| Get last full DB backup...get all transaction logs....and then roll forward all o/s transactions using a tool like Lumigent or LogPI (forgot to mention that this may be useful/needed). Stepping through this way, you may be able to spot what is causing the blocking.Alternatively, enhance current code to have error trapping, and to log 'cause of error'....and hopefully 'rollback by SPID xxxx' can be identified and also what/who SPID xxxx is/was doing. |
|
|
|
|
|
|
Random Job failures due to blocking