I came across an issue today where a backup process was in a blocked state with a wait type of MSSEARCH. The issue boiled down to an issue with communication/contention between the SQL Server engine service and the SQL Server Full Text Search service.

An attempt to kill the backup process resulted in a process stuck in a killed/rollback state. A scan of the error logs also reveals that the full text search service is paused when the backup normally begins and resumed when it completes. Errors in the SQL logs indicated that the FT service had failed to resume correctly and could be indicating a corrupt catalog.

A quick fix was to restart the SQL Full Text Search service. Because of the contention this couldn’t be done with the services console and required the underlying process to be ended with task manager. The process for SQL Server 2005 is msftesql. Once this was stopped and restarted the killed/rollback spid resolved instantly and a backup was able to successfully complete.

Because no post is complete without scripts…

This script will return all full text catalogs ”that have some population activity in progress on the server.”(B.O.L.)

select dbs.name as database_name
,ftc.name as full_text_catalog_name
,ftc.catalog_id
,ftc.is_importing
,ftc.is_paused
,ftc.status_description
from sys.dm_fts_active_catalogs as ftc
inner join sys.databases as dbs
on ftc.database_id = dbs.database_id

Of course because I was already aware of the database – it was the one that wasn’t being backed up – I could also use:

select * from 'database name'.sys.fulltext_catalogs