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): Full-Text Index Status

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

5 Answers 5


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.

Database Properties Window

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!


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).

  • up-voting @Brandon. Read this section, "The main cause for reduced full-text indexing performance is hardware-resource limits:" Jun 21, 2012 at 4:20

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] 

Declare @cmdA NVARCHAR(255)
Declare @cmdB NVARCHAR(255)
Declare @cmdC NVARCHAR(255)
DECLARE @Database VARCHAR(255)   
DECLARE @fillfactor INT 

SET @fillfactor = 90 

SELECT name FROM MASTER.dbo.sysdatabases   
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')   

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  

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

    SET @cmdB = 'USE ['+ @Database + ']; ALTER FULLTEXT CATALOG ' + @Catalog + ' REBUILD;'
    --PRINT @cmdB
    EXEC (@cmdB)

    FETCH NEXT FROM CatalogCursor INTO @Table, @Catalog, @Schema

   CLOSE CatalogCursor   

   OPEN CatalogCursor   

   FETCH NEXT FROM CatalogCursor INTO @Table, @Catalog, @Schema

    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

   CLOSE CatalogCursor   
   DEALLOCATE CatalogCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  

CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor


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


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.

  • Right now my indexes are set to automatic change tracking. I've read that changing it to manual and using triggers can actually cause problems when one person inserts or updates a record in an indexed table before the triggered index update from the previous table update hasn't finished yet. It seems like the automatic change tracking should work... especially since I don't have that many records in my tables.
    – Jargs
    Nov 21, 2011 at 23:56
  • I reviewed my setup against the example link you gave, but everything appears to be in order. I even tried repairing my SQL Server install, but the problem remains.
    – Jargs
    Dec 1, 2011 at 17:08
  • 1
    I don't know if this is related, but I once had a problem with fulltextcatalog population because of Word Breaker problems. If you Open SSMS and select Storage under your database, then right click on the full text catalog. Go to Tables/Views and look at the Language for Word Breaker. Are there columns that use different languages for word breakers? I have noticed, that if you have different languages for Word Breakers in the same table, the population doesn't work. Maybe unrelated but who knows? Dec 2, 2011 at 9:21

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.


Use this code:

ALTER FULLTEXT INDEX ON dbname.dbo.tablename

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.