Transact-SQL (T-SQL) query to return the status and detail information for all SQL Server Full-Text Catalogs on an SQL Server instance

We have about 200 user databases in which we have the full-text search enabled and these databases contain several tables. As part of my daily checks, I have to check the status of all full-text catalogs, to ensure that all full-text catalogs are successfully populated without errors. However, to check the status of every full-text catalog using SQL Server Management Studio is a challenge and very time consuming task.  So, to make my life easier, I wrote the following T-SQL script that quickly returns the status of all full-text catalogs and their population status.

SELECT DB_NAME(ftsac.[database_id]) AS [db_name]
	,DATABASEPROPERTYEX(DB_NAME(ftsac.[database_id]), 'IsFulltextEnabled') AS [is_ft_enabled]
	,ftsac.[name] AS [catalog_name]
	,mfs.[name] AS [ft_catalog_file_logical_name]
	,mfs.[physical_name] AS [ft_catalog_file_physical_name]
	,OBJECT_NAME(ftsip.[table_id]) AS [table_name]
	,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'IndexSize') AS [ft_catalog_logical_index_size_in_mb]
	,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'AccentSensitivity') AS [is_accent_sensitive]
	,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'UniqueKeyCount') AS [unique_key_count]
	,ftsac.[row_count_in_thousands]
	,ftsip.[is_clustered_index_scan]
	,ftsip.[range_count]
	,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'ImportStatus') AS [import_status]
	,ftsac.[status_description] AS [current_state_of_fts_catalog]
	,ftsac.[is_paused]
	,(
		SELECT CASE FULLTEXTCATALOGPROPERTY(ftsac.[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 Full. Paused'
				WHEN 9
					THEN 'Change Tracking'
				END
		) AS [population_status]
	,ftsip.[population_type_description] AS [ft_catalog_population_type]
	,ftsip.[status_description] AS [status_of_population]
	,ftsip.[completion_type_description]
	,ftsip.[queued_population_type_description]
	,ftsip.[start_time]
	,DATEADD(ss, FULLTEXTCATALOGPROPERTY(ftsac.[name], 'PopulateCompletionAge'), '1/1/1990') AS [last_populated]
FROM [sys].[dm_fts_active_catalogs] ftsac
INNER JOIN [sys].[databases] dbs
	ON dbs.[database_id] = ftsac.[database_id]
LEFT JOIN [sys].[master_files] mfs
	ON mfs.[database_id] = dbs.[database_id]
		AND mfs.[physical_name] NOT LIKE '%.mdf'
		AND mfs.[physical_name] NOT LIKE '%.ndf'
		AND mfs.[physical_name] NOT LIKE '%.ldf'
CROSS JOIN [sys].[dm_fts_index_population] ftsip
WHERE ftsac.[database_id] = ftsip.[database_id]
	AND ftsac.[catalog_id] = ftsip.[catalog_id];

Here is the description of the columns of this script result set:

  • db_name – Name of the SQL Server database, unique within an instance of SQL Server
  • is_ft_enabled – The value of 1 indicates that the full-text and semantic indexing is enabled
  • ft_catalog_file_logical_name – Returns the logical file name of the full-text index catalog file
  • ft_catalog_file_physical_name – Returns the phyisical file name of the full-text index catalog file
  • table_name – Returns the name of the table where full-text index exists
  • ft_catalog_logical_index_size_in_mb – Returns the logical size of the full-text catalog in megabytes(MB)
  • is_accent_sensitive – Returns the accent-sensitivity setting for full-text catalog. The value of 1 indicates that full-text catalog is accent sensitive
  • unique_key_count – Returns the number of unique keys in the full-text catalog
  • row_count_in_thousands – Returns the estimated number of rows (in thousands) in all full-text indexes in this full-text catalog
  • is_clustered_index_scan – Indicates whether the population involves a scan on the clustered index
  • range_count – Returns the number of sub-ranges into which this population has been parallelized
  • import_status – Indicates whether the full-text catalog is being imported. The value of 1 indicates that the full-text catalog is being imported
  • current_state_of_fts_catalog – Returns the state of the full-text catalog
  • is_paused – Indicates whether the population of the active full-text catalog has been paused
  • population_status – Returns the status of current population
  • ft_catalog_population_type – Returns the type of full-text catalog population type
  • status_of_population – Returns the status of this population
  • completion_type_description – Returns the description of status of the population
  • queued_population_type_description – Returns description of the population to follow, if any. For example, when CHANGE TRACKING = AUTO and the initial full population is in progress, this column would show “Auto population.”
  • start_time – Returns the time that the population started.
  • last_populated – Returns the time when the last full-text index population completed

I wrote this script using function FULLTEXTCATALOGPROPERTY and following system views and dmvs:

Hope you will find this post useful 😉 .

Advertisements

Understanding SQL Server Full-text Indexing – (Part 2)

Checkout the final part of this two part article series here, in which I discussed how to create full-text index using Transact-SQL, and how to manage and optimize full-text search catalogs and indexes.

This article is published on SSWUG.org.

Understanding SQL Server Full-text Indexing – (Part 1)

SQL Server allows applications and users to execute full- text search queries against character based data in SQL Server tables. Full-text search is applicable in wide-range of business scenarios. You use full-text search to create queries that search for a word or phrase in a column. You can use full-text search to search a one or more columns for a word or phrase, or words in close proximity to each other, or multiple inflections and tenses of a word (for example: work, works, working).

Full-text search supports a user-friendly interface for data search. Full-text search stores information about words and the row in which the word is found in a full-text search index. A full-text index is a special type of token-based functional index that is built and maintained by the Full-Text Engine for SQL Server. The process of creating full-text index differs from other types of indexes. Instead of the construction of the B-tree structure based on a value stored in a particular row, a full-text index builds an inverted, staked, compressed index structure based on individual tokens from the text being indexed. You can group multiple full-indexes in a single full-text search catalog.

Checkout the part-1 of this two part article series here, in which I discussed about full-text indexing feature, how to configure an SQL Server instance to support full-text search, and how to create full-text index using SQL Server Management Studio.

This article is published on SSWUG.org.

Guide to SQL Server Table Indexes (Part 3)

Checkout the part-3 of my five part article series on Guide to SQL Server Table Indexes here, in which you will learn about remaining index types available in SQL Server, and what are these indexes advantages and dis-advantages.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Guide to SQL Server Table Indexes (Part 2)

Checkout the part-2 of my five part article series on Guide to SQL Server Table Indexes here, in which you will learn about the different types of indexes available in SQL Server, and what are some of these indexes advantages and dis-advantages.

This article is published on SQL-SERVER-PERFORMANCE.COM.