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

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 2

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.