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

SQL Server 2005 backups in a hung state because of full text search process

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
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


Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.


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

Loading comments...