Posted by Sergey on 03/18/06 06:11

Hi everyone,

It looks like a mystery, but I hope there should be some explanation to
the issue I experience. Once in a blue moon a random stored procedure
stops working the way it was designed. The stored procedure code looks
unchanged. Recompiling, altering the code do not help. It looks like it
is simply does not execute some part of it or does not run at all.
However it returns no errors.
One time a procedure entered into infinite loop and almost hang the
whole server.

When I copy procedure code and save it under different name, it works
as designed. But nothing helps with existing procedure. The only way
how to fix it is to completely drop and recreate.

The problem is, that you usually have to do it in the middle of the
business day after you spent few hours trying to realize what went
wrong before you realize that you got another mysterious corruption. Of
cause I have no clue of how to detect such things in advance and to
prevent them from occurring in the future.

I can guarantee that the SQL code in those procedures was absolutely
bug free, fully tested and was working fine for a long time.

For the first time I thought that internal compiled code might corrupt.
In this case altering or recompiling should help.
I also thought about execution plan, but it should be also fixed by
doing things above.
DBCC checkdb does not find any errors
The issue never goes away until stored procedure is manually dropped
and recreated with the same SQL code.

So, I'm asking all if someone experienced something similar and can
explain how to prevent it, please share the knowledge. I would
appreciate any type of help.

Thank you.

[Back to original message]