Reports or fixes consistency problems in the FTS index and source tables.
sp_check_text_index server, "index_name", "id_column", "fixit"
– the name of the text server.
– the name of the text server.
– the source identity column name.
– if FALSE, just reports problems. If TRUE, doesn’t report but repairs problems.
sp_check_text_index "textsvr", "text.i_text", "id", "false"
Lists problems on the server named textsvr with the column name text.i_text.
Before using sp_check_text_index you must issue sp_dboption “select into”, true
This procedure addresses three problems:
It generates an sp_refresh_text_index insert for entries in the source table that do not have a matching entry in the index.
It generates an sp_refresh_text_index delete for entries in the index table that have no source table entry.
It generates an sp_refresh_text_index delete for each extra entry where duplicate index entries exist.
In order to determine the index duplicates, it is necessary to select all of the ID values from the index table into a temporary table. If the collection has more than 64K ID values, it will be necessary to change the “batch_blocksize configuration parameter from its default of 0 to 65536 to enable blocked reading of the returned Verity information. If this is not done, FTS will attempt to real all ID values in one read and fail with a Verity error of “-27.”
None
Any user can execute sp_check_text_index.
