Rebuild TFS Full Text Index
Without this full text index, you're not able to run queries in the History or Description fields of the 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')