Returns information about full-text catalog properties in SQL Server 2017.
FULLTEXTCATALOGPROPERTY ('catalog_name' ,'property')
The following properties will be removed in a future release of SQL Server: LogSize and PopulateStatus. Avoid using these properties in new development work, and plan to modify applications that currently use any of them.
Is an expression containing the name of the full-text catalog.
Is an expression containing the name of the full-text catalog property. The table lists the properties and provides descriptions of the information returned.
0 = Accent insensitive
1 = Accent sensitive
|IndexSize||Logical size of the full-text catalog in megabytes (MB). Includes the size of semantic key phrase and document similarity indexes.
For more information, see "Remarks," later in this topic.
|ItemCount||Number of indexed items including all full-text, keyphrase, and document similarity indexes in a catalog|
|LogSize||Supported for backward compatibility only. Always returns 0.
Size, in bytes, of the combined set of error logs associated with a Microsoft Search Service full-text catalog.
|MergeStatus||Indicates whether a master merge is in progress.
0 = master merge isn't in progress
1 = master merge is in progress
|PopulateCompletionAge||The difference in seconds between the completion of the last full-text index population and 01/01/1990 00:00:00.
Only updated for full and incremental crawls. Returns 0 if no population has occurred.
|PopulateStatus||0 = Idle
1 = Full population in progress
2 = Paused
3 = Throttled
4 = Recovering
5 = Shutdown
6 = Incremental population in progress
7 = Building index
8 = Disk is full. Paused.
9 = Change tracking
|UniqueKeyCount||Number of unique keys in the full-text catalog.|
|ImportStatus||Indicates whether the full-text catalog is being imported.
0 = The full-text catalog isn't being imported.
1 = The full-text catalog is being imported.
Returns NULL on error or if a caller doesn't have permission to view the object.
In SQL Server 2017, a user can only view the metadata of securables. These securables are ones that the user owns or on which the user has been granted permission. As such, built-in functions that emit metadata, such as FULLTEXTCATALOGPROPERTY, may return NULL if the user doesn't have any permission on the object. For more information, see sp_help_fulltext_catalogs (Transact-SQL).
FULLTEXTCATALOGPROPERTY ('catalog_name','IndexSize') looks at only fragments with status 4 or 6 as shown in sys.fulltext_index_fragments. These fragments are part of the logical index. As such, the IndexSize property returns only the logical index size.
During an index merge, however, the actual index size might be double its logical size. To find the actual size that is being consumed by a full-text index during a merge, use the sp_spaceused system stored procedure. That procedure looks at all fragments associated with a full-text index.
The full-text population may fail. It may fail if you restrict the growth of the full-text catalog file and don't allow enough space for the merge process. In this case, FULLTEXTCATALOGPROPERTY ('catalog_name','IndexSize') returns 0 and the following error is written to the full-text log:
Error: 30059, Severity: 16, State: 1. A fatal error occurred during a full-text population and caused the population to be cancelled. Population type is: FULL; database name is FTS_Test (id: 13); catalog name is t1_cat (id: 5); table name t1 (id: 2105058535). Fix the errors that are logged in the full-text crawl log. Then, resume the population. The basic Transact-SQL syntax for this is: ALTER FULLTEXT INDEX ON table_name RESUME POPULATION.
It's important that applications don't wait in a tight loop, checking for the PopulateStatus property to become idle. Becoming idle indicates that population has completed. This check takes CPU cycles away from the database and full-text search processes, and causes timeouts. It's usually a better option to check the corresponding PopulateStatus property at the table level, TableFullTextPopulateStatus in the OBJECTPROPERTYEX system function. This and other new full-text properties in OBJECTPROPERTYEX provide more granular information about full-text indexing tables. For more information, see OBJECTPROPERTYEX (Transact-SQL).
The following example returns the number of full-text indexed items in a full-text catalog named
USE AdventureWorks2012; GO SELECT fulltextcatalogproperty('Cat_Desc', 'ItemCount'); GO