SQL Server FTI: How to check table status?

Question

In the SQL Server Full-Text Indexing scheme i want to know if a table is in

• start_chage_tracking mode
• update_index mode
• start_change_tracking and start_background_updateindex modes

The problem is that i set my tables to "background update index", and then tell it to "start change tracking", but then some months later it doesn't seem to be tracking changes.

How i can i see the status of "background updateindex" and "change tracking" flags?

example:

sp_fulltext_table @tabname='DiaryEntry', @action='start_background_updateindex' 

Server: Msg 15633, Level 16, State 1, Procedure sp_fulltext_table, Line 364
Full-text auto propagation is currently enabled for table 'DiaryEntry'.

sp_fulltext_table @tabname='Ticket', @action='start_background_updateindex' 
Server: Msg 15633, Level 16, State 1, Procedure sp_fulltext_table, Line 364
Full-text auto propagation is currently enabled for table 'Ticket'.

Obviously a table has an indexing status, i just want to know it show i can display it to the user (i.e. me).

The other available API:

EXECUTE sp_help_fulltext_tables

only returns the tables that are in the catalog, it doesn't return their status.

TABLE_OWNER  TABLE_NAME  FULLTEXT_KEY_INDEX_NAME  FULLTEXT_KEY_COLID  FULLTEXT_INDEX_ACTIVE  FULLTEXT_CATALOG_NAME
===========  ==========  =======================  ==================  =====================   =====================
dbo          DiaryEntry  PK_DiaryEntry_GUID       1                   1                      FrontlineFTCatalog
dbo          Ticket      PK__TICKET_TicketGUID    1                   1                      FrontlineFTCatalog

And i can get the PopulateStatus of an entire catalog:

SELECT FULLTEXTCATALOGPROPERTY('MyCatalog', 'PopulateStatus') AS PopulateStatus

which returns a status for the catalog:

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 not for a table.

---

SQL Server 2000 SP4

SELECT @@version
Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
    Aug  6 2000 00:57:48 
    Copyright (c) 1988-2000 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Regardless of any bug, i want to create UI to easily be able to see its status.

Answer 1

What version of SQL / Service pack are you running; this used to be a bug in sql 2000

http://support.microsoft.com/kb/290212

execute the sp_fulltext_table in this sequence to temporarily fix the issue. (The low disk space is likely the cause)

• stop_change_tracking
• start_change_tracking
• stop_background_updateindex
• start_background_updateindex

OK to monitor the status you need to look at this very handy resource on SQL Server FTI on MSSQL Tips; i think the script there will give you what you are looking for.

Answer 2

Christ. i had a whole nicely formatted answer. i was scrolling to hit save when IE crashed.

Short version:

OBJECTPROPERTY

• TableFullTextPopulateStatus
• TableFullTextBackgroundUpdateIndexOn
• TableFullTextCatalogId
• TableFullTextChangeTrackingOn
• TableFullTextKeyColumn
• TableHasActiveFulltextIndex

TableFullTextBackgroundUpdateIndexOn 1=TRUE 0=FALSE

TableFullTextPopulateStatus 0=No population 1=Full population 2=Incremental population

Full example:

SELECT
    --indicates whether full-text change-tracking is enabled on the table (0, 1)
    OBJECTPROPERTY(OBJECT_ID('DiaryEntry'), 'TableFullTextChangeTrackingOn') AS TableFullTextChangeTrackingOn,

    --indicate the population status of a full-text table (0=No population, 1=Full Population, 2=Incremental Population)
    OBJECTPROPERTY(OBJECT_ID('DiaryEntry'), 'TableFullTextPopulateStatus') AS TableFullTextPopulateStatus,

    --indicates whether a table has full-text background update indexing (0, 1)
    OBJECTPROPERTY(OBJECT_ID('DiaryEntry'), 'TableFullTextBackgroundUpdateIndexOn') AS TableFullTextBackgroundUpdateIndexOn,

    -- provides the full-text catalog ID in which the full-text index data for the table resides (0=table is not indexed)
    OBJECTPROPERTY(OBJECT_ID('DiaryEntry'), 'TableFullTextCatalogId') AS TableFullTextCatalogId,

    --provides the column ID of the full-text unique key column (0=table is not indexed)
    OBJECTPROPERTY(OBJECT_ID('DiaryEntry'), 'TableFullTextKeyColumn') AS TableFullTextKeyColumn,

    --indicates whether a table has an active full-text index (0, 1)
    OBJECTPROPERTY(OBJECT_ID('DiaryEntry'), 'TableHasActiveFulltextIndex') AS TableHasActiveFulltextIndex