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”
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
.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With