SQL Server Performance Forum – Threads Archive
Frequency of Running FullText Catalog Incremental?
I’m new to full text catalogs and we have a vendor who’s code utilizes them. The database server is SQL 2005 and I am noticing the following message in the SQL log every minute. Changing the status to MERGE for full-text catalog "ResearchCatalog" (5) in database "DBA_Test" (11). This is an informational message only. No user action is required. A SQL job is running the following command every minute. exec sp_fulltext_catalog ‘ResearchCatalog’, ‘start_incremental’ What is the typical frequency for running an incremental? Can the messages be suppressed? Thanks, DaveI believe this was a problem in SQL 2000 SP4 onwards, as it seems inherited from it. One of the KBA refers:
Disable background updates by running the sp_fulltext_table stored procedure with the stop_background_updateindex option. Instead of using background updates, use the update_index option at a scheduled time to apply the full-text index changes. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Thanks Satya. Do you know if it is common to run an incremental every few minutes or is it more common to run them once or twice a day? I’m trying to understand why the vendor feels the job should be scheduled every three minutes. Correct me if I am wrong, but if someone adds records to a table between incrementals, the data will still be available for querying, although it will not be added to the index until an incremental is run. Or am I incorrect and the data will only be available after an incremental or full reindex? Thanks, Dave
I’m not aware of any standard schedule for such schedule for FTs in this case, it depends on the business logic and how busy the queries are accessed. So I would guess to have a thorough look on the usage of system in this case. With incremental population only changed rows are retrieved for full-text indexing, provided there is a timestamp column present in the table being full-text indexed. If you perform the start_full then there will be a contention until it finishes, for this reason it is suggested to perform during less traffic hours on the database. Yes the data will still be available during the incremental process. Check the books online for "Change Tracking" and "Update Index in Background" and avoid the need to run the process on a scheduled basis.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
]]>