It has happened several times...but we've just started tracking this.

Yesterday and today I tried to do a fulltext search and server came back
with an error telling me that fulltext search isn't enabled for the database


I run sp_fulltext_database 'enable', then it starts working again.


What's going on?

Kyle!

I will also note that we just cleared the table and reset the identity seed.

Could this be causing it?

[quoted text, click to view]

I tried to repro your problem but was unable to do so. What is your os?
and SQL Server version?

Yes, any time you edit properties that are connected to the index (like
an indexed field or the identiy column), it in essence resets the index
and you will have to repopulate it. Since the index "indexes" the
columns by indentity column...reseting the identity column would
deffinately reset it.

Im not sure if dbcc checkident does the same thing as a modification
through EM though. When you remove and reapply the identity property
or change one of its values it in effect creates a copy of the table
then drops the old version. One question about your dbcc checkident
is...did it change anything ? Because if you were to even change the
identity value of a single row...I believe you would have to
reindex...unless you have set it to incrementally populate. I have
personally seen the effect of this in my own index when editing indexed
tables (through EE).

I am not sure if this is accurate. I tried it by using dbcc checkident and
it had no impact. I did not try it by modifying it through EM though.

[quoted text, click to view]

dbcc checkident with reseed will set a value for the next identity
value, so dbcc checkident('tablename',reseed, 1000) will have the
effect of giving the next identity value for tablename a value of 1000.
It will not allow you to change the increment. So it has some of the
functionality that EM gives you, but not everything.

Running the DBCC CheckIdent command and alter table commands will not
break the full-text index.

If you do us EM, as you point ou, to make modifications, you will break
the full-text index, and you will get a warning message that they could
be broken. Not good when you have spend several days populating your
index

AFAIK - there is no way to modify a column which has a identity value
and is part of a pk, so I can't test the validity of your statement. If
you do make changes they will not be picked up until you run a full
population, incremental population, or update your index if you are
using change tracking. If you are using change tracking with update the
index in the background, they should be picked up within 10 or so secs.

DBCC checkident reseed will only impact new rows, as will changes made
through EM.

Most DBAs don't use EM to make schema modifications as
1) it involves a temp table which means
a) pushing all data into the temp table - a table scan
b) dropping constraints
c) tables must go offline while you are doing this
2) dba's prefer to script schema changes to avoid typo's, errors, and
for recoverability
3) em caches information, so the table can be changes underneath
without you knowing about it
4) you get warning dialogs that some event has happened with no
possibility to roll back - warning like you have broken your full-text
index

HTH