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?
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
The details of compile and re-compile is discussed below.
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: