“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.
While a stored procedure is being executed, altering definition itself is not enough to cause the problem, only statistics changed ( a table that is referred by a statement in stored procedure) to mark recompile also not cause the problem. It just happen when combine the altering and updating statistics. Let’s see how it work in the following demo.
USE tempdb GO CREATE TABLE customers ( customerId INT IDENTITY PRIMARY KEY CLUSTERED, firstname VARCHAR(50), lastname VARCHAR(50), email VARCHAR(120) ) CREATE TABLE transactions ( Id INT IDENTITY PRIMARY KEY CLUSTERED, customerId INT, transactiontime DATETIME, transactiondesc VARCHAR(120) ) CREATE INDEX IX_customerId ON transactions(customerId,transactiontime) GO DECLARE @i INT = 1 BEGIN TRAN WHILE @i <= 100 BEGIN INSERT INTO customers(firstname,lastname,email) SELECT CAST(@i AS VARCHAR(10)) + '-firstname', CAST(@i AS VARCHAR(10)) + '-lastname', CAST(@i AS VARCHAR(10)) + '@email.com' SET @i += 1 END COMMIT GO DECLARE @i INT = 1 BEGIN TRAN WHILE @i <= 1000 BEGIN INSERT INTO transactions(customerId, transactiontime, transactiondesc) SELECT @i%10+1, GETDATE(), CAST(@i AS VARCHAR(10)) + '-desc' SET @i += 1 END COMMIT GO GO CREATE PROCEDURE [dbo].[sp_alterSP_break_execution] ( @waittime INT = 0 ) AS BEGIN SET NOCOUNT ON SELECT COUNT(*) FROM transactions t WHERE t.customerid = 8 AND transactiontime <= GETDATE() WAITFOR DELAY @waittime SELECT id, t.customerid, transactiontime, c.firstname, c.lastname FROM transactions t INNER JOIN customers c ON t.customerId = c.customerId WHERE t.customerid = 8 AND transactiontime <= GETDATE() ORDER BY transactiontime END GO
The script above created two tables customers and transaction, then populate some data into them. I also created a stored procedure with two statements, the ‘WAITFOR DELAY @waittime’ command will wait for several seconds while we will do alter the procedure, insert some more rows to transactions table and then update statistics the table. the scripts below will be executed in two separate session, session 1 will execute the procedure while session 2 do altering & updating statistics.
--- session 1 USE tempdb GO --- execute several times with @waittime = 0 to make sure query plan is compiled and it works well EXECUTE [dbo].[sp_alterSP_break_execution] @waittime = 0 GO 2 GO --- execute the procedure with long wait time EXECUTE [dbo].[sp_alterSP_break_execution] @waittime = 30
On session 2, run the following script.
--- session 2 USE tempdb GO ALTER PROCEDURE [dbo].[sp_alterSP_break_execution] ( @waittime INT = 0 ) AS BEGIN SET NOCOUNT ON SELECT COUNT(*) AS cnt FROM transactions t WHERE t.customerid = 8 AND transactiontime <= GETDATE() WAITFOR DELAY @waittime SELECT id, t.customerid, transactiontime, c.firstname, c.lastname FROM transactions t INNER JOIN customers c ON t.customerId = c.customerId WHERE t.customerid = 8 AND transactiontime <= GETDATE() ORDER BY transactiontime END GO DECLARE @i INT = 1 BEGIN TRAN WHILE @i <= 1000 BEGIN INSERT INTO transactions(customerId, transactiontime, transactiondesc) SELECT @i%10+1, GETDATE(), CAST(@i AS VARCHAR(10)) + ' - new desc' SET @i += 1 END COMMIT GO UPDATE STATISTICS transactions
Then turn to session 1 and wait for it complete with the error as below screenshot.

Another situation to get this error is when you use the OPTION(RECOMPILE) in your statements. If you alter a stored procedure that it is running any statements before the one with recompile option hint then the execution will be terminated when it enter that statement. Script below show the stored procedure with OPTION(RECOMPILE) in the last statement. Please press F5 to apply new change to the procedure.
USE tempdb GO ALTER PROCEDURE [dbo].[sp_alterSP_break_execution] ( @waittime INT = 0 ) AS BEGIN SET NOCOUNT ON SELECT COUNT(*) as cnt FROM transactions t WHERE t.customerid = 8 AND transactiontime <= GETDATE() WAITFOR DELAY @waittime SELECT id, t.customerid, transactiontime, c.firstname, c.lastname FROM transactions t INNER JOIN customers c ON t.customerId = c.customerId WHERE t.customerid = 8 AND transactiontime <= GETDATE() ORDER BY transactiontime OPTION (RECOMPILE) END GO
Then repeat the steps as in the first demo but this time we don’t need to update statistics on session 2, just alter the procedure is enough.
--- session 1 USE tempdb GO --- execute several times with @waittime = 0 to make sure query plan is compiled and it works well EXECUTE [dbo].[sp_alterSP_break_execution] @waittime = 0 GO 2 GO --- execute the procedure with long wait time EXECUTE [dbo].[sp_alterSP_break_execution] @waittime = 30 GO --- session 2 USE tempdb GO ALTER PROCEDURE [dbo].[sp_alterSP_break_execution] ( @waittime INT = 0 ) AS BEGIN SET NOCOUNT ON SELECT COUNT(*) as cnt FROM transactions t WHERE t.customerid = 8 AND transactiontime <= GETDATE() WAITFOR DELAY @waittime SELECT id, t.customerid, transactiontime, c.firstname, c.lastname FROM transactions t INNER JOIN customers c ON t.customerId = c.customerId WHERE t.customerid = 8 AND transactiontime <= GETDATE() ORDER BY transactiontime OPTION (RECOMPILE) END GO
and then we will get the same error in demo 1.
You can see full demo in the video below
I tried those demos on many version of SQL Server such as SQL Server 2008R2, SQL Server 2016, SQL Server 2017 and they happen exactly same. Is this a bug of MS SQL Server?
In this post I shared with you two situations when we alter a running stored procedure will break its execution. It’s summarized as following:
1. The combination of altering procedure with updating statistics.
2. Altering a procedure that contain OPTION (RECOMPILE) hint in one of its statements.
Leave a Reply