The definition of object '[sp_name]' has changed since it was compiled on fail-over to mirror database

by QFDev   Last Updated June 20, 2017 22:06 PM

We have two SQL 2012 database servers configured in a mirror with a witness for automatic fail-over.

Yesterday the primary server suffered a HDD degradation which triggered the fail-over, however when the secondary server became the principle we started to see many errors on execution of some SPROCs.

The definition of object '[sp_name]' has changed since it was compiled

I understand that this can be resolved by running sp_recompile on the new principle, which would force a recompile on all SPROCs, functions and triggers.

This is not ideal as it requires manual intervention on fail-over. Is this a problem I can resolve through amending the configuration, or is it a known SQL Server bug?



Answers 1


Which SQL Edition are you using? Sometimes the engine couldn't refer the metadata catalog resulting in such error. This was identified as a bug. But you've workaround to resolve this issue. If You could recompile the stored procedure the next time it runs using sp_recompile.

The details of compile and re-compile is discussed below.

[https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/recompile-a-stored-procedure][1]

[https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/recompile-a-stored-procedure][1] 

Update Stored Proc in SSMS and executing a separate application which calls the procedure. You need to end the session in which you are editing the procedure. The changes are only visible in the editing session until they are committed.

Check this link:

https://stackoverflow.com/questions/26236470/sqlserver-the-definition-of-object-name-has-changed-since-it-was-compiled

CR241
CR241
June 20, 2017 21:15 PM

Related Questions


Best DR strategy for MS SQL Failover Cluster

Updated August 11, 2017 21:06 PM



Mirroring DB failover, and found Schema different

Updated July 14, 2015 15:02 PM