How can I know when SQL Full Text Index Population is finished?

Solution 1:

I would like to offer an easier-to-read version of @Daniel Renshaw's answer:

DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'FTS_Demo_Catalog'

SELECT
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
    ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus

Results:

LastPopulated           PopulateStatus
----------------------- ----------------------------------
2012-05-08 14:51:37.000 Idle

(1 row(s) affected)

Solution 2:

You can query the status using FULLTEXTCATALOGPROPERTY (see here: http://technet.microsoft.com/en-us/library/ms190370.aspx).

For example:

SELECT
    FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') AS [ItemCount],
    FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus') AS [MergeStatus],
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS [PopulateCompletionAge],
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus') AS [PopulateStatus],
    FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus') AS [ImportStatus]
FROM sys.fulltext_catalogs AS cat

You might also like to use SQL Profiler to monitor what commands SQL Server Management Studio issues when you bring up the properties dialog for the catalog. The dialog includes an indicatin of population status and all the information shown is queried using T-SQL.

Solution 3:

This is a stored procedure we created based on GarethOwen's answer. It accepts a comma separated list of tables as parameters and waits until full text indexes on all of them have been updated. It does this check every tenth of a second to prevent thrashing the disk and times out after 10 seconds just in case things are running slowly/broken. Useful if your FT searches are across multiple indexes.

Called in the following way:

EXECUTE [dbo].[WaitForFullTextIndexing] 'MY_TABLE,ALTERNATE_NAMES,TAG_GROUP_VALUES,TAG_GROUPS,FIELD_OPTION';

The source:

CREATE PROCEDURE WaitForFullTextIndexing
    @TablesStr varchar(max)
AS
BEGIN
    DECLARE @Tables AS TABLE( [word] [varchar](8000) NULL)

    INSERT INTO @Tables (word) SELECT items from dbo.Split(@TablesStr, ',');

    DECLARE @NumberOfTables int;
    SELECT @NumberOfTables = COUNT(*) from @Tables;

    DECLARE @readyCount int;
    SET @readyCount = 0;

    DECLARE @waitLoops int;
    SET @waitLoops = 0;

    DECLARE @result bit;

    WHILE @readyCount <> @NumberOfTables AND @waitLoops < 100
    BEGIN

        select @readyCount = COUNT(*)
        from @Tables tabs
        where OBJECTPROPERTY(object_id(tabs.word), 'TableFulltextPopulateStatus') = 0;

        IF @readyCount <> @NumberOfTables
        BEGIN
            -- prevent thrashing
            WAITFOR DELAY '00:00:00.1';
        END

        set @waitLoops = @waitLoops + 1;

    END

END
GO

dbo.split is a table value function that everyone must have by now which splits a string on a separator into a temporary table:

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))        
returns @temptable TABLE (items varchar(8000))        
as        
begin        
    declare @idx int        
    declare @slice varchar(8000)        

    select @idx = 1        
        if len(@String)<1 or @String is null  return        

    while @idx!= 0        
    begin        
        set @idx = charindex(@Delimiter,@String)        
        if @idx!=0        
            set @slice = left(@String,@idx - 1)        
        else        
            set @slice = @String        

        if(len(@slice)>0)   
            insert into @temptable(Items) values(@slice)        

        set @String = right(@String,len(@String) - @idx)        
        if len(@String) = 0 break        
    end    
return        
end 

GO

Solution 4:

Thanks Daniel, your answer got me on the right track.

I actually use the following T-SQL statement to ask if the population status of the full text index is Idle:

SELECT OBJECTPROPERTY(object_id('v_doc_desc_de'), 'TableFulltextPopulateStatus')

'v_doc_desc_de' is the name of the database view that we index.

If the population status is not idle, I wait a couple of seconds and ask again, until it is Idle. It is important to wait a small amount of time between checks to ensure the full text population is not slowed down by continuously checking the population status.

The MSDN documentation states that the OBJECTPROPERTYEX function (at table level) is recommended over the FULLTEXTCATALOGPROPERTY statement with property 'PopulateStatus'. It states the following:

The following properties will be removed in a future release of SQL Server: LogSize and PopulateStatus. Avoid using these properties in new development work, and plan to modify applications that currently use any of them.