Enhanced Checking Functionality for Shrinking Databases

Additional checking capabilities for the shrink database functionality have been added.

The alter database command is used to shrink databases. The syntax is:

alter database database_name 
. . . 
   off database_device {=size | [from page_number] [to page_number]} 
   [, database_device…] 
   [with timeout='time'] 
   [with check_only]

The with check_only command option is used to check the expected results of shrinking the database rather than actually shrinking the database.

In 15.7 SP100, the with check_only option checks for:

tables with text columns that are not marked as having text back-linked to their owning rows.
All text and image columns are required to have back-linked pointers to the home data row in the first text page.
sufficient room in the remainder of the database to accommodate everything that must be moved.
Any indication of a potential problem is reported.

In 15.7 SP121, the with check_only option additionally checks and reports any potential problems where a significant amount of time could be spent sorting the index.

When checking the indexes, if the results indicate that there could be enough duplicate key entries that the command will spend a significant amount of time sorting the index, the index is reported as a problem. The recommendation is that the index should be dropped before shrinking the database and the index be re-created after the database is shrunk.

Results of the checks being done by with check_only can be compromised by any other work being performed on in the database while the checks are running, or by work that was done after the checks are run but before the actual shrink is completed.