Altering and Dropping Procedures in Use
Go Up to Altering and Dropping Stored Procedures
You must make special considerations when making changes to stored procedures that are currently in use by other requests. A procedure is in use when it is currently executing, or if it has been compiled internally to the metadata cache by a request.
Changes to procedures are not visible to client applications until they disconnect and reconnect to the database; triggers and procedures that invoke altered procedures don’t have access to the new version until there is a point in which all clients are disconnected.
To simplify the task of altering or dropping stored procedures, it is highly recommended to perform this task during a maintenance period when no client applications are connected to the database. By doing this, all client applications see the same version of a stored procedure before and after you make an alteration.
You can minimize the maintenance period by performing the procedure alteration while the database is in use, and then briefly closing all client applications. It is safe to alter procedures while the database is in use.
Internals of the technology:
Below is a detailed description of the internal maintenance of stored procedure versions, to help explain the behavior of the technology.
When any request invokes a stored procedure, the current definition for that stored procedure is copied at that moment to a metadata cache. This copy persists for the lifetime of the request that invoked the stored procedure.
A request is one of the following:
- A client application that executes the stored procedure directly
- A trigger that executes the stored procedure; this includes system triggers that are part of referential integrity or check constraints
- Another stored procedure that executes the stored procedure
Altering or dropping a stored procedure takes effect immediately; new requests that invoke the altered stored procedure see the latest version. However, outstanding requests continue to see the version of the stored procedure that they first saw, even if a newer version has been created after the request’s first invocation of the stored procedure. There is no method to force these outstanding requests to update their metadata cache.
A trigger or stored procedure request persists in the metadata cache while there are one or more clients connected to the database, regardless of whether the client makes use of the trigger or stored procedure. These requests never update as long as any client is connected to the database. These requests are emptied from the metadata cache only when the last client disconnects from the database.
The only way to guarantee that all copies of a stored procedure are purged from the metadata cache is for all connections to the database to terminate. Only then are all metadata objects emptied from the metadata cache. Subsequent connections and triggers spawned by them are new requests, and they see the newest version of the stored procedure.