How To Define If Rebuilding Of The Full Text Index Has Finished?

Got a requirement to rebuild mssql full-text index. Problem is - I need to know exactly when job is done. Therefore - just calling: ALTER FULLTEXT CATALOG fooCatalog REBUILD WITH

Solution 1:

You can determine the status of the fulltext indexing by querying the indexing properties like this:

SELECT FULLTEXTCATALOGPROPERTY('IndexingCatalog', 'PopulateStatus') AS Status

Populate Status: 0 = Idle 1 = Full population in progress 2 = Paused 3 = Throttled 4 = Recovering 5 = Shutdown 6 = Incremental population in progress 7 = Building index 8 = Disk is full. Paused. 9 = Change tracking

But also pay attention to this note in the article:

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.

EDIT: Corrected link to a newer page and added quote from the note

Solution 2:

Since I cannot comment on Magnus' answer yet (lack of reputation), I will add it here. I found that there is a conflict of information on MSDN according to this MSDN link. According to the link I am referencing, the PopulateStatus has 10 possible values listed below:

0 = Idle.

1 = Full population in progress

2 = Paused

3 = Throttled

4 = Recovering

5 = Shutdown

6 = Incremental population in progress

7 = Building index

8 = Disk is full.  Paused.

9 = Change tracking

Solution 3:

SELECT name, case FULLTEXTCATALOGPROPERTY(name, 'PopulateStatus') when0then'Idle'when1then' Full population in progress'when2then' Paused'when3then' Throttled'when4then' Recovering'when5then' Shutdown'when6then' Incremental population in progress'when7then' Building index'when8then' Disk is full.  Paused.'when9then' Change tracking' end AS Statusfrom sys.fulltext_catalogs