April 11, 2013 at 11:22 am
Hi, the user is reporting the following error, i have not done this previously. Any suggestions would be appreciated.
"Users having an issue with "databaseName" . Users receiving the following error.
Full-text catalog 'catalogName' is in an unusable state. Drop and re-create this full-text catalog."
Anyone have any script to re-create this, and also what should I make sure before doing this, this is a production change.
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.
April 12, 2013 at 6:54 am
Try this:
/*First off - lets disable*/
IF EXISTS ( SELECT
*
FROM
sys.fulltext_indexes fti
WHERE
fti.object_id = OBJECT_ID(N'[dbo].[InsertYourObjectNameHere]') )
ALTER FULLTEXT INDEX ON [dbo].[InsertYourObjectNameHere] DISABLE
GO
/*Then lets drop the index*/
IF EXISTS ( SELECT
*
FROM
sys.fulltext_indexes fti
WHERE
fti.object_id = OBJECT_ID(N'[dbo].[InsertYourObjectNameHere]') )
DROP FULLTEXT INDEX ON [dbo].[InsertYourObjectNameHere]
GO
/*Drop the catalog*/
IF EXISTS ( SELECT
*
FROM
sysfulltextcatalogs ftc
WHERE
ftc.name = N'DbName.FullTextCatalog' )
DROP FULLTEXT CATALOG [DbName.FullTextCatalog]
GO
/*Finally recreate*/
CREATE FULLTEXT INDEX ON [InsertYourObjectNameHere](CommonPlace LANGUAGE 'ENGLISH')
KEY INDEX PK_InsertYourObjectNameHere
ON [DbName.FullTextCatalog]
WITH
CHANGE_TRACKING AUTO
go
/*SSMS - generate scripts option*/
USE [YourDBNameGoesHere]
GO
IF EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'YourCatalogNameGoesHere')
EXEC dbo.sp_fulltext_catalog @ftcat=N'YourCatalogNameGoesHere', @action=N'drop'
GO
IF NOT EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'YourCatalogNameGoesHere')
CREATE FULLTEXT CATALOG [YourCatalogNameGoesHere]WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION [dbo]
GO
gsc_dba
July 22, 2016 at 2:57 pm
You may want a script that recreates all the indexes.
WITH ftCat AS (
SELECT CatalogName = QUOTENAME(c.name), is_default, is_accent_sensitivity_on,
owner = QUOTENAME(p.name COLLATE DATABASE_DEFAULT),
path = LEFT(path, LEN(path) - CHARINDEX('\', REVERSE(path)))
FROM sys.fulltext_catalogs c
INNER JOIN sys.database_principals p ON p.principal_id = c.principal_id ),
ftIdx AS (
SELECT CatalogName = QUOTENAME(cat.name),
TableName = QUOTENAME(sch.name) + '.' + QUOTENAME(tbl.name),
ColumnName = QUOTENAME(col.name),
IndexName = QUOTENAME(pk.name),
ix.change_tracking_state_desc
FROM sys.fulltext_indexes ix
INNER JOIN sys.fulltext_catalogs cat ON cat.fulltext_catalog_id = ix.fulltext_catalog_id
INNER JOIN sys.fulltext_index_columns ixc ON ix.object_id = ixc.object_id
INNER JOIN sys.tables tbl ON tbl.object_id = ix.object_id
INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id
INNER JOIN sys.columns col ON ixc.object_id = col.object_id AND ixc.column_id = col.column_id
INNER JOIN sys.indexes pk ON ix.object_id = pk.object_id AND ix.unique_index_id = pk.index_id
)
SELECT 'ALTER FULLTEXT INDEX ON ' + TableName + ' DISABLE;
DROP FULLTEXT INDEX ON ' + TableName + ';
GO'
FROM ftIdx
UNION ALL
SELECT 'DROP FULLTEXT CATALOG ' + CatalogName + ';
GO
CREATE FULLTEXT CATALOG ' + CatalogName + ' IN PATH ' + QUOTENAME(ftCat.path, CHAR(39))
+ ' WITH ACCENT_SENSITIVITY = ' + CASE is_accent_sensitivity_on WHEN 1 THEN 'ON' ELSE 'OFF' END
+ CASE is_default WHEN 1 THEN ' AS DEFAULT' ELSE '' END
+ ' AUTHORIZATION ' + owner + ';
GO'
FROM ftCat
UNION ALL
SELECT 'CREATE FULLTEXT INDEX ON ' + TableName + '(' + ColumnName + ') KEY INDEX ' + IndexName + ' ON ' + CatalogName + ' WITH CHANGE_TRACKING ' + change_tracking_state_desc + ';
GO'
FROM ftIdx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply