sys.dm_fts_active_catalogs

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

Column name Data type Description

database_id

int

ID of the database that contains the active full-text catalog.

catalog_id

int

ID of the active full-text catalog.

memory_address

varbinary(8)

Address of memory buffers allocated for the population activity related to this full-text catalog.

name

nvarchar(128)

Name of the active full-text catalog.

is_paused

bit

Indicates whether the population of the active full-text catalog has been paused.

status

int

Current 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_description

nvarchar(120)

Description of current state of the active full-text catalog.

previous_status

int

Previous 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_description

nvarchar(120)

Description of previous state of the active full-text catalog.

worker_count

int

Number of threads currently working on this full-text catalog.

active_fts_index_count

int

Number of full-text indexes being populated.

auto_population_count

int

Number of tables with auto population in progress for this full-text catalog. nullable.

manual_population_count

int

Number of tables with manual population in progress for this full-text catalog.

full_incremental_population_count

int

Number of tables with full or incremental population in progress for this full-text catalog.

row_count_in_thousands

int

Estimated number of rows (in thousands) in all full-text indexes in this full-text catalog.

Permissions

Requires VIEW SERVER STATE permission on the server.

Physical Joins

Significant joins of this dynamic management view

Relationship Cardinalities

From To Relationship

dm_fts_active_catalogs.database_id

dm_fts_index_population.database_id

One-to-one

dm_fts_active_catalogs.catalog_id

dm_fts_index_population.catalog_id

One-to-one

Examples

Identifying Full-Text Catalogs for the Current Database

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

SELECT catalog.name, catalog.is_paused, catalog.status_description, catalog.row_count_in_thousands, 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

See Also

Reference

Dynamic Management Views and Functions
Full-Text Search Related Dynamic Management Views

Help and Information

Getting SQL Server 2005 Assistance