SQL Server Full Text Search: Hung Processes with the MSSEARCH Wait Type

Question

We have a SQL Server 2005 Service Pack 2 (SP2) machine that runs a large number of databases, all of which contain full-text catalogs. Whenever we try to delete one of these databases or rebuild the full-text index, the drop or rebuild process hangs endlessly with the MSSEARCH wait type. The process cannot be killed, and a server restart is required to restart. Based on a post from the Microsoft 1 forum, it looks like the problem might be the incorrectly deleted full-text directory. Can anyone recommend a way to determine which directory is causing the problem without having to remove them?

1 [ http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2681739&SiteID=1] "Yes, we had full text directories in the database, but since I turned off full-text database searches and disabled msftesql, I I didn’t suspect them. However, I received an article from Microsoft support showing how I can check it, so I found that there was still an old directory that I could delete after and after re-enabling full-text search, because then my backup worked "

Answer 1

Here is a suggestion. I have no corrupted databases, but you can try the following:

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 this does not work, remove the sys.databases filter sys.databases .

Answer 2

Have you tried to start the process monitor and when it freezes and see what is the main error? Using a process monitor, you should be able to tell the whick file / resource in which it expects / errors.

Answer 3

I had a similar problem with invalid full directory directories. The server will not load all databases online at startup. It processed the databases in dbid order and would get half way and stop. Only the older databases were connected to the network, and the rest were not available. Looking at sysprocesses, I found ten or more processes with waittype = 0x00CC, lastwaittype = MSSEARCH. MSSEARCH cannot be stopped. The problem was caused by the fact that we moved the full text directories, but indicated 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 the databases to go online, find the damaged database, disconnect it, fix the file path using the alter database command and start the database online. Then run MSSEARCH and set it to start automatically.