Post reply

TSQL query not working properly against Full Text Index

  • May 23, 2019 at 3:28 pm

    #3646559

    Hi,

    I've been having this issue for quite some time and need some help to understand it. I am not sure if this is an "expected behavior" or, as the user is indicating, an issue that needs to be fixed or corrected somehow from MSSQL.

    This query suppose to return two rows

    SELECT * 
    FROM [MyTable] Where CONTAINS(Title, 'PBR')

    But after a few days or weeks, it is no longer working and only returning 1 row. This breaks the application and the user raise a service ticket as a consequence.

    The only fix is re indexing the FULL Text index, this way

    ALTER FULLTEXT CATALOG MyIndex REBUILD

    But why?

    The Population schedule is this:

    Type: Catalog-optimize

    Weekly basis  on Saturdays

    Also, The column in the FT index is nvarchar(400)

    Why the catalog gets out of sync or needs to be rebuilt to fix this issue? Do I have to alter the FT schedule in order to fix this?

    Last but not least, I am not sure if this is relevant, but this is the current MSSQL version of that box:

    Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

     

     

     

     

     

     

  • May 24, 2019 at 4:10 pm

    #3647043

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • May 24, 2019 at 6:20 pm

    #3647091

    This might help you: https://www.sqlshack.com/automatically-maintain-full-text-indexes-catalogs/

  • May 24, 2019 at 7:30 pm

    #3647104

    Thanks Steve

    But isn't that more focused towards performance and fragmentation? I actually took a look on it yesterday, before you post it.

     

  • May 24, 2019 at 7:56 pm

    #3647108

    It is, but some maintenance stuff in there. Unless the data changes, I wouldn't expect you to lose rows from an index out of date. You might miss new rows, but my guess is there is something strange with the index.

    Is it always the same value missing rows? Or is the user adding data and not seeing it?

  • May 24, 2019 at 8:04 pm

    #3647112

    Did you check the full text logs in the log directory for any issues, errors?

    Sue

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply