Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Nov 16 '11 at 23:35

This question came from our site for professional and enthusiast programmers.

5 Answers 5

up vote 3 down vote accepted

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!

share|improve this answer

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

share|improve this answer
    
up-voting @Brandon. Read this section, "The main cause for reduced full-text indexing performance is hardware-resource limits:" –  MacGyver Jun 21 '12 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] 
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?

share|improve this answer

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.

share|improve this answer
    
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. –  Jeff Nov 21 '11 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. –  Jeff Dec 1 '11 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? –  Craig Efrein Dec 2 '11 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.

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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