A Developer's Blog · Full-Text Indexing

If you have installed SQL Server 2008 without making any modifications to the default service settings and you’re trying out the AdventureWorks2008 sample database, you may be getting some similar errors as the following in the Application event log:

A fatal error occurred during a full-text population and caused the population to be cancelled. Population type is: FULL; database name is AdventureWorks2008 (id: 7); catalog name is AW2008FullTextCatalog (id: 5); table name ProductReview (id: 354100302). Fix the errors that are logged in the full-text crawl log. Then, resume the population. The basic Transact-SQL syntax for this is: ALTER FULLTEXT INDEX ON table_name RESUME POPULATION.

The same errors are logged in the \MSSQL10.SQL2008\MSSQL\Log\SQLFT0000700005.LOG log file. Here you also get an error number: Error: 30059, Severity: 16, State: 1.

I have seen the issue occur when the SQL Full-text Filter Daemon Launcher service was running with the NT AUTHORITY\NETWORK SERVICE account. Switching the service to use the Local System account solved the issue. Then the Full Text Catalog would rebuild without any issue.

To rebuild the Full Text Catalog in Management Studio, open your database in the Object Explorer, under Storage > Full Text Catalogs there’s an entry called AW2008FullTextCatalog (in the case of the sample DB). Double-clicking this shows the Full-Text Catalog Properties. Select the Rebuild catalog radio button and click OK to rebuild the catalog.

Alternatively you could execute following statement on the AdventureWorks database:

ALTER FULLTEXT CATALOG [AW2008FullTextCatalog] REBUILD;

After waiting for about 15 seconds (there’s not a lot of data to be indexed) if you open the properties again you should see the Last Population Date filled in with the current timestamp. Another way to tell if it was successful is to look at the FT log file, error 30059 should not be mentioned anymore.

Or try using a Full Text query:

select * from Production.ProductReview
where contains(Comments, 'heavy');

This should output two records.

I’d just like to mention that Robert Sheldon wrote a really interesting article on how Full-Text Indexing works in both SQL Server 2005 and 2008. If you’re new to full-text indexing or if you want to know how it has evolved in 2008, I really recommend reading this.

There are several interesting queries in the article, such as how to retrieve a list of terms that are being indexed using the sys.dm_fts_index_keywords dynamic management function. This is a new feature of SQL Server 2008.

The article can be found on the simple-talk site and is called Understanding Full-Text Indexing in SQL Server.

This is the first article in a series on full-text indexing. The next one is called Full-Text Indexing Workbench and is written completely in T-SQL .