17.10 - DEFRAGMENT - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Utilities

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
Because of the difficulty in determining how severely a cylinder is fragmented, only trained personnel should use this command.

The DEFRAGMENT command causes free sectors on each qualifying logical cylinder contained within the current scope to be combined together into a single contiguous block of free space on that cylinder. The scope used is designated by the SCOPE command or the default if no SCOPE command was issued.



Syntax Elements

To defragment every cylinder whether a cylinder meets the criteria specified in Usage Notes below.
To bypass the confirmation prompt that asks if you are sure you want to run this command.

The Ferret ABORT command can be used to halt this operation during command execution. After it is initiated, ABORT stops the defragmenting and reports the current status of the vprocs.

Up to 30 seconds can elapse before the ABORT operation begins.

Usage Notes

A cylinder becomes fragmented as a result of modifications to table data. As data blocks (DBs) are created, deleted, or change size as a result of these modifications, what started out as a small number of large free spaces on the newly allocated cylinder becomes a large number of small free spaces. Although there may still be a large total number of free sectors on the cylinder, with time there are fewer ranges of contiguous free sectors that are large enough to store new DBs. When new DBs cannot be stored on the cylinder, new free cylinders must be found and allocated.

The defragmentation process moves the data blocks closer together, coalescing the free space into areas that are large enough that new DBs can be allocated on the cylinder. This can reduce the need to allocate free cylinders for new data storage. The defragmentation process actually moves DBs from the fragmented cylinder to a new cylinder, where the DBs can be packed together, leaving a single, large set of consecutive free sectors. The cylinder from which the DBs were moved is freed, and made available for new allocations.

Consequently, defragmentation is a costly operation, so should be performed only when system resources are plentiful.

Unless the FORCE option is used, the DEFRAGMENT command performs defragmentation of a logical cylinder within the scope only if all of the following criteria are met.

  • More than one set of free sectors is on the cylinder, so there is something that could be combined.
  • 25% or more free sectors are on the cylinder.
  • The average number of sectors comprising a set of free sectors is less than the average size of a data block on that cylinder.

The FORCE option causes the DEFRAGMENT command to defragment a logical cylinder within the scope if it has more than one block of free space. In that case, it will defragment a cylinder regardless of the percentage of free sectors or average block sizes.

Note that the DEFRAGMENT operation does not change the size or content of any data blocks. Existing blocks may be moved, but they are otherwise unchanged.

For each AMP, the Ferret indicates how many cylinders it will attempt to defragment. For example:

vproc  2  response
Requested Cylinder Range had 197 cylinders placed on the defrag list

Upon completion, for each amp, the actual number of cylinders defragmented will be logged into the system's message log. For example:

002507 14:47:07 043100d6 ... 44 8
340516600|appl|1|S|I|U|0|0|M|0|0|PDE|0|0|0|1#PDElogd: Event number 34-05166-00
(severity 10, category 10)
5166: Defragment of cylinder[s] occurred.

On Wed Jun 22 14:47:07 2016 on NODE 001-01, VPROC 2, partition 9, task
197 cylinder[s] defragmented

Defragmentation proceeds as a background task, and can continue to run even after the Ferret prompt returns. To determine when defragmentation has completed, check DBC.SW_Event_Log for event_tag=34-05166-00.

Vantage can isolate some file system errors to a specific data or index subtable, or to a contiguous range of rows ("region") in a data or index subtable. In these cases, Vantage marks only the affected subtable or region down. This improves system performance and availability by allowing transactions that do not require access to the down subtable or rows to proceed, without causing a database crash that would require a system restart.

If DEFRAGMENT encounters down regions, it skips these regions, and displays the percentage of total space that was skipped.

Example: DEFRAGMENT command output

The following example shows the output that DEFRAGMENT generates:

Ferret ==>
defrag /y
Defrag has been sent to all AMP vprocs in the SCOPE.
Type ‘ABORT’ to stop them before completion

vproc 0 response
Requested Cylinder Range had 5 cylinders placed on the defrag list

vproc 1 response
Requested Cylinder Range had 9 cylinders placed on the defrag list