SQL Server Full-Text Search: Hung processes with MSSEARCH wait type

Question

We have a SQL Server 2005 SP2 machine running a large number of databases, all of which contain full-text catalogs. Whenever we try to drop one of these databases or rebuild a full-text index, the drop or rebuild process hangs indefinitely with a MSSEARCH wait type. The process can’t be killed, and a server reboot is required to get things running again. Based on a Microsoft forums post[1], it appears that the problem might be an improperly removed full-text catalog. Can anyone recommend a way to determine which catalog is causing the problem, without having to remove all of them?

[1] [http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2681739&SiteID=1] “Yes we did have full text catalogues in the database, but since I had disabled full text search for the database, and disabled msftesql, I didn't suspect them. I got however an article from Microsoft support, showing me how I could test for catalogues not properly removed. So I discovered that there still existed an old catalogue, which I ,after and only after re-enabling full text search, were able to delete, since then my backup has worked”

Answer 1

Have you tried running process monitor and when it hangs and see what the underlying error is? Using process moniter you should be able to tell whick file/resource it waiting for/erroring on.

Answer 2

Here's a suggestion. I don't have any corrupted databases but you can try this:

declare @t table (name nvarchar(128))
insert into @t select name from sys.databases --where is_fulltext_enabled 

while exists(SELECT * FROM @t)
begin
    declare @name nvarchar(128)
    select @name = name from @t
    declare @SQL nvarchar(4000)
    set @SQL = 'IF EXISTS(SELECT * FROM '+@name+'.sys.fulltext_catalogs) AND NOT EXISTS(SELECT * FROM sys.databases where is_fulltext_enabled=1 AND name='''+@name+''') PRINT ''' +@Name + ' Could be the culprit'''
    print @sql
    exec sp_sqlexec @SQL
    delete from @t where name = @name
end

If it doesn't work, remove the filter checking sys.databases.

Answer 3

I had a similar problem with invalid full text catalog locations. The server wouldn't bring all databases online at start-up. It would process databases in dbid order and get half way through and stop. Only the older DBs were brought online and the remainder were inaccessible. Looking at sysprocesses revealed a dozen or more processes with a waittype = 0x00CC , lastwaittype = MSSEARCH. MSSEARCH could not be stopped. The problem was caused when we relocated the full text catalogs but entered the wrong path for one of them when running the alter database ... modifyfile command. The solution was to disable MSSEARCH, reboot the server allowing all DBs to come online, find the offending database, take it offline, correct the file path using the alter database command, and bring the DB online. Then start MSSEARCH and set to automatic start-up.