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]
	,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'ImportStatus') AS [import_status]
	,ftsac.[status_description] AS [current_state_of_fts_catalog]
				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'
		) AS [population_status]
	,ftsip.[population_type_description] AS [ft_catalog_population_type]
	,ftsip.[status_description] AS [status_of_population]
	,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 😉 .


Six top SQL Server 2012 management tools you should know about

Management tools play a vital role in enterprise database management. This is because the well-integrated tools extend the administrator’s capabilities, whereas a random collection of tools can lead to confusion, operational mistakes, high training costs and poor database administrator (DBA) productivity.

To keep up with these new demands of complex enterprise database management solutions, Microsoft ships SQL Server software with a broad group of several graphical management tools that help DBAs and developers efficiently create, manage and maintain SQL Server solutions, and allow them to quickly resolve complex performance and configuration problems.

Checkout my article here, in which I gave an overview of the key features of these graphical SQL Server 2012 management tools.

This article is published on