Full-Text Search
Overview
Full-text search fails quietly. A catalog that stopped populating, an index left on CHANGE_TRACKING OFF, or an index worn into hundreds of fragments is usually found when users complain that search results are stale or slow.
The Full-Text Search report reads the full-text setup of one database and shows:
- status cards for the full-text service, the catalogs, the indexes, fragments, populations and failed rows, each red, yellow or green,
- the reasons under the cards, worst first, with what to do next,
- a bar per full-text index for its fragment count, with the REORGANIZE threshold marked,
- one grid with a toolbar switch between Catalogs and Indexes,
- right-click scripts for REORGANIZE, the populations and CHANGE_TRACKING AUTO. They are shown, never run.
A database with no full-text catalog opens on one Not in use state, with nothing else on the page.
Where to find it
| Route | How |
|---|---|
| Database tree | Expand a database → Real Time → Full-Text Search |
The page title reads Full-Text Search for <database name>. The item is not shown for master, model and tempdb, where SQL Server does not allow a full-text catalog, or on SQL Server 2005.
The toolbar
| Control | What it does |
|---|---|
| Refresh | Reads the database again |
| Catalogs / Indexes | Which grid to show (Indexes by default). The choice is remembered. Switching does not read the database again |
| Crawl Log Folder | Opens the folder that holds ERRORLOG and the SQLFT crawl logs, when it can be reached from this computer. Otherwise a message gives the folder on the server and the crawl log name for each catalog |
| Cancel | Stops a read in progress |
The Catalogs and Indexes switch and the Crawl Log Folder button are only shown when there is something to list.
Reading the page
The heading reads Full-text search in <database>: followed by the overall state: healthy, needs a look, needs attention now or partly unreadable. The line under it gives when the page was read and the SQL Server version.
The cards:
| Card | What it shows | Color |
|---|---|---|
| Full-Text Service | The Full-text Filter Daemon Launcher state and startup type | Red when full-text is not installed but the database holds catalogs, or when the service is not Running. Blue (Installed) when no launcher service is listed, for example on Linux |
| Catalogs | The catalog count, with the total items and MB | The worst catalog status |
| Full-Text Indexes | The index count, with how many are disabled, on tracking OFF or on tracking MANUAL | Yellow for any disabled or OFF index, blue for MANUAL, green when all are AUTO |
| Fragments | The most fragments on one index | Yellow when any index has more than 30 |
| Populations | Idle, or how many are running, and when the last one finished | Yellow when a population has been running over 24 hours |
| Failed Rows | Rows the crawl could not index, and in how many tables | Yellow when any row failed |
Hover a card for its tip: the service name, or what the server said when a read was refused.
The findings under the cards give each red and yellow reason in plain words, then what could not be read, then notes. The last note names the crawl log for the first catalog.
The chart draws one bar per full-text index for its fragment count, most first, up to 12 indexes (the rest are counted under the chart). Bars over the threshold are amber, and a dashed line marks 30. Hover a bar for the catalog, the fragment count and size, the largest fragment and the change tracking mode.
Catalog and index status
A catalog is:
| Status | When |
|---|---|
| Red | Full-text is not installed, or the populate status is Disk full, paused or Shutdown |
| Yellow | The populate status is Paused or Throttled, or the catalog is paused in sys.dm_fts_active_catalogs |
| Unknown | The catalog properties returned NULL |
| Otherwise | The worst status of its indexes |
An index is:
| Status | When |
|---|---|
| Red | Full-text is not installed on the instance |
| Yellow | The index is disabled, its change tracking is OFF, it has more than 30 fragments, a population on it has been running over 24 hours, or rows failed |
| Info | Change tracking is MANUAL |
| OK | None of the above |
The Status column is colored red, orange or gray to match. For an index it lists every problem, separated by semicolons: Disabled, Change tracking OFF, never populated (or manual population only), Population running <time>, <n> fragments, <n> rows failed. Without a problem it reads Populating (<type>), Change tracking MANUAL, <n> pending or OK.
The standing AUTO population that an index on CHANGE_TRACKING AUTO keeps open is not counted as a running population.
Reading the grid
Catalogs view:
| Column | What it is |
|---|---|
| Catalog | The catalog name |
| Status | The populate status in words (Idle, Full population in progress, Paused, Throttled, Recovering, Shutdown, Incremental population in progress, Building index, Disk full, paused, Change tracking), plus paused, master merge in progress or importing |
| Items | FULLTEXTCATALOGPROPERTY ItemCount |
| Size MB | FULLTEXTCATALOGPROPERTY IndexSize, the figure SSMS shows on the catalog properties page |
| Last Completed Crawl | When the last population of the catalog finished |
| Indexes | Full-text indexes in the catalog |
| Fragments | Fragments over all its indexes |
| Merge in Progress | Whether a master merge is running |
| Default | Whether it is the default catalog |
| Accent Sensitive | Whether accent sensitivity is on |
| Fragment Size | Total size of its fragments |
| Crawl Log | The catalog’s crawl log file name |
Indexes view (one row per table):
| Column | What it is |
|---|---|
| Table | Schema and table |
| Catalog | The catalog it is in |
| Status | See above |
| Change Tracking | AUTO, MANUAL or OFF |
| Fragments | Fragments not marked for deletion |
| Last Crawl End | When the last crawl finished |
| Failed Rows | OBJECTPROPERTYEX TableFulltextFailCount |
| Crawl Type | FULL_CRAWL, INCREMENTAL_CRAWL, UPDATE_CRAWL or PAUSED_FULL_CRAWL |
| Last Crawl Start | When the last crawl started |
| Crawl Completed | Whether it finished |
| Items | OBJECTPROPERTYEX TableFulltextItemCount |
| Table Rows | Rows in the table |
| Pending Changes | OBJECTPROPERTYEX TableFulltextPendingChanges |
| Fragment Size | Total size of the index’s fragments |
| Key Index | The unique index the full-text index is keyed on |
| Columns | Full-text indexed columns |
| Stoplist | The stoplist, (system) or (off) |
| Enabled | Whether the index is enabled |
Hover an index row for its status, the population running on it (type, status, start time and ranges done) and any outstanding batches, retries and failed documents. Both grids export to CSV and Excel like every other grid.
Right-click actions
Nothing on this page runs anything against the database. Each script opens in a window to copy.
| Item | When | What it shows |
|---|---|---|
| Script Start Incremental Population | Index row | ALTER FULLTEXT INDEX ... START INCREMENTAL POPULATION. The script notes when the table has no rowversion column, so SQL Server runs a full population instead |
| Script Start Full Population | Index row | ALTER FULLTEXT INDEX ... START FULL POPULATION |
| Script Change Tracking AUTO | Index not on AUTO | ALTER FULLTEXT INDEX ... SET CHANGE_TRACKING = AUTO. Switching from OFF starts a full population |
| Script Enable Index | Disabled index | ALTER FULLTEXT INDEX ... ENABLE |
| Script Reorganize Catalog <name> | Catalog row, or the catalog of an index row | ALTER FULLTEXT CATALOG ... REORGANIZE, which merges the fragments (a master merge) |
| Open the Crawl Log Folder | The error log folder is known | Same as the toolbar button |
| Copy the findings as text | Always | The heading, every card and every finding |
| Copy Chart to Clipboard | Always | The cards, findings and fragment chart as a picture |
On the Indexes view the menu also offers the table pages for the row, such as Table Size, Table Space Breakdown and Table Use.
Crawl logs
Crawl errors are written to files next to ERRORLOG on the server, named SQLFT, the database id and the catalog id (five digits each), then .LOG. For example SQLFT0000500007.LOG is catalog 7 in database 5. They cannot be read through T-SQL, so the page names them and the Crawl Log Folder button opens the folder when it can.
Permissions and versions
- The catalogs and indexes come from
sys.fulltext_catalogs,sys.fulltext_indexes,sys.fulltext_index_fragments,FULLTEXTCATALOGPROPERTYandOBJECTPROPERTYEX. - Without VIEW DEFINITION on the database (db_owner and sysadmin have it), SQL Server only lists the catalogs the login owns and the indexes on tables it has been granted. The page says so, and when nothing is visible it says the catalogs are not visible to this login instead of Not in use.
sys.dm_fts_index_population,sys.dm_fts_outstanding_batches,sys.dm_fts_active_catalogsandsys.dm_server_servicesneed VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later). Without it the live population detail and the service state are missing; crawl start and end times still show.- Each part that is refused is named on the page; the rest still shows.
- Needs SQL Server 2008 or later. When full-text is not installed on the instance but the database holds catalogs, the page is red: CONTAINS and FREETEXT queries fail with Msg 7609 until the Full-Text and Semantic Extractions for Search feature is added in SQL Server Setup.