How to define if rebuilding of the full text index has finished?

Question

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 ACCENT_SENSITIVITY = OFF  

doesn't work or I'm doing something slightly wrong. :/

Any ideas?

Pythagoreanism

Answer 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

Noma

So - the best option is to call this in while(status!=0)? – Pythagoreanism

Ehh... will be fine for now. Still better than waiting X seconds and hoping that rebuild will be finished. – Pythagoreanism

Yes, it would be nice if some event could be raised that you could subscribe on, but I don't think there is one to the best of my knowledge... – Noma

Answer 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

Congregate

This list is perhaps more correct than the one that only goes up to 5. Using SQL2012 I saw a status of 9 immediately after repopulating my Full Text Indexed table. – Vitia

Answer 3

SELECT name, case FULLTEXTCATALOGPROPERTY(name, '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 is full.  Paused.'
    when 9 then ' Change tracking' end AS Status
from sys.fulltext_catalogs

Playsuit