Symptoms
After you import your organization by using Deployment Manager, a system job of FullTextCatalogIndex in the imported organization stops working and stays in a waiting status. If you applied KB977867, you won't have the issue.
Cause
Import organization does not rename the full text catalog and the imported organization database has stored the full text catalog which contains the backed-up organization's GUID. System jobs try to execute the full text catalog update which contains the current organization id ftcat_documentindex_<OrganizationIdGuidValue> and then changes to waiting status.
Resolution
In order to resolve this issue run the following SQL script on your organization database:
DECLARE @ID AS NVARCHAR(128),
@OLDNAME AS NVARCHAR(128),
@NEWNAME AS NVARCHAR(128),
@LINES AS INT
SELECT @OLDNAME = name FROM SYS.FULLTEXT_CATALOGS WHERE name LIKE 'ftcat_documentindex_%'
SELECT @ID = OrganizationId FROM Organization
SET @ID = REPLACE(@ID,'-','')
SET @ID = LOWER(@ID)
SET @NEWNAME = 'ftcat_documentindex_' + @ID
PRINT 'GUID IS : '+@ID
PRINT '---------------------'
PRINT 'OLDNAME IS : '+@OLDNAME
PRINT '---------------------'
PRINT 'NEWNAME IS : '+@NEWNAME
PRINT '---------------------'
IF @OLDNAME!=@NEWNAME
BEGIN
EXEC sp_fulltext_database 'enable'
-- Drop exsiting full text catalog
PRINT 'START DROPPING EXSITING FULL TEXT CATALOG ..'
PRINT '---------------------'
IF EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[DocumentIndex]'))
BEGIN
ALTER FULLTEXT INDEX ON [dbo].[DocumentIndex] DISABLE
END
IF EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[DocumentIndex]'))
BEGIN
DROP FULLTEXT INDEX ON [dbo].[DocumentIndex]
END
SELECT @LINES = COUNT(*) FROM sysfulltextcatalogs ftc WHERE ftc.name = @OLDNAME
IF @LINES > 0
BEGIN
EXEC sp_fulltext_catalog @OLDNAME,'drop'
END
PRINT 'DROPPING COMPLETE ..'
PRINT '---------------------'
-- Add new full text catalog
PRINT 'START ADDING EXSITING FULL TEXT CATALOG ..'
PRINT '---------------------'
EXEC sp_fulltext_catalog @NEWNAME,'create'
EXEC sp_fulltext_table 'documentindex', 'create', @NEWNAME, 'cndx_PrimaryKey_DocumentIndex'
EXEC sp_fulltext_column 'documentindex', 'Title', 'add'
EXEC sp_fulltext_column 'documentindex', 'KeyWords', 'add'
EXEC sp_fulltext_column 'documentindex', 'SearchText', 'add'
EXEC sp_fulltext_table 'documentindex', 'activate'
EXEC sp_fulltext_catalog @NEWNAME, 'start_full'
PRINT 'ADDING COMPLETE ..'
END
ELSE
PRINT 'THE FULL TEXT CATALOG HAS BEEN UPDATED ..'
