Post reply

Rebuilding an index results in a stored procedure failing with "stored procedure xxx has changed since it was last compiled"

  • August 3, 2015 at 7:43 am

    #398837

    We have a scheduled task which runs each Saturday early morning, determining which indexes are fragmented, and then rebuilding those which need it.

    This Monday we had an error saying that "stored procedure xxx has changed since it was last compiled". I'm pretty sure this is because one of the indexes which it uses was rebuilt.

    The solution was to flag the stored procedure for recompile using sp_recompile.

    This raises two questions.

    1. I've read that the index rebuild should have automatically flagged the stored procedure for recompile - are there known conditions where this may not occur?

    2. If we can't guarantee that stored procedures are automatically flagged, can/should we just flag all stored procedures for recompile after running the index defrag/rebuild script? This seems like a sledgehammer approach and not optimal.

    Opinions/advice would be welcome.

  • August 3, 2015 at 10:27 pm

    #1816994

    This sounded intriguing, I've never seen it before and didn't think it was possible. Recompilation of procedures is automatic, so why is it complaining?

    I found two related articles because it looks like there's at least two ways to reproduce it; one with index changes the other with trigger changes:

    https://sqldbasupport.wordpress.com/2010/09/21/sql-server-error-definition-object-stored-procedure-changed-since-compiled/

    https://connect.microsoft.com/SQLServer/feedback/details/224946/msg-2801-the-definition-of-object-xxx-has-changed-since-it-was-compiled

    Unfortunately the script for the first one has gone walkies and their descriptions aren't very clear. I thought it might help point you in the right direction for your own testing even if it's not an answer. (And if you find a better one, please post back).

  • August 4, 2015 at 2:20 am

    #1817026

    Thanks for the links - I hadn't found them despite some creative Googling. It appears from the 1st one that it's possible to reproduce this. To be honest, given that it's occurred in our live environment, I need a solid workaround/mitigation for it - it was denying access to approximately 8,000 users, due to the fact that the SP was being run at login!

    I think I'm going to have to put in place a proactive sp_recompile for at least this SP, if not all, after the index defragmentation. This will cause a small performance hit when each SP is next initially run.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply