Problem

Adams Web text search times out

If an Adams Web text search is timing out, a SQL Server full text index may be corrupt.

Solution

Diagnose

Timing

Change the 2026-04-22 date to today’s date in these SQL statements
Run the SQL statements
Review timings in the Message tab in SQL Server Management Studio (SSMS)

Check Logs

You may be able to find the log path using

SELECT SERVERPROPERTY('ErrorLogFileName') AS error_log_path;

It should be similar to

C:\Program Files\Microsoft SQL Server\MSSQLXX.INSTANCE\MSSQL\LOG\

What to look for in the crawl log

Open the newest SQLFT*.LOG* file and search for:

error
warning
fail
retry
timeout
access denied
filter daemon
fdhost
word breaker
merge
crawl

Common messages include:

filter failures
I/O errors
catalog corruption
memory pressure
document parsing failures
aborted populations
retries
stalled merges

Check for Open Transactions

Open transactions could block the full text index.

DBCC OPENTRAN;

SELECT
    session_id,
    open_transaction_count,
    status,
    program_name,
    login_name
FROM sys.dm_exec_sessions
WHERE open_transaction_count > 0;

Open transactions with session details.

SELECT
    transaction_information.transaction_id,
    transaction_information.name AS transaction_name,
    transaction_information.transaction_begin_time,
    DATEDIFF(MINUTE,
             transaction_information.transaction_begin_time,
             GETDATE()) AS transaction_age_minutes,

    session_information.session_id,
    session_information.login_name,
    session_information.host_name,
    session_information.program_name,
    request_information.status,
    request_information.command,
    request_information.wait_type,
    request_information.blocking_session_id,

    DB_NAME(database_transaction_information.database_id) AS database_name
FROM sys.dm_tran_active_transactions AS transaction_information
JOIN sys.dm_tran_session_transactions AS session_transaction_information
    ON transaction_information.transaction_id =
       session_transaction_information.transaction_id
JOIN sys.dm_exec_sessions AS session_information
    ON session_transaction_information.session_id =
       session_information.session_id
LEFT JOIN sys.dm_exec_requests AS request_information
    ON session_information.session_id =
       request_information.session_id
LEFT JOIN sys.dm_tran_database_transactions AS database_transaction_information
    ON transaction_information.transaction_id =
       database_transaction_information.transaction_id
ORDER BY transaction_information.transaction_begin_time;

Fins sleeping sessions with open transactions.

A session can be:

sleeping
have no active request
still hold an uncommitted transaction

SELECT
    session_information.session_id,
    session_information.login_name,
    session_information.host_name,
    session_information.program_name,
    session_information.status,
    session_information.open_transaction_count,
    session_information.last_request_start_time,
    session_information.last_request_end_time
FROM sys.dm_exec_sessions AS session_information
WHERE session_information.open_transaction_count > 0
ORDER BY session_information.open_transaction_count DESC;

Find the exact SQL text for the transaction

Once you identify a suspicious session_id:

SELECT
    request_information.session_id,
    sql_text.text
FROM sys.dm_exec_requests AS request_information
CROSS APPLY sys.dm_exec_sql_text(request_information.sql_handle) AS sql_text
WHERE request_information.session_id = 57;

If the session is sleeping and not currently executing:

SELECT
    connection_information.session_id,
    sql_text.text
FROM sys.dm_exec_connections AS connection_information
CROSS APPLY sys.dm_exec_sql_text(connection_information.most_recent_sql_handle) AS sql_text
WHERE connection_information.session_id = <SESSION_ID>;

Full Text Index Population Not Progressing

A full text index could be stuck (ex: due to open transactions blocking it). A lack of progress/change here could indicate a block. However, at least open DB showed AUTO, Starting, 0, 0 for all tables.

SELECT
    OBJECT_NAME(table_id) AS table_name,
    population_type_description,
    status_description,
    start_time,
    completed_range_count,
    outstanding_batch_count
FROM sys.dm_fts_index_population;

Rebuild the Full Text Index

For the tables that have greater than 10 millisecond times, use these SQL statements to rebuild the SQL Server full text index.

In each statement, replace <tablename> with the actual database table name (e.g. location).

Turn off automatic change tracking

ALTER FULLTEXT INDEX ON dbo.<tablename> SET CHANGE_TRACKING OFF;

Stop the active population (if any)

ALTER FULLTEXT INDEX ON dbo.<tablename> STOP POPULATION;

Start a full population manually

ALTER FULLTEXT INDEX ON dbo.<tablename> START FULL POPULATION;

To determine if the population is complete, verify the table does not show in this result set

SELECT
    db_name(database_id) AS DatabaseName,
    object_name(table_id) AS TableName,
    population_type_description AS PopulationType,
    is_clustered_index_scan AS IsScanning,
    range_count,
    completed_range_count,
    start_time
FROM sys.dm_fts_index_population
WHERE object_name(table_id) = '<tablename>'

5. If the table is not present in the previous result set, then restart automatic rebuilds

ALTER FULLTEXT INDEX ON dbo.<tablename> SET CHANGE_TRACKING AUTO;

Adams Web Text Search Timing Out