October 9, 2012 at 1:48 pm
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.
October 9, 2012 at 4:20 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply