SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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 ;) .

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.


Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...