Sql-server – Disable full-text logging (SQL Server)

full-text-searchsql server

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.)

Best Answer

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.