Why SQL Server 2008 Full Text Index Never Seems to Complete?

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 END

Does anyone have a method that doesn't require cursors?