Search in Team System 2008 Web Access doesn't work
I found this interesting stuff in one of my cases where customer was trying to to search for Work Items from within Team System Web Access and was getting the following error:
"There is a problem on the server. Contact your Team Foundation Server administrator".
And in the event log you see one or more of the following errors:
Error#1
Source: TFS WorkItem Tracking, ID 9000
Detailed Message: Full-text catalog 'TeamFoundationServer10FullTextCatalog' is in an unusable state. Drop and re-create this full-text catalog.
Error#2
Source: MSSQLSERVER, ID 7683
Errors were encountered during full-text index population for table or indexed view '[TfsWorkItemTracking].[dbo].[WorkItemLongTexts]', database 'TfsWorkItemTracking' (table or indexed view ID '2066106401', database ID '14'). Please see full-text crawl logs for details.
In the SQL server crawl logs the following error is repeated a number of times:
The component 'MSFTE.DLL' reported error while indexing. Component path 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\MSFTE.DLL'.
Error '0x80070003' occurred during full-text index population for table or indexed view '[TfsWorkItemTracking].[dbo].[WorkItemLongTexts]' (table or indexed view ID '2066106401', database ID '14'), full-text key value 0x00000000000088EB. Attempt will be made to reindex it.
The component 'MSFTE.DLL' reported error while indexing. Component path 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\MSFTE.DLL'.
Informational: Full-text retry pass of Incremental population completed for table or indexed view '[TfsWorkItemTracking].[dbo].[WorkItemLongTexts]' (table or indexed view ID '2066106401', database ID '14'). Number of retry documents processed: 2. Number of documents failed: 2.
Changing the status to MERGE for full-text catalog "TeamFoundationServer10FullTextCatalog" (14) in database "TfsWorkItemTracking" (14). This is an informational message only. No user action is required
After extensive troubleshooting we found that this can occur when the TeamFoundationServer10FullTextCatalog in the TfsWorkitemTracking database becomes corrupted.
Hence, we concluded the set of steps as solution for resolving this issue in the follow order. If the first resolution does not resolve the issue, please move on to the next resolution.
RESOLUTION 1:
Launch SQL Server Management Studio with an account that has administrative privileges and in the Object Explorer and point to Databases, TfsWorkItemTracking, Storage, Full Text Catalogs and right click on TeamFoundationServer10FullTextCatalog and chose Rebuild.
RESOLUTION 2:
Try dropping and rebuilding the catalog using the script given below.
This script will rebuild the full text index in the TfsWorkItemTracking database for Team Foundation Server. Without this full text index, you will not be able to run queries on the History or Description fields of work items.
use TFSWorkitemtracking
GO
declare @dbname as sysname
declare @tblName as sysname
set @dbname = 'TFSWorkitemtracking'
set @tblName = 'WorkItemLongTexts'
declare @LCID as int
set @LCID = convert(int, (SELECT COLLATIONPROPERTY(
convert(nvarchar,DATABASEPROPERTYEX(@dbname,'collation')), 'LCID')))
select @lcid
-- if exists drop the index
if OBJECTPROPERTY(OBJECT_ID(@tblName),N'TableFulltextCatalogId') <> 0
begin
exec(N'DROP FULLTEXT INDEX ON ' + @tblName)
end
-- Default to Neutral if Language resource not available for Full Text
if not exists(select * from sys.fulltext_languages where lcid = @LCID)
begin
set @LCID = 0
end
declare @lcidChar nvarchar(128)
set @lcidChar = cast(@LCID as nvarchar)
-- create the index
exec (N'CREATE FULLTEXT INDEX ON ' + @tblName + '(
Words --full-text index column name
LANGUAGE '+ @lcidChar + ' --LCID for the language
)
KEY INDEX UQ_WorkItemLongTexts_ChangedOrder --Unique index
WITH CHANGE_TRACKING OFF --Population type')
RESOLUTION 3:
- Check for the FTData folder in c drive.
If a user has moved the folder from install directory, please check the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup
Check the value of FullTestDefaultPath - Move the folder back to the location that this key points to.
Backup the transaction log for the TFSWorkitemTracking database manually by right clicking the database and pointing to Tasks and then clicking on "Back up". When the backup dialog appears, select "Transaction Log" from the "Backup Type", choose whatever other options you want and click "OK"
- For recreating the TeamFoundationServer10FullTextCatalog catalog do the following:
o In the object explorer expand: Databases --> TfsWorkItemTracking --> Storage and right click the TeamFoundationServer10FullTextCatalog catalog. Select Delete from the menu and press the OK button in the Delete Object window. This will drop the corrupted catalog.
o Right click the Full Text Catalogs item and select New Full-Text Catalog... from the menu. In the New Full-Text Catalog - TfsWorkItemTracking enter the name of the catalog:TeamFoundationServer10FullTextCatalog, select a location for the files, select Primary in the Filegroup combo box and in the Owner text box select dbo. Check the Set as default catalog check box. Press the OK button.
o Double click the newly created catalog in the Object Explorer tree. In the Full-Text Catalog Properties - TeamFoundationServer10FullTextCatalog select the Tables/Views from the left list view. Add the tables: dbo.WorkItemLongTexts to the Table/View objects assigned to the catalog. In the Eligible columns select the Words column and press the OK button. The catalog will now process and then the window will be closed.
Author: Nitish Nagpal
Reviewed by: Wendell Phillips