Full Text Catalog not populating

All we need is an easy explanation of the problem, so here it is.

I just created a full text catalog :

  • Enable full text (right click on db => file => enable ful text indexing)
  • Create catalog (Storage => Fulle text Catalog => New Catalog => "FTCatalog" => OK)
  • Add column to catalog (right click on catalog => properties => table/views add table/columns , automatic => ok)

The table has 270k lines, I created the catalog this morning and still it has 0 Item Count and the populate status is 1.

The logs in […]\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLFT0000800007.LOG doesn’t show anything more than (i tried to recreate it 10 min ago)

2014-06-30 16:32:03.93 spid23s Informational: Full-text Full
population initialized for table or indexed view ‘[XXX].[dbo].[XXX]’
(table or indexed view ID ‘1771153355’, database ID ‘8’). Population
sub-tasks: 4.

All SPs are installed.

I tried with some smaller table, it’s the same.

The service "SQL Server FullText Search" is running.

Nothing on the EventViewer.

Any idea ?

UPDATE

In Sql Server Log I have these two thing :

Changing the status to PAUSE for full-text catalog "XXX" (7) in database "XXX" (8). This is an informational message only. No user action is required.

Changing the status to PAUSE for full-text catalog "XXX" (7) in database "XXX" (8). This is an informational message only. No user action is required.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You might try scripting it out to see if you get a different behaviour. This simple example runs in under a minute on my local SQL 2005 instance:

USE master
GO

SET NOCOUNT ON
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
BEGIN
    ALTER DATABASE fullTextDemo3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE fullTextDemo3
END
GO

IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
CREATE DATABASE [fullTextDemo3]
GO

ALTER DATABASE fullTextDemo3 SET RECOVERY SIMPLE
GO

USE fullTextDemo3
GO

CREATE TABLE dbo.nearnessTest (
    rowId           INT IDENTITY,
    keywords        VARCHAR(MAX) NOT NULL,
    notes           VARCHAR(100) NOT NULL,

    CONSTRAINT PK_nearnessTest PRIMARY KEY ( rowId )
)
GO


INSERT dbo.nearnessTest
--SELECT TOP 270000 m1.[text], ''
SELECT m1.[text], ''
FROM sys.messages m1
--  CROSS JOIN sys.messages m2
WHERE m1.language_id = 1033
  --AND m2.language_id = 1033
GO

-- Create the catalog
IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ftc_List3' ) 
CREATE FULLTEXT CATALOG ftc_List3
GO

-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.nearnessTest (keywords) KEY INDEX PK_nearnessTest ON ftc_List3 WITH CHANGE_TRACKING MANUAL -- CHANGE_TRACKING OFF, NO POPULATION
GO

SELECT 'before' ft, * FROM sys.fulltext_indexes
GO

DECLARE @i INT 
SET @i = 0

WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN

        SELECT outstanding_batch_count, *
        FROM sys.dm_fts_index_population
        WHERE database_id = DB_ID()

        --SELECT *
        --FROM sys.dm_fts_outstanding_batches
        --WHERE database_id = DB_ID()

    WAITFOR DELAY '00:00:05'

    SET @i = @i + 1
    IF @i > 60 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END

END

SELECT 'after' ft, * FROM sys.fulltext_indexes
GO

SELECT * 
FROM dbo.nearnessTest 
WHERE CONTAINS( keywords, 'error' )

What happens when you issue an explicit start for the index population, eg

ALTER FULLTEXT INDEX ON dbo.yourTable START FULL POPULATION;

Run these summary functions

SELECT FULLTEXTSERVICEPROPERTY ( 'IsFulltextInstalled' ) IsFulltextInstalled
SELECT DATABASEPROPERTY('fullTextDemo3','IsFulltextEnabled') IsFulltextEnabled
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'AccentSensitivity ') AS AccentSensitivity 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'IndexSize ') AS IndexSize 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ItemCount ') AS ItemCount 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'LogSize ') AS LogSize 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'MergeStatus ') AS MergeStatus 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateCompletionAge ') AS PopulateCompletionAge 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateStatus ') AS PopulateStatus 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'UniqueKeyCount ') AS UniqueKeyCount 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ImportStatus ') AS ImportStatus

Method 2

I finnaly found something :

Reboot the server !

I absolutely have no idea why.

Thanks @wBob for your help.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply