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.Sergey (afanas01@.gmail.com) writes:
> 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.
Have you ruled out the possibility there are two procedures with the same
name, but different schema? (= different owner in SQL 2000?) Or for some-
thing really exotic, what about numbered procedures? (That is, some_sp;2)
If you for instance change the code of the corrupted procedure to say
"PRINT 'Hello!" and nothing else, does print the message, or does it
continue what it used to do?
When this happens a SELECT * FROM sysobjects WHERE name = 'procname'
may reveal something.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||No numbered procedures, no different schema/owner. Everything is "plain
vanilla". Unfortunately, I did not have chance to play with corrupted
procedures.Usually when you have a production problem everybody whant
a) have it fixed ASAP and only then b) provide them with reasonable
explanation. I have a problm with b) ;-)|||Sergey (afanas01@.gmail.com) writes:
> No numbered procedures, no different schema/owner. Everything is "plain
> vanilla". Unfortunately, I did not have chance to play with corrupted
> procedures.Usually when you have a production problem everybody whant
> a) have it fixed ASAP and only then b) provide them with reasonable
> explanation. I have a problm with b) ;-)
I would still encourage you to examine sysobjects next time it happens.
Maybe there is something in your system, you did not know of. :-)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You must be right. I will definitely check this next time. This morning
I restored my DB to the point before the fix, but as I was expecting
everything was OK.Next time I will rename the corrupted procedure and
create a new one with old name (to get customer support drop off my
shoulders ;-). After that I will have plenty of time to have that thing
cracked.
I just hoped that maybe someone already know about this problem, so I
could learn faster, but thank you anyway. I can tell you if you'd like
when I get that solved.
Thanks again|||You must be right. I will definitely check this next time. This morning
I restored my DB to the point before the fix, but as I was expecting
everything was OK.Next time I will rename the corrupted procedure and
create a new one with old name (to get customer support drop off my
shoulders ;-). After that I will have plenty of time to have that thing
cracked.
I just hoped that maybe someone already know about this problem, so I
could learn faster, but thank you anyway. I can tell you if you'd like
when I get that solved.
Thanks again
