Shrink Database Backlink Performance Improvements

Improve performance for backlinking pointers for shrink database operations.

The shrink database operations for 15.7 SP100 and later require that all text and image columns have backlinking pointers to the home data row in the first text page. By default, the text and backlinking pointers are created and maintained if the table contains text, image, or unitext columns. However, this can impact the performance of these commands:

create clustered index
reorg rebuild
reorg defrag
alter table (which involves data copy)
- unpartition
- split or move partition
- drop a column
- add a NOT NULL column
- change table scheme from APL to DOL, or vice-versa
- modify any of these properties of a column
  - the datatype
  - from NULL to NOT NULL, or vice-versa
  - decrease length
  - increase the length of a number column (for example, from tinyint to int)

To improve performance, 15.7 SP121 and later maintains backlinking pointers for the above commands only when tables are set up for replication using text and image backlinking pointers, in which case sp_reptostandby, sp_setreptable, and sp_setrepcol are run without the use_index parameter. Otherwise, the tables are marked without text and image backlinking pointers after the command execution.

Important

Since shrink database operations require text and image backlinking pointers be maintained, SAP strongly suggests you follow these steps before shrinking a database:

Run the proposed shrink database command, adding the qualifier with check_only
Run dbcc shrinkdb_setup for each table noted by that command as having its text affected

If you have already initiated a shrink database operation, do not run any of above commands in the database being shrunk, otherwise, the operation can be halted with error 5066:

Table <table name> contains off-row columns that do not link back to their owning rows.