I always thought that updating a stored procedure was something I could do while users were actively using the system.

But I am currently testing out an update to a stored procedure and while a long running call to the sproc was being executed (it was 4+ min into the call), I updated the sproc. (I was planning to try a different query plan in a seperate window.)

When I went back to the long running one, I had this error:

The definition of object 'MySprocName' has changed since it was compiled.

This error seems to say that any sproc that is being actively executed will fail if the definition of that sproc is updated. (I thought that once a sproc started running, it would use that plan for the rest of the run despite changes to the definition.)

Is this true? Do I need to have downtimes to update definition of a sproc?

  • 3
    Is the stored procedure call part of a larger transaction? Does it have any code that takes out schema locks on anything? In theory the alter should just be blocked. Can you go into more detail about what the procedure does and what it is a part of? – Aaron Bertrand Jan 16 at 16:50
  • @aaron Bertrand - it is a fairly benign sproc. It has some select top calls. No locks or any other things like that. The only thing significant is that it has some “if” statements based off the parameters. The actual query run varies a bit based on the parameters. It is called from a .net service. – Vaccano Jan 16 at 17:48
  • 2
    A “fairly benign” procedure shouldn’t take four minutes to run. What is it doing for all that time? – Aaron Bertrand Jan 16 at 19:06
  • Have you tried alter proc <proc Name> WITH RECOMPILE ? This is a procedure level option to set the procedure to recompile after each run. This means that the plan will always recompile and so avoid cached plans being reused. – MarmiK Jan 17 at 12:57
  • dba.stackexchange.com/questions/154939/… Have you looked at this question? – k010mb0 Mar 20 at 21:27

Yes it is true that you should not change an actively running stored procedure, because SQL Server doesn't like it.

As for taking downtime, well yeah, you shouldn't be making changes to frequently-used stored procedures in a busy production environment during operating hours as a best-practice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.