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