Malaika Consultants LLC

Why does my full-text search index goes empty for no reason?

by Swanand Mokashi 21. February 2012 20:54

I have posted the following to the SQL Server forums as well, but no luck so far Sad smile:

I am using SQL Server full text search with SQL 2008. The database we use is mostly read-only. It is updated by an external process on a weekly basis. The database has 4 tables on which full text index has been defined. I am only using the CONTAINS clause in my SQL to utilize the full text search, Nothing fancy.

The external process mentioned above, truncates the tables of the database and imports data to them from CSV files. After the external process completes its task, I am rebuilding the search index. This seems to work fine, for after a few minutes of the external process completion, I am able to get search results correctly. However the full text index seems to get empty for some reason after a few hours/days (I have not been able to find any pattern to when it happens). When this happens, I sometimes get either no search results, or partial results. If I manually rebuild the index from SQL Server Management tool, it works fine after that. Again the tables that have the index defined really do not have any data change, but I still have setup a sql server task to rebuild the search index both on the catalog as well as the individual tables. But this has not helped much

A few things I would like to understand:

  1. What is causing the index to be empty (or partially filled)? And how to fix it?
  2. Is there any way to monitor the status of the index to know how much has been rebuild?
  3. The sql server jobs mentioned above : where can I see the history for them?

Please let me know if any other information is needed to help me solve this issue. It has been driving me nuts.

Tags: ,

Database

::: | © Malaika Consultants LLC | :::

Malaika Consultants LLC

Malaika Consultants LLC is a custom software development consulting firm located in Cary (Raleigh Durham Research Triangle AKA RTP area ) North Carolina. We offer our expert consulting services in the Microsoft .NET and related technologies. Our mission is to partner with you and ensure the success of your project. We have a team in the USA that will help you with your Information Technology needs and we strive to be truly "Your Information Technology Angels"

We are offering 1 hour of free consulting -- ASP.NET, SQL Server, IIS, anything. Contact us to get your free consulting

Recent Comments

None