“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.