The IN_DB_UTIL_SP_CLEAR_IDENTIFIER stored procedure is responsible cleaning up the IN_DB_UTIL framework when a session is tearing down. This procedure should be executed when a procedure or session has finished its unit of work.
The IN_DB_UTIL_SP_CLEAR_IDENTIFIER procedure is responsible for the following framework deinitialization steps.
- Close and deallocate any existing global database cursors
- Decrement the call stack for the specified identifer(s)
- Clear the session attributes for the specified identifier(s)
- Release the application resource locks for the stored procedure and identifiers using the sp_releaseapplocks procedure.
Logged actions also include recording any exceptions (errors) that occur within the IN_DB_UTIL and IN_DB_UPGRADE packages.
The IN_DB_UTIL framework uses the SQL Server APPLOCK procedures to lock the various resources (stored procedures and framework identifiers) while they are being used. The IN_DB_UTIL_SP_SESSION_SETUP procedure uses the sp_getapplock procedure, during session initialization, to lock the resources. These application resource locks are held until the procedure has completed and called the IN_DB_UTIL_SP_CLEAR_IDENTIFIER procedure to cleanup the session and release the resource locks using the sp_releaseapplock procedure. If a procedure is cancelled prior to completion then these application locks will persist until the session is disconnected or until the IN_DB_UTIL_SP_CLEAR_IDENTIFIER procedure is executed within the same session.
Under normal circumstances, the IN_DB_UTIL_SP_CLEAR_IDENTIFIER procedure should never need to be manually executed. However, it can be used to manually clear the call stack and session attribute tables as needed due to an unhandled exception or after manually cancelling an operation before completion.
Important Do not manually execute this procedure if any operations are still in progress as it will clear the call stack and session attributes which will lead to undesired results and unhandled exceptions.
Examples
| Parameter | Description | |
|---|---|---|
| IDENTIFIER | Type: | INPUT |
| Datatype: | SYSNAME | |
| Default Value: | NULL | |
| Description | Specifies the identifier for which to extend the call stack. | |
| Options |
IN_DB_UTIL SYNC UPGRADE ALL (SYNC and UPGRADE) |
|
| CALLER | Type: | INPUT |
| Datatype: | SYSNAME | |
| Default Value: | NULL | |
| Description |
Specifies the procedure name that called the current procedure (if applicable). This is the procedure that precedes the current procedure in the call stack depth unless the current procedure is the first item in the call stack depth. If the CALLER is NULL then the call stack will be cleared and all attributes will be reset to IDLE for the identifier(s). If the CALLER is NOT NULL then the call stack will be decremented and the OPERATION attribute will be returned to the CALLER procedure for the identifier(s). |
|
| SPID | Type: | INPUT |
| Datatype: | SMALLINT | |
| Default Value: | NULL | |
| Description | The SPID of the session that is currently utilizing the IN_DB_UTIL identifier. The inclusion of the SPID is only necessary when clearing the IN_DB_UTIL identifier from a separate session (SPID) and is not required for the SYNC, UPGRADE, or ALL identifiers. | |
| DEBUG | Type: | INPUT |
| Datatype: | NVARCHAR(3) | |
| Default Value: | NO | |
| Description | Specifies whether to display the procedure name during execution. | |
| Options | YES NO |
|
| V_RETURN_VAL | Type: | RETURN |
| Datatype: | INTEGER | |
| Default Value: | 0 | |
| Description | Specifies whether the execution of the procedure was successful or
not. 0 = Success 1 = Error |
|
Examples
The following example demonstrates how this procedure is called from within another procedure after it has completed all of its work. If the current procedure is not the first on the call stack depth (1) then V_CALLER should be the name of procedure that called the current procedure and thus precedes it on the call stack depth.
EXEC IN_CLEAR_IDENTIFIER @V_IDENTIFIER, @V_CALLER, @SPID, @V_DEBUG;
The following example demonstrates how to use this procedure to manually clear the call stack and session attributes for a specific identifier, IN_DB_UTIL in this case, whose SPID is 95.
ImportantDo not manually execute this procedure if any operations are still in progress.
EXEC IN_CLEAR_IDENTIFIER @IDENTIFIER = 'IN_DB_UTIL', @SPID = 95, @DEBUG = 'YES';
Example output
The following example demonstrates the clearing of “ALL” identifiers (SYNC and UPGRADE) after manually executing the IN_DB_UTIL_SP_CLEAR_IDENTIFIER procedure to clear the call stack and session attributes after having cancelled the IN_DB_UPGRADE_SP_UPTIME_STEPS procedure before it completed.
EXEC inuser.IN_DB_UTIL_SP_CLEAR_IDENTIFIER 'ALL', @DEBUG = 'YES'; Procedure Name: IN_DB_UTIL_SP_CLEAR_IDENTIFIER *************** DEBUG INFORMATION BEGIN *************** ***** Call Stack Start ***** Identifier Depth SPID Procedure ----------- ------ ------ ----------------------------------- SYNC 1 125 IN_DB_UPGRADE_SP_UPTIME_STEPS UPGRADE 1 125 IN_DB_UPGRADE_SP_UPTIME_STEPS UPGRADE 2 125 IN_DB_UPGRADE_SP_SCHEMA_UPGRADE_7230_7403 ***** Call Stack End ***** Current Database Time: 2022-05-02 17:59:58.2870000 Current Activity for the SYNC identifier: SYNC STATUS: EXECUTING SYNC OPERATION: IN_DB_UPGRADE_SP_UPTIME_STEPS SYNC CALLED BY: NULL SYNC START TIME: 2022-05-02 17:52:06.4870000 SYNC MAX TIME: NULL SYNC MAX_MINUTES: 0 SYNC BATCH SIZE: 50000 SYNC SPID: 125 Current Activity for the UPGRADE identifier: UPGRADE STATUS: EXECUTING UPGRADE OPERATION: IN_DB_UPGRADE_SP_SCHEMA_UPGRADE_7230_7403 UPGRADE CALLED BY: IN_DB_UPGRADE_SP_UPTIME_STEPS UPGRADE START TIME: 2022-05-02 17:59:13.5100000 UPGRADE MAX TIME: NULL UPGRADE MAX_MINUTES: 0 UPGRADE OBJECT: inuser.IN_INSTANCE_PROP UPGRADE SUBOJECT: INSTANCE_PROP_IDX_04 UPGRADE FILEGROUP: SECONDARY UPGRADE MAX_DOP: 0 UPGRADE FILLFACTOR: 95 UPGRADE SCHEMA_TARGET: 7.9.0.0 UPGRADE SCHEMA_BASE: 7.1.5.2 UPGRADE SCHEMA_VERSION: 7.1.5.2u UPGRADE IS_SYNC_UPGRADE: YES UPGRADE SPID: 125 Successfully cleared the session attributes and callstack for the SYNC and UPGRADE identifiers. The CALLSTACK is empty. Current Database Time: 2022-05-02 17:59:58.2970000 Current Activity for the SYNC identifier: SYNC STATUS: IDLE SYNC OPERATION: NULL SYNC CALLED BY: NULL SYNC START TIME: NULL SYNC MAX TIME: NULL SYNC MAX_MINUTES: NULL SYNC BATCH SIZE: NULL SYNC SPID: NULL Current Activity for the UPGRADE identifier: UPGRADE STATUS: IDLE UPGRADE OPERATION: NULL UPGRADE CALLED BY: NULL UPGRADE START TIME: NULL UPGRADE MAX TIME: NULL UPGRADE MAX_MINUTES: NULL UPGRADE OBJECT: NULL UPGRADE SUBOJECT: NULL UPGRADE FILEGROUP: NULL UPGRADE MAX_DOP: NULL UPGRADE FILLFACTOR: NULL UPGRADE SCHEMA_TARGET: NULL UPGRADE SCHEMA_BASE: NULL UPGRADE SCHEMA_VERSION: NULL UPGRADE IS_SYNC_UPGRADE: NULL UPGRADE SPID: NULL *************** DEBUG INFORMATION END ******************