Why SQL Server 2008 Full Text Index Never Seems to Complete?
Our website has a SQL Server 2008 R2 Express Edition database with full-text indexing for our website search. Each time a new record is added or updated in one of the indexed tables, the indexing process never seems to complete. I have been monitoring the status over the last several weeks using basically the same query found on this site: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-search/2155/Why-is-this-population-taking-so-long This is what I see when I run the query (click for full size):
The newest records in the indexed tables never complete and are not searchable. Even though there is not very much data in the tables, I have waited days to see if the indexing completes, but nothing ever changes. The only way I am able to get the indexing to complete successfully is to either rebuild the catalog or to drop and re-create all of the indexes. Every time I have done that, the same problem ends up coming back as soon as the first new record is added. Here are the server stats just in case:
- Quad-Core AMD Opteron 2.34GHz
- 4GB RAM
- Windows Server 2008 R2 Enterprise SP1 x64
- SQL Server 2008 R2 Express Edition with Advanced Services x64
How do I get SQL server full-text index?
To create SQL server full text index choose your table and right click on that table and select the “Define Full-Text Index” option. Now select Unique Index. It is compulsory that for “Full Text Index” table must have at least one unique index. Select columns name and language types for columns. Here below is a script I created using cursors to rebuild and populate full indexes for any table that has one for MSSQL2008. This is working in a production environment with databases migrated from a MSSQL 2000 server. I have turned off change tracking and just run this stored procedure via SQL Server Agent. If you were using express, you could use a VBS script to run it via Task Scheduler. It was important in the script to do a rebuild first on every catalog before trying to populate the indexes.
CREATE PROCEDURE [dbo].[rebuild_repopulate_fulltext] AS BEGIN Declare @cmdA NVARCHAR(255) Declare @cmdB NVARCHAR(255) Declare @cmdC NVARCHAR(255) DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd NVARCHAR(500) DECLARE @fillfactor INT DECLARE @Catalog VARCHAR(255) DECLARE @Schema VARCHAR(255) SET @fillfactor = 90 DECLARE DatabaseCursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ('master','msdb','tempdb','model','distribution') ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN -- rebuild fulltext catalog set @cmd = 'DECLARE CatalogCursor CURSOR FOR SELECT t.name AS TableName, c.name AS FTCatalogName, s.name as schemaname FROM ['+ @Database + '].sys.tables t JOIN ['+ @Database +'].sys.fulltext_indexes i ON t.object_id = i.object_id JOIN ['+ @Database + '].sys.fulltext_catalogs c ON i.fulltext_catalog_id = c.fulltext_catalog_id JOIN ['+ @Database + '].sys.schemas s ON t.schema_id = s.schema_id' --PRINT @cmd EXEC (@cmd) OPEN CatalogCursor FETCH NEXT FROM CatalogCursor INTO @Table, @Catalog, @Schema WHILE @@FETCH_STATUS = 0 BEGIN SET @cmdB = 'USE ['+ @Database + ']; ALTER FULLTEXT CATALOG ' + @Catalog + ' REBUILD;' --PRINT @cmdB EXEC (@cmdB) FETCH NEXT FROM CatalogCursor INTO @Table, @Catalog, @Schema END CLOSE CatalogCursor OPEN CatalogCursor FETCH NEXT FROM CatalogCursor INTO @Table, @Catalog, @Schema WHILE @@FETCH_STATUS = 0 BEGIN SET @cmdC = 'USE ['+ @Database + ']; ALTER FULLTEXT INDEX ON ['+ @Database + '].[' + @Schema + '].[' + @Table + '] START FULL POPULATION;' --PRINT @cmdC EXEC (@cmdC) FETCH NEXT FROM CatalogCursor INTO @Table, @Catalog, @Schema END CLOSE CatalogCursor DEALLOCATE CatalogCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor ENDDoes anyone have a method that doesn't require cursors?