SQL Server 2008 Full Text Index Never Seems to Complete

Question

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

Answer 1

I finally found the cause of my problem!

I tried for months to track down the issue, but finally gave up, disabled automatic change tracking, just manually initiated incremental population and moved on with my life.

Meanwhile, there had been another nagging error that I was having trouble tracking down. Periodically the website would throw a DB connection error:

Cannot open database "XXXX" requested by the login. The login failed. Login failed for user 'XXXX'.

It turned out that both of these problems had the same solution. All I had to do is turn off a database setting called Auto Close. To do this, just right-click the database and click properties. In the properties window select Options and set "Auto Close" to false.

As soon as I disabled Auto Close, my DB login issues went away and the automatic change tracking worked perfectly.

Thanks again for everyone's help. I appreciate it!

Answer 2

Curious if you've gone through the troubleshooting steps in BOL for Full-Text Performance - http://technet.microsoft.com/en-us/library/ms142560.aspx.

I'm betting that SQL Server is eating all your memory and not letting the filter daemon have any, so your population is slow, since it's most likely having to swap stuff down to the page file. You should limit the amount of memory that SQL can use (I'd think to somewhere around 3GB given your current system specs - that'd leave 1GB for FDHost and the OS).

Answer 3

Here 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?

Answer 4

Usually is recommended to update the full-text catalog using triggers. That is the aproach I use on mssql, but in my case because I've a localized application with several specific requirements that lead me to a solution using triggers, that solutions works 100% starting 2 years back.

Review your implementation against this example.

Answer 5

Not sure what the root cause is in your situation, but this can happen after a backup occurs. Not sure if that's what's happening in your case or how that table is different from the others. Now you have me curious. Do you have SQL replication turned on?

For a temporary fix, I'd perform a "Crawl" (population) on the table when this happens.

http://msdn.microsoft.com/en-us/library/ms142575(v=sql.105).aspx

Use this code:

ALTER FULLTEXT INDEX ON dbname.dbo.tablename
START FULL POPULATION;