Altering SQL Server stored procedure while it’s running

“Msg 2801, Level 16, State 1, Procedure dbo.sp_alterSP_break_execution, Line 16 [Batch Start Line 0]
The definition of object ‘sp_alterSP_break_execution’ has changed since it was compiled.”

I get this is error message when alter stored procedures on servers that are frequently called. The error does not occur to all procedures and it’s also not always to any specific ones, I found that error come from just some procedures but it’s impossible to predict when it will happen. When you get this error message mean the execution of that stored procedure has been terminated midway, subsequent statements were not executed. This post from Microsoft show a situation produce this error and they have a hotfix for it. In this post I will share with you two situations where you would get the same error when alter the procedure while while it is running in another session.

Continue reading
Posted in Uncategorized | Leave a comment