SQL Script to Check the SQL Server Full Text Index Population Progress

I need to enable the SQL Server full text index on a table with over 20 million rows.   (It’s crazy!)

I know that it will take a long time for the full text index to populate, but I really hope to know the status:  How many items have been indexed?  When might it finish?   The following SQL script gives me the answer:

    FULLTEXTCATALOGPROPERTY(cat.name,'AccentSensitivity') AS [AccentSensitivity],
    FULLTEXTCATALOGPROPERTY(cat.name,'IndexSize') AS [IndexSize],
    FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') AS [ItemCount],
    FULLTEXTCATALOGPROPERTY(cat.name,'LogSize') AS [LogSize],
    FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus') AS [MergeStatus],
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS [PopulateCompletionAge],
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus') AS [PopulateStatus],
    FULLTEXTCATALOGPROPERTY(cat.name,'UniqueKeyCount') AS [UniqueKeyCount],
    FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus') AS [ImportStatus]
FROM sys.fulltext_catalogs AS cat

ItemCount tells me the number of indexed items including all full-text, keyphrase and document similarity indexes in a catalog.  PopulateStatus tells me if the population is in progress.   Here is the documentation of these properties.


Leave a Reply