Export (0) Print
Expand All

sys.dm_fts_active_catalogs (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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

Note Note

The following columns will be removed in a future version of Microsoft SQL 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 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 that are being populated.

auto_population_count

int

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

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 a 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.

is_importing

bit

Indicates 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.

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

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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft