Stored Procs Reverting

[SJPS06]

So, we have been having this really weird problem. We have had some stored procedures revert back to old versions seemingly on their own.

Here is one example (very detailed).

On Friday, I identified two bugs in one of my procs where I had an issues with the dates and a problem in some logging I build into it.

At approx. Noon of Friday I released an update to the stored proc to edit the dates, ran it a couple of times, then at approx 4:40PM I released an update to the logging, and ran it one more time. Everything worked great.

At 5:00PM on Saturday I ran the proc again, and again it ran fine (all of the new code was included).

On Monday, I ran the proc again, and suddenly it was reverted back to the way it was on Friday morning before I fixed the two errors.

So, on Monday at 1:00PM I changed the proc again and so far, everything is fine.
-----

Now, I am trying to figure out what the heck happened.

I did some research. I ran the Schema Changes History on the DB. And sure enough. between Friday 4:45PM and Monday 1:00PM there were no Alters to the stored proc.

So I dug deeper, thought maybe there was a mysterious restore done to the database (even though the data didn't revert, just the stored proc). Well again, there have been no restores on the DB since the last reboot (2 weeks ago), which is not surprising since I have not done any restores.

-----

So, anybody got any ideas on what could cause this?


Environment:
MSSQL 2005 Enterprise (64-bit).
Latest SPs and Hot-Fixes.
AMD64 processor
16GB Memory

 

[MartinCroft]

When you said
"On Monday, I ran the proc again, and suddenly it was reverted back to the way it was on Friday morning before I fixed the two errors"

did you mean the output of the procedure or did you physically look at the procedure and the code has changed back?

 

[SJPS06]

No output changes I expect. The actualy code in the procedure changed back.

 

[MartinCroft]

Most bizarre on, could a user have amended it?. If it reoccurred I would check modify_date column in sys.objects for that procedure before changing back.

I asked if the output had changed as I wondered if there were actually two procedures being run at different points one [username].procedure and one dbo.procedure and user in question had run the one under their own context.

 

[SQLWilts]

Just an obvious question - it isnt a clustered install is it? It culdn't have failed over to another version of the DB?
Other than that, it is VERY mysterious

 

[yelworcm]

Clustering would have no effect on the code of the stored procedure, unless the change was not committed (had to have been, though, since he saw the right result at least once).

You may want to set up a profiler trace to run quietly from some machine (or log to the local machine) picking up any statements that include the words CREATE, ALTER and PROC or PROCEDURE. I think someone is unintentionally changing the code on you. you don't have any kind of automated build process floating about in the system, do you?