Note: To view full execution information in the Script Properties dialog, right-click a script from the Detail pane of the Job Monitor and select Properties.
When execution of a scheduled script is interrupted, you can use the Restart function to start it again from the Scripts/Job Monitor. This function lets you easily resume execution after it is cancelled or stops due to an error, a system failure, or a system shutdown. Execution automatically resumes from the correct point.
Review the following best practices for script restart:
To restart a script with the Restart function:
If a script was interrupted due to failure, resolve the problem behind the failure. See Troubleshoot Script Execution Errors for troubleshooting tips.
Tip: To determine why a script failed, check the Status Message column of the Scripts/Job Monitor. Also check the script log. To view the log, right-click the script in the Detail pane and select Retrieve Scripts Logs.
Restart for live reorganization and partitioning scripts is managed by QSA. The agent determines whether a script can be restarted, at what point it should be restarted, and which statements must be run. Only one restart command is used.
The restart point for live reorganization and partitioning scripts is the “point of failure”. This is the point at which execution failed or was cancelled:
When a live reorganization or partitioning script is restarted, QSA first validates the script. QSA makes sure all LiveReorg commands are scripted correctly and that LiveReorg objects and triggers have not been changed or dropped.
If QSA detects problems with LiveReorg commands or objects, a script cannot be restarted. In this case, the script is given a status of 324 Aborted no restart or 325 Aborted restart after you try to restart it. For help with cleanup of LiveReorg objects, contact Quest Software Support. Once cleanup is performed, a new script can be generated for objects in a failed or cancelled script.
If a live reorganization or partitioning script can be restarted, QSA determines where it should restart—with creation of the copy table or at a point after that. To determine the restart point, QSA checks its QUEST_QSA_PDATA table. This table records all tasks performed during each run of a live reorganization or partitioning script.
The agent also “plays back” or reviews commands and statements from the last run of a script and identifies which statements do not need to be repeated and which
ones do. This ensures that restart is efficient. LiveReorg commands sometimes must be repeated. SQL statements are never repeated. They are only executed if they failed or were not executed during the last run. For example, if a CREATE INDEX statement was successfully executed for a copy index, the copy index is not created again after restart.
Restart begins with creation of a copy table if:
In both cases, execution is initiated with the ALWAYS_RUN and RESTART_POINT commands. The first SQL statement executed is the CREATE TABLE statement that creates a copy of the original table. All remaining SQL statements and LiveReorg commands for the table are then executed.
Restart bypasses creation of a copy table if that table was created during the last
run of a script. In this case, execution is initiated with the ALWAYS_RUN command. The first SQL statement executed is the statement on which execution was interrupted. This can be anywhere after the CREATE TABLE statement that creates a copy of the original table.
If execution failed on any of the statements inside a “parallel block”, execution resumes from the start of the parallel block and the entire block is executed. However, any indexes created during the first run of a parallel block are not created again. (A parallel block is a section of script where parallel QSA processes are used to create indexes or compile dependencies.)
If any WHENEVER, WAIT, or RETRY commands occur after the restart point,
all are active and used for the tasks they apply to, including specific tasks within parallel blocks.
Note If the switch is approved before a script is interrupted, it is still approved after the script is restarted. You do not need to approve the switch for the table that was being reorganized at the time of interruption. However, you do need to approve it for all subsequent tables in the script.
If a live reorganization or partitioning script is interrupted before the switch for a table, DML activity can continue against the table. This is the case whether the table is being reorganized with the online switch or the T-Lock switch. Live transactions are posted to the reorganized copy table when the script is restarted.
If a live reorganization or partitioning script is interrupted during the switch, whether DML activity can continue depends on switch style:
Execution logs for restarted live reorganization and partitioning scripts contain comments that identify the restart statement. One comment is displayed near the top of the log and reads as follows: “Restarting at statement 39” (where 39 is the statement number in the log). A second comment is displayed at the point where QSA restarted execution. This reads: “Restart playback is now off at line 39”.
Logs also identify which script statements QSA played back and which ones were executed:
In both scripts and logs, the restart command for each table is assigned a sequential number. This is displayed in parentheses beside the command. It associates a table with its parallel blocks (used for tasks performed in parallel). The following is an example of the command and sequential number for the second table in a script: