Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Wednesday, 30 April 2008

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.

Wednesday, 30 April 2008 13:44:30 (Central Standard Time, UTC-06:00) | Comments [0] |  Full-Text#
Friday, 25 April 2008

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;

 

Friday, 25 April 2008 12:00:46 (Central Standard Time, UTC-06:00) | Comments [0] |  Full-Text#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
DTA - Failed to initialize MSDB dat...
Check the Uptime of a Windows Serve...
Recent Posts
Archive
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll