Return to LifeAsBob - SQL Server | Full-Text

Full-Text Index Population Errors, 7683

Bob Admin — Wed, 30 Apr 2008 19:44:30 GMT

Tips for Troubleshooting Full-Text Index Population Errors

The example below is a typical error you will receive in an email and is also the same error found in the SQL Server error log.

BCTFSQL01, Error: 7683, Severity: 16, State: 1.

Errors were encountered during full-text index population for

table or indexed view '[STS_Content_TFS].[dbo].[Docs]',

database 'STS_Content_TFS' ( table or indexed view ID '2089058478', database ID '15' )

When an error occurs during a crawl, the Full-Text Search crawl logging facility creates and maintains a crawl log, which is a plain text file.

Each crawl log corresponds to a particular full-text catalog. By default crawl logs for a given instance, in this case, the first instance, are located in Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder. The crawl log file follows the following naming scheme:

SQLFT<DatabaseID><FullTextCatalogID>.LOG[<n>]

For example, SQLFT0001500005.LOG is the crawl log file for a database with database ID = 15, and full-text catalog ID = 5.

The LOG at the end of the file name indicates that there is only one crawl log file for this database, although in some cases there could be 2, then there would be a 2 at the end of the file name indicating that there are two crawl log files/catalog pair.

So for TFS the crawl log is located at the following location:

\\{server name}\c$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

You need to check the crawl log for the error to get the full-text key value which is a hex value. Part of the error entry in the crawl log will be somethin like this:

Error '0x8004170c: The document format is not recognized by the filter.' occurred during full-text index population for table or indexed view '[STS_Content_TFS].[dbo].[Docs]' (table or indexed view ID '2089058478', database ID '15'),

full-text key value 0x51212D1F9462944E97329D29B35E050F. Attempt will be made to reindex it.

Once you have the full-text key value, connect to the database, in this case ‘STS_Content_TFS’ on {server name} and query the table, in this case it’s ‘docs’ where the id equals the full-text key value.

I.E.
SELECT *
FROM [STS_Content_TFS].[dbo].[Docs]
WHERE ID = 0x51212D1F9462944E97329D29B35E050F

There is a column called DirName, If you append http://bctfapp01/ to the front of the contents of the DirName column and paste that into IE it will take you out to the website where the document with the error is located.

I.E.
http://bctfapp01/sites/Tax Team Studio/katn/Shared Documents/Projects/DDM

From there the actual issue could be anything, so it’s best to look at who last updated the document and contact them to take care of the issue.

Fulltext migrate from sql 2000 to 2005

Bob Admin — Fri, 25 Apr 2008 18:00:46 GMT

When migrating a database, via backup from a sql server 2000 instance to a sql server 2005 instance, the full-text catalogs must be rebuilt.

This is most evident when a full backup fails with the following error:

System.Data.SqlClient.SqlError: The backup of full-text catalog 'DevStudio' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)

To correct this error you can uses pieces of the following script, ensure to change your database, paths and catalogs as appropriate for your environment:

Create a Full-Text Catalog and Index

/**************** Return info about Full-Text Catalog and Index *********************/

-- Obtain a list of all the full-text catalogs linked to the current database by running this query:

EXEC sp_help_fulltext_catalogs>

-- SELECT name FROM sys.fulltext_catalogs;>

-- Returns information about full-text catalog properties

SELECT FULLTEXTCATALOGPROPERTY('DevStudio','PopulateStatus')>

-- Obtain a list of all the tables in the database that have been enabled for full-text processing:

sp_help_fulltext_tables;>

sp_help_fulltext_tables @fulltext_catalog_name = 'DevStudio'; -- all the tables linked to that full-text catalog>

-- select * from sys.fulltext_indexes >

-- Obtain a list of all the columns in the database that have been enabled for full-text processing:

sp_help_fulltext_columns;>

sp_help_fulltext_columns @table_name = 'XmlFile' -- returns list of all the columns for a single table>

-- select * from sys.fulltext_index_columns >

/**************** Create a Full-Text Catalog and Index *********************/

-- CREATE FULLTEXT CATALOG DevStudio;

create FULLTEXT CATALOG [CDD]>

in PATH N'D:\Shr2k5a\data\FTSearch'>

AUTHORIZATION [dbo]>

CREATE FULLTEXT INDEX ON XmlFile(Type, Content)>

KEY INDEX PK_XmlFile>

ON CDD;>

/**************** Rebuild and Populate Full-Text Catalog *******************/

-- Rebuild, but do not repopulate, the full-text catalog:

EXECUTE sp_fulltext_catalog 'CDD', 'Rebuild';>

-- NOTE: The sp_fulltext_database stored procedure with the ENABLE option may be used to rebuild all known full-text catalogs>

-- Start a full population of the full-text catalog

EXECUTE sp_fulltext_catalog 'CDD_07', 'start_full';>

/**************** Drop a Full-Text Index and Catalog **********************/

-- Unregister a table for full-text processing:

DROP FULLTEXT INDEX ON dbo.XmlFile;>

-- Drop the full-text catalog from the file system and its metadata from the catalog views:

DROP FULLTEXT CATALOG DevStudio;>