Upgrading text data after changing character sets

If you have changed an Adaptive Server’s character set to a multibyte character set use dbcc fix_text to upgrade text values.

The syntax is:

dbcc fix_text ({table_name | table_id})

Changing to a multibyte character set makes the management of text data more complicated. A text value can be large enough to cover several pages; therefore, Adaptive Server must be able to handle characters that span page boundaries. To do so, Adaptive Server requires additional information on each of the text pages. The System Administrator or table owner must run dbcc fix_text on each table that has text data to calculate the new values needed.

To see the names of all tables that contain text data, use:

select sysobjects.name
from sysobjects, syscolumns
where syscolumns.type = 35
and sysobjects.id = syscolumns.id

The System Administrator or table owner must run dbcc fix_text to calculate the new values needed.

The syntax of dbcc fix_text is:

dbcc fix_text (table_name | table_id) 

The table named must be in the current database.

dbcc fix_text opens the specified table, calculates the character statistics required for each text value, and adds the statistics to the appropriate page header fields. This process can take a long time, depending on the number and size of the text values in a table. dbcc fix_text can generate a large number of log records, which may fill up the transaction log. dbcc fix_text performs updates in a series of small transactions so that if a log becomes full, only a small amount of work is lost.

If you run out of log space, clear out your log (see Chapter 27, “Backing Up and Restoring User Databases”). Then restart dbcc fix_text, using the same table that was being upgraded when the original dbcc fix_text halted. Each multibyte text value contains information that indicates whether it has been upgraded, so dbcc fix_text upgrades only the text values that were not processed in earlier passes.

If your database stores its log on a separate segment, you can use thresholds to manage clearing the log. See Chapter 29, “Managing Free Space with Thresholds.”

If dbcc fix_text cannot acquire a needed lock on a text page, it reports the problem and continues with the work, like this:

Unable to acquire an exclusive lock on text page 408. This text value has not been recalculated.  In order to recalculate those TEXT pages you must release the lock and reissue the dbcc fix_text command.