6

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?

Improve this question
2
  • Does this same issue happen when you use availability groups? This may be a by-product of using a deprecated feature.
    – Chris Lumnah
    Mar 17, 2017 at 18:00
  • Even though mirroring is depreciated it doesn't mean it should break all existing implementations. Recompiling the procs did resolve the issue so I can now switch the principle over at any time without seeing this error.
    – QFDev
    Mar 20, 2017 at 20:16

1 Answer 1

Reset to default
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.

Recompile a Stored Procedure

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 the link below:

The definition of object 'name' has changed since it was compiled

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.