none
SQL2005 Express FREETEXTTABLE lost rank

    Question

  • Hi everybody,

    I have trouble with full text search in one database running on SQL 2005 Express SP2 server. When I run this query:

    SELECT * FROM 
    FREETEXTTABLE(Content_Page, FullTextSearch, ' test '

    It returns me correct result, but RANK column is 0 for all rows. I read some forums and I try first to Rebuild Catalog. After rebuild catalog, I get correct result:

    KEY RANK  
    43  925  
    44  925  
    45  925  
    46  925  
    47  925  
    50  939  
    51  939  
    52  925  
    55  925  
    56  939  
    57  925  
    58  925  
    59  939  
    70  925  
    71  925  
    72  763  
    77  839  
    78  805  
    79  805  
    80  805  
    81  805  
    82  805  
    85  763 

    but after let's say 5 queries or 1 minute I got again this:

    KEY RANK  
    43  0  
    44  0  
    45  0  
    46  0  
    47  0  
    50  0  
    51  0  
    52  0  
    55  0  
    56  0  
    57  0  
    58  0  
    59  0  
    70  0  
    71  0  
    72  0  
    77  0  
    78  0  
    79  0  
    80  0  
    81  0  
    82  0  
    85  0 

    As u can see, records are same, but because RANK = 0, order of records is lost, so I can't make ORDER BY RANK.

    I tried to set pre-compute rank:

    exec sp_configure 'precompute','0' 
    reconfigure with override  
    go  
     

    Setting it ON or OFF change nothing. Still same scenario. After few queries or time (i'm more sure about time) all RANK get reset to 0.

    I have 2 other databases running on this server and they have no trouble with lost rank number. Each database have own full text catalog.

    I tried re-create catalog, set pre-compute rank, nothing helps. Only rebuild fix it but only for short time (1 minute).

    Here are full text catalog settings (I copy it all from properties tab in SQL Management Studio):

    Unique Key Count: 171  
    Item Count: 30  
    Accent Sensitive: False (i tried True/False, not help)  
    Owner: dbo  
    Filegroup: PRIMARY  
    Default Catalog: False (i tried True/False, not help)  
    Last Population Date: 23.1.2009 9:49 (rebuild time by myself)  
    Catalog Size: 0MB  
    Population Status: Idle 

    Track changes I have set AUTO
    and I have no population schedule set.

    Friday, January 23, 2009 2:04 AM

Answers