I'm pulling my hair out once again. Did I happen to mention my sincere admiration and respect for all of you out there. Ok, enough sucking up, onto the meat...
I have a monthly build that runs a couple hundred stored procedures in sequence. This process takes approximately 15 hours to run. I have almost everything humming along in this big POS (can you tell I inherited it?) except for one very odd hangup...
There is a single proc that runs within the last 30 minutes of the build that seems to hang up for absolutley no reason. Here is what I've found so far.
1. The build hangs up on this proc every time it's ran.
2. There are no blocks and no wait anywhere
3. If I run the proc outside of the build it finishes within 3 minutes
4. If I kill the build and start it again from that step, the proc runs fine with 10 minutes or so.
5. A trace shows that it's hanging on an insert from a subquery into a table variable. This insert runs in less than a minute if ran manually.
6. I thought the tempdb might be full but spaceused during hang only shows about 85% free.
7. Same behavior in development and production
8. Both servers are SQL2000 SP4 with 2 DualCore 3.0 Xeons and 2GB Ram
9. IO seems to minimize and CPU load jumps to an oddly consistent state at around 25% during hangup
I've been digging for answers but with the SPID running with no locks and no wait, it doesn't make any sense to me what is actually going on.
I have a monthly build that runs a couple hundred stored procedures in sequence. This process takes approximately 15 hours to run. I have almost everything humming along in this big POS (can you tell I inherited it?) except for one very odd hangup...
There is a single proc that runs within the last 30 minutes of the build that seems to hang up for absolutley no reason. Here is what I've found so far.
1. The build hangs up on this proc every time it's ran.
2. There are no blocks and no wait anywhere
3. If I run the proc outside of the build it finishes within 3 minutes
4. If I kill the build and start it again from that step, the proc runs fine with 10 minutes or so.
5. A trace shows that it's hanging on an insert from a subquery into a table variable. This insert runs in less than a minute if ran manually.
6. I thought the tempdb might be full but spaceused during hang only shows about 85% free.
7. Same behavior in development and production
8. Both servers are SQL2000 SP4 with 2 DualCore 3.0 Xeons and 2GB Ram
9. IO seems to minimize and CPU load jumps to an oddly consistent state at around 25% during hangup
I've been digging for answers but with the SPID running with no locks and no wait, it doesn't make any sense to me what is actually going on.
) but there are no locks showing up anywhere. The only SPID running at the time is this one and it's not even listed as waiting.
