Troubleshooting vanishing fulltext index in SQL Server 2012

Question

I am trying to get to the bottom of some weirdness. Yesterday I enabled a fulltext index on a table in SQL Server 2012 running on one of our LAN servers. I used the Wizard and set the index to update automatically. I had put some test data into the table and ran some CONTAINS queries and they worked fine.

Today I added some more data to the table and ran some CONTAINS() queries, and they failed with this error message:

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'foo' because it is not full-text indexed.

What could make the fulltext index ephemeral? How can I get the status of fulltext indexing in this database, including which tables have been indexed?

When I go back in to the Fulltext Wizard to recreate the indexes, the catalog that I created is still present.

EDIT: I did confirm that there were data in the table. Test queries were working. Nothing was deleted, but there was indeed some kind of anomaly overnight that caused the server to shut down. Is the definition of the fulltext index stored in the Master database?

Answer 1

Catalog and index are separate. What do you see in sys.fulltext_indexes? Did you confirm data there after the index was created?

You could check for recent Object:Deleted events in the default trace:

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
  LoginName,
  HostName,
  StartTime,
  ObjectName,
  TextData
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
-- AND StartTime > 'yyyymmdd' -- rough date/time you created the index
ORDER BY StartTime DESC;

Now, your default trace may be very busy with a lot of relevant events, in which case the drop could have rolled off the current set of trace files, but this is a rare scenario.

Another case where it might not show up in the default trace is if the database was restored to an earlier point in time - you may find some evidence of this in SQL Server's error log (though this only keeps so much history too - if you've restarted SQL Server six times, for example, any information from before that is gone).