sys.dm_fts_active_catalogs (Transact-SQL)

sys.dm_fts_active_catalogs (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns information on the full-text catalogs that have some population activity in progress on the server.

System_CAPS_ICON_note.jpg Note


The following columns will be removed in a future version of MicrosoftSQL Server: is_paused, previous_status, previous_status_description, row_count_in_thousands, status, status_description, and worker_count. Avoid using these columns in new development work, and plan to modify applications that currently use any of them.

Applies to: SQL Server (SQL Server 2008 through current version).
Column nameData typeDescription
database_idintID of the database that contains the active full-text catalog.
catalog_idintID of the active full-text catalog.
memory_addressvarbinary(8)Address of memory buffers allocated for the population activity related to this full-text catalog.
namenvarchar(128)Name of the active full-text catalog.
is_pausedbitIndicates whether the population of the active full-text catalog has been paused.
statusintCurrent state of the full-text catalog. One of the following:

0 = Initializing

1 = Ready

2 = Paused

3 = Temporary error

4 = Remount needed

5 = Shutdown

6 = Quiesced for backup

7 = Backup is done through catalog

8 = Catalog is corrupt
status_descriptionnvarchar(120)Description of current state of the active full-text catalog.
previous_statusintPrevious state of the full-text catalog. One of the following:

0 = Initializing

1 = Ready

2 = Paused

3 = Temporary error

4 = Remount needed

5 = Shutdown

6 = Quiesced for backup

7 = Backup is done through catalog

8 = Catalog is corrupt
previous_status_descriptionnvarchar(120)Description of previous state of the active full-text catalog.
worker_countintNumber of threads currently working on this full-text catalog.
active_fts_index_countintNumber of full-text indexes that are being populated.
auto_population_countintNumber of tables with an auto population in progress for this full-text catalog.
manual_population_countintNumber of tables with manual population in progress for this full-text catalog.
full_incremental_population_countintNumber of tables with a full or incremental population in progress for this full-text catalog.
row_count_in_thousandsintEstimated number of rows (in thousands) in all full-text indexes in this full-text catalog.
is_importingbitIndicates whether the full-text catalog is being imported:

1 = The catalog is being imported.

2 = The catalog is not being imported.

The is_importing column was new in SQL Server 2008.

Requires VIEW SERVER STATE permission on the server.

Significant joins of this dynamic management view

FromToRelationship
dm_fts_active_catalogs.database_iddm_fts_index_population.database_idOne-to-one
dm_fts_active_catalogs.catalog_iddm_fts_index_population.catalog_idOne-to-one

The following example returns information about the active full-text catalogs on the current database.

SELECT catalog.name, catalog.is_importing, catalog.auto_population_count, OBJECT_NAME(population.table_id) AS table_name, population.population_type_description, population.is_clustered_index_scan, population.status_description, population.completion_type_description, population.queued_population_type_description, population.start_time, population.range_count   
FROM sys.dm_fts_active_catalogs catalog   
CROSS JOIN sys.dm_fts_index_population population   
WHERE catalog.database_id = population.database_id   
AND catalog.catalog_id = population.catalog_id   
AND catalog.database_id = (SELECT dbid FROM sys.sysdatabases WHERE name = DB_NAME());  
GO  

Dynamic Management Views and Functions (Transact-SQL)
Full-Text Search and Semantic Search Dynamic Management Views and Functions (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft