Full text index rebuild RRS feed

  • Question

  • We've developed an ASP.NET MVC application serving hundreds of concurrent users on a sql server 2016 database (running in compatibility mode 2008).

    One of our user defined functions is not returning any data from a "contains" statement, which uses an FTI catalog for a table containing binary objects (mainly pdf documents). This particular table is approx 381 gb in size, the database is around 1.5 tb.

    Looks like this catalogue needs to be rebuilt as the last population date is blank?

    What I'd like advice on is what the implications are for rebuilding this full text index on a 1.5 tb database would be?

    Could this present any resource issues that could potentially cause CPU spikes, locking issues, tempdb growth, downtime etc?

    Any advice on alternative strategies would be appreciated i.e. rebuilding the catalog outside of the database to reduce any such issues?

    Wednesday, September 4, 2019 1:11 PM

All replies

  • Why do you say you think the catalog needs to be rebuilt? By default it should be automatically populated. Unless you have a reason to think the full text population did not succeed this will not likely be helpful.

    Check the full text logs in the SQL server error log directory. On my machine they are in :

    C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log

    This is SQL 2016. They will look like: SQLFT0005100005.txt.

    Right click on your full-text catalog to see what your unique key count is. If it is 0 you might need to rebuild. Otherwise you will not.

    I would only rebuild the index on that table. Drill down on the table in the database, right click on it and select full-txt index and follow the prompts to do a full population

    There will be a cpu hit while the catalog rebuild is going on. There will also be significant IO as that table is reindexed.

    I would focus on your query to ensure that it is working. You can also use the full-text catalogs DMV to ensure that your word documents are being indexed.
    Wednesday, September 4, 2019 1:27 PM
  • (mainly pdf documents).

    Is the PDF full text index filter installed at all; by Default it's not? You can check it with

    EXEC sp_help_fulltext_system_components 'filter'; 


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 4, 2019 1:51 PM