Is FTS Running? Is it being USED?

• mstjean

  Hall of Fame

  Points: 3310

  More actions

  October 9, 2012 at 1:48 pm

  #147628

  We have several SQL 2005 EE servers that have the FTS service running-- but I'm not sure it needs to be. So I poked around online and found "SELECT * FROM sys.fulltext_catalogs"; I assume if I find any rows in any database that means I need to leave FTS running on the server.

  I run the following on a server to see if any online db is using FTS:

  DECLARE @sss as varchar(max); SET @sss = ''

  SELECT @sss = @sss + case when @sss = '' then '' else ' union all ' end

  + 'select ''' + name + ''' as dbname,* from [' + name + '].sys.fulltext_catalogs'

  + char(13) + char(10)

  FROM sys.databases

  WHERE state_desc = 'ONLINE'

  ORDER BY name

  --PRINT @sss

  EXEC (@sss)

  I ran it on a server-- and returned no rows. But I think 1 of the dbs actually does need it; it's a document management system after all. So I dug around a bit more and found this article in BOL: "How to: Enable a Database for Full-Text Indexing (SQL Server Management Studio)" (http://msdn.microsoft.com/en-us/library/ms403375(v=sql.90).aspx).

  It says to enable a database for full-text indexing, open the db properties, select the files page and check "use full-text indexing". So I examined the suspicious db and lo and behold-- it has "use full-text indexing" checked... but it also has no entries in sys.fulltext_catalogs.

  This brings me (FINALLY) to my 2 questions:

  * Does this mean FTS is enabled in this db-- but it's never been used? (I think yes; I think this is my "least worrisome" possibility... also possible is that a server to server move of this db may have mucked up FTS, leaving the db without an expected sys.fulltext_catalogs)

  * Is there a TSQL way to check a db to see if it has "use full-text indexing" checked?

  Thanks in advance for any input.

  ---

  Cursors are useful if you don't know SQL

• mstjean

  Hall of Fame

  Points: 3310

  More actions

  October 9, 2012 at 4:20 pm

  #1547395

  This brings me (FINALLY) to my 2 questions:

  * Does this mean FTS is enabled in this db-- but it's never been used? (I think yes; I think this is my "least worrisome" possibility... also possible is that a server to server move of this db may have mucked up FTS, leaving the db without an expected sys.fulltext_catalogs)

  * Is there a TSQL way to check a db to see if it has "use full-text indexing" checked?

  I figured out the answer to question 2:

  select name,is_fulltext_enabled from sys.databases where is_fulltext_enabled=1

  Question 1 remains... What does the fact that I don't see anything in sys.fulltext_catalogs mean? Is this what fouled up FTS in a database looks like or is this what it looks like if FTS is enabled but never been used? I do think this db has been moved from one server to another in the past-- and am not sure if there was any special handling done (or required) regarding any FTS data that may have existed.

  ---

  Cursors are useful if you don't know SQL