Re-resolution

The process of updating the existing query tree and invalidating any query plans in the cache is called re-resolution. Re-resolution occurs when one of the tables changes in such a way that the query tree stored in sysprocedures might be invalid (for example, if the datatypes, column offsets, object IDs, or other parts of the table have changed).

Re-resolution can be triggered by various activities, most of which are controlled by SQL Server, not the procedure owner or the SA. Procedure re-resolution occurs after any of the following:

• Executing LOAD DATABASE on the database containing the procedure.
• Executing LOAD DATABASE on a database containing a referenced table.
• Dropping and re-creating a table referenced by the procedure.
• Dropping and re-creating a database containing a table referenced by the procedure.
• Binding or unbinding a rule to a table referenced by a query in the procedure.
• Binding or unbinding a default to a table referenced by a query in the procedure.

During re-resolution, SQL Server rebuilds some parts of the query tree but does not deallocate others. This process slowly causes the query plan to grow, which eventually causes an error. Dropping and re-creating the procedure will resolve this.