FTS Not enabled for DB - but it is working(?)
-
Hello.
I have a couple of DBs that say that Full Text Search is not Enabled on the DB - yet it seems to work?
FTS catalogs were created by upgrades to SW that use them to run Contains queries via the portal - this seems to work fine. Maybe the data is stale or something - not sure about that - but the catalogs have data in.
So, I have some DMV type queries for FTS catalogs collated from around the Internet, that error because FTS is not enabled on the DB.
I can enable this with a simple command so that all looks as it should do - however this immediately triggers a Full Populate and this can take between 2 and 7.5 hours depending on the DB, It also required setting to Simple Mode due to the TLOG activity - and either way has a knock-on effect on Log Shipping replication (high volume and duration or else needs re-establishing (by other party).
Likely I will want to do this (Enable) for my own peace of mind, despite the disruption (FTS being unavailable for the duration of the Full Populate plus dealing with Log Shipping) but am unsure how FTS is working if it says it is not Enabled on a DB.
Any ideas?
TIA
Steve O.
-
This is one of the queries I run :
--USE[
--1) Is FullTextSearchEnabled in SQL Instance
SELECT
CASE FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')
WHEN 1 THEN 'Full-Text installed.'
ELSE 'Full-Text is NOT installed.'
END
;
--2 Is FullTextSearchEnabled in DB
SELECT name, Database_id, is_fulltext_enabled
FROM sys.databases
WHERE database_id = DB_ID()
--3) Is there a FulltextSearch Catalog
select *
FROM sys.fulltext_catalogs
--4) What is being Indexed
SELECT
SCHEMA_NAME(tbl.schema_id) as SchemaName,
tbl.name AS TableName,
FT_ctlg.name AS FullTextCatalogName,
i.name AS UniqueIndexName,
scols.name AS IndexedColumnName
FROM
sys.tables tbl
INNER JOIN
sys.fulltext_indexes FT_idx
ON
tbl.[object_id] = FT_idx.[object_id]
INNER JOIN
sys.fulltext_index_columns FT_idx_cols
ON
FT_idx_cols.[object_id] = tbl.[object_id]
INNER JOIN
sys.columns scols
ON
FT_idx_cols.column_id = scols.column_id
AND FT_idx_cols.[object_id] = scols.[object_id]
INNER JOIN
sys.fulltext_catalogs FT_ctlg
ON
FT_idx.fulltext_catalog_id = FT_ctlg.fulltext_catalog_id
INNER JOIN
sys.indexes i
ON
FT_idx.unique_index_id = i.index_id
AND FT_idx.[object_id] = i.[object_id];
From this snip of the output you can see that it all looks good apart from Step 2 - where the DB is Disabled.
I can change this (I have done to 1 Live system and others in Test) - but it forces a Full Population when I do.
FTS (contains) queries return results.
sp_who2 shows the FTS processes (crawl) but these appear to be system/instance wide (db name is master) and there are other FTS catalogs in other DBs in this instance
Steve O.
-
Guess I am going to change these for consistency and let the catalogs rebuild just for peace of mind that all looks OK.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply