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

Question

We are writing unit tests for our ASP.NET application that run against a test SQL Server database. That is, the ClassInitialize method creates a new database with test data, and the ClassCleanup deletes the database. We do this by running .bat scripts from code.

The classes under test are given a connection string that connects to the unit test database rather than a production database.

Our problem is, that the database contains a full text index, which needs to be fully populated with the test data in order for our tests to run as expected.

As far as I can tell, the fulltext index is always populated in the background. I would like to be able to either:

1. Create the full text index, fully populated, with a synchronous (transact-SQL?) statement, or
2. Find out when the fulltext population is finished, is there a callback option, or can I ask repeatedly?

My current solution is to force a delay at the end the class initialize method - 5 seconds seems to work - because I can't find anything in the documentation.

Overwind

Answer 1

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.

Dilly

I've come across a page stating that the 'PopulateStatus' property is/will be deprecated in a future release of SQL Server, but I havn't been able to find an alternative to check the population status. Do you have any ideas? See first note: technet.microsoft.com/en-us/library/ms190370.aspx – Samarium

@Samarium According to the detailed list of deprecated features, technet.microsoft.com/en-us/library/cc646010.aspx, there's no replacement! Joe Stefanelli in another question posted a comment suggesting a possible alternative: #3680953 but this all looks very messy. Maybe there'll be a new system view for FTE with this info in it in SQL Server 2012... – Dilly

Actually technet.microsoft.com/en-us/library/ms190370.aspx states that we should use OBJECTPROPERTYEX on the table instead. It would be better performance wise, give more detail and stay supported. technet.microsoft.com/en-us/library/ms188390.aspx Following exists equivalent to the mentioned properties: TableFulltextPopulateStatus, TableFulltextItemCount, TableFulltextCatalogId, TableFullTextMergeStatus, and there are more (look at the link) – Aikens

Answer 2

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)

Clear

Combining this one with Daniel Renshaw's answer makes for a great query for multiple catalogs. – Phillisphilly

Both of queries are excellent, this one is great if you don't need to know specifics which is what I wanted. The accepted answer added more to this one. – Perdition

Why would you query the sys.fulltext_catalogs table when you're not using anything from it? You might as well remove the last line of from query. Or alternatively remove your @CatalogName variable and use [name] in your SELECT statement instead. – Buttery

@Buttery you are indeed correct! I have removed it from the code – Clear

Answer 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

Interesting

@Valamas: if you don't you're still blocking you're thread. In that case a Thread.Sleep could be better. But it's not related to the question/answer here. – Aikens

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

Overwind

Citation for Gareth's MSDN Statement msdn.microsoft.com/en-us/library/ms190370(v=sql.90).aspx : "It is usually a better option to check the corresponding PopulateStatus property at the table level, TableFullTextPopulateStatus in the OBJECTPROPERTYEX system function. This and other new full-text properties in OBJECTPROPERTYEX provide more granular information about full-text indexing tables" – Clear

Answer 5

To wait for a full text catalog to finish population of all its tables and views without having to specify their names, you can use the following stored procedure. This is a combination of JohnB's answer to this question and the answer by cezarm to a related question:

CREATE PROCEDURE WaitForFullTextIndexing
@CatalogName VARCHAR(MAX)
AS
BEGIN
    DECLARE @status int;
    SET @status = 1;
    DECLARE @waitLoops int;
    SET @waitLoops = 0;

    WHILE @status > 0 AND @waitLoops < 100
    BEGIN       
        SELECT @status = FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        FROM sys.fulltext_catalogs AS cat;

        IF @status > 0
        BEGIN
            -- prevent thrashing
            WAITFOR DELAY '00:00:00.1';
        END
        SET @waitLoops = @waitLoops + 1;
    END
END

Althaalthea

When selecting the status value from FULLTEXTCATALOGPROPERTY, why do you put FROM sys.fulltext_catalogs. That will just make it return the same value however many times there are full text indexes in your database, right? – Fishbein

Looks like you're correct @BVernon. This similar SO thread answer doesn't select from sys.fulltext_catalogs when using FULLTEXTCATALOGPROPERTY #1092312. Thanks for the nice proc, Henno! – Toledo

Answer 6

I did the following:

        var indexIsPopulating = true;
        var stopWatch = new Stopwatch();
        stopWatch.Start();
        while (indexIsPopulating)
        {
            System.Threading.Thread.Sleep(500);
            using var con = new SqlConnection(databaseConnectionString);
            // using dapper here - but you just need to run query on databsae
            var status = await con.QueryFirstAsync<int>("SELECT OBJECTPROPERTY(OBJECT_ID('dbo.MyTableName'), 'TableFulltextPopulateStatus'); ");
            if (status == 0)
            {
                indexIsPopulating = false;
            }
            else if (stopWatch.ElapsedMilliseconds > 60000) // 1 minute
            {
                stopWatch.Stop();
                throw new Exception("Full Text Index failed to populate within 1 minute.");
            }
        }
        stopWatch.Stop();

Adrastus

Answer 7

I had the same issue using a dockerized sql server with a full text search. The database was seeded successfully but when I ran the test, the index populate status didn't finished yet, so no results were returned in my test.

In order to make sure the indexes were rebuilt before running the test, I used a spinlock with a query that identifies if any of my tables have an index being rebuilt:

public MyApplication SeedDatabase( Action<MyDbContext> seed )
{
    using var scope = Services.CreateScope();
    var scopedServices = scope.ServiceProvider;
    var db = scopedServices.GetRequiredService<MyDbContext>();

    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();

    CreateFullTextCatalog();
    CreateFullTextIndexes();

    seed.Invoke( db );
    db.SaveChanges();

    SpinWait.SpinUntil( () => IsFullTextPopulateStatusIdle(), TimeSpan.FromSeconds( 5 ) );

    return this;

    bool IsFullTextPopulateStatusIdle() => db.Database.SqlQuery<int>( $"select case when EXISTS(SELECT OBJECTPROPERTY(object_id, 'TableFulltextPopulateStatus') from sys.tables where OBJECTPROPERTY(object_id, 'TableFulltextPopulateStatus') <> 0) then 1  else 0  end as value" ).Single() == 0;

    void CreateFullTextCatalog() => db.Database.ExecuteSqlRaw( File.ReadAllText( Path.Combine( Directory.GetCurrentDirectory(), "Sql", "FT_Catalog.sql" ) ) );

    void CreateFullTextIndexes() => db.Database.ExecuteSqlRaw( File.ReadAllText( Path.Combine( Directory.GetCurrentDirectory(), "Sql", "FullTextIndexes.sql" ) ) );
}

Aikoail