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
,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
About these ads
This entry was posted in Database options, Disaster Recovery, SQL Server and tagged , . Bookmark the permalink.

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

  1. dazpoc says:

    Definitely comes in handy…

  2. Peter says:

    help me to fix the issue with SQL2005 backups. Thank you very much !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s