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
Best Answer
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:
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!