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?

Magnus Johansson · Accepted Answer · 2020-04-13T20:54:35.400

19

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

edited Apr 13 '20 at 20:54

answered Jul 07 '09 at 11:36

Magnus Johansson

28,010
19
106
164

So - the best option is to call this in while(status!=0)? – Arnis Lapsa Jul 07 '09 at 11:42
Ehh... will be fine for now. Still better than waiting X seconds and hoping that rebuild will be finished. – Arnis Lapsa Jul 07 '09 at 12:13
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... – Magnus Johansson Jul 07 '09 at 12:55

score 4 · Answer 2 · answered Jun 24 '14 at 21:57

4

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

answered Jun 24 '14 at 21:57

fujiiface

1,262
11
15

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. – OutstandingBill Oct 16 '15 at 00:31

score 3 · Answer 3 · answered Mar 31 '19 at 06:14

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

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

3 Answers3

Linked