8

Any way to disable FT logs completely? I spent hours googling - no luck.

I get tons of "informational" messages literally every second.

2020-01-01 10:43:16.48 spid33s     Informational: Full-text Auto population initialized for table or indexed view xxx
2020-01-01 10:43:23.48 spid34s     Informational: Full-text Auto population completed for table or indexed view zzz
2020-01-01 10:43:23.48 spid36s     Informational: Full-text Auto population completed for table or indexed view xxx
2020-01-01 10:43:24.64 spid12s     Informational: Full-text Auto population initialized for table or indexed view xxx
2020-01-01 10:43:25.64 spid12s     Informational: Full-text Auto population completed for table or indexed view xxx
2020-01-01 10:43:26.58 spid36s     Informational: Full-text Auto population initialized for table or indexed view xxx
2020-01-01 10:43:26.98 spid17s     Informational: Full-text Auto population initialized for table or indexed view xxx

P.S. My cloud hosting provider charges me for "i/o ops per second" so this is something I want disabled. Also, these logs grow really fast, several gigabytes every week, so I had to write a log maintenance script (rollover + archive etc.)

0

1 Answer 1

2

I'd recommend configuring the full text index for manual population; this will vastly reduce the number of "full-text auto population" messages written to the log.

The Microsoft Docs page shows how to configure a full text index for manual population.

Essentially, you explicitly define the index like so:

CREATE UNIQUE INDEX ui_ukDoc ON Production.Document(DocumentID);  
CREATE FULLTEXT CATALOG AW_Production_FTCat;  
CREATE FULLTEXT INDEX ON Production.Document  
(  
    Document                         --Full-text index column name   
        TYPE COLUMN FileExtension    --Name of column that contains file type information  
        Language 1033                 --1033 is LCID for the English language  
)  
    KEY INDEX ui_ukDoc  
    ON AW_Production_FTCat  
    WITH CHANGE_TRACKING OFF, NO POPULATION;  
GO  

Then, you'd need to schedule population via a SQL Server Agent job with the following command:

ALTER FULLTEXT INDEX ON Production.Document  
   START FULL POPULATION;  

Once you've completed the full population, you can schedule partial, incremental updates of the full text index via:

ALTER FULLTEXT INDEX ON Production.Document  
   START INCREMENTAL POPULATION;  

INCREMENTAL
Specifies that only the modified rows since the last population be retrieved for full-text indexing. INCREMENTAL can be applied only if the table has a column of the type timestamp. If a table in the full-text catalog does not contain a column of the type timestamp, the table undergoes a FULL population.

or

ALTER FULLTEXT INDEX ON Production.Document  
   START UPDATE POPULATION;  

UPDATE
Specifies the processing of all insertions, updates, or deletions since the last time the change-tracking index was updated. Change-tracking population must be enabled on a table, but the background update index or the auto change tracking should not be turned on.

You only need to do the full population once, after you initially create the full-text index. This page has details regarding incremental or update populations, and the requirements.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.