I have a strange problem with SQL Server 2005 and Full text indexing. My database schema has couple of indexes included in the full text catalog. There aren't too many records in these index (total under 100,000)
After running the SQL Server instance for some time, say few days to a couple of weeks, all commands that insert or update data in the table with fulltext index starts timing out. On checking lock information, the transaction is waiting for a processid 22 to release a lock. The processid 22 points to a MSSEARCH process. This process seems to be running for really long time.
I tried running ALTER FULLTEXT CATALOG XXX REBUILD from SQL Management Studio. This command takes forever and never finishes. The index table is defined with background update of tracked changes.
After I restart SQL Server 2005 instance and then immediately run ALTER FULLTEXT CATALOG XXX REBUILD, it completes with in few seconds. But fails to complete again after few days.
Has anyone experienced this problem before? Any resolution. It is not possible to restart SQL Server in 24x7 production environment.