{"id":3583,"date":"2012-05-22T14:28:40","date_gmt":"2012-05-22T13:28:40","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=3583"},"modified":"2019-09-13T14:17:48","modified_gmt":"2019-09-13T13:17:48","slug":"repeupler-un-index-fulltext","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/","title":{"rendered":"Repeupler un index FullText"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Cet article concerne un probl\u00e8me rencontr\u00e9 chez l’un de nos clients\u00a0sur la mise \u00e0 jour d’un index fulltext :<\/p>\n

Voici la question du client (les informations confidentielles sont masqu\u00e9es):<\/span>
\nSuite \u00e0 une plainte d’un client, nous avons d\u00e9couvert que la table TB_appel a un d\u00e9faut d’indexation FT.<\/span><\/p>\n

s<\/span>elect DISPLAY_TERM
\nfrom sys.dm_fts_index_keywords_By_Document (db_id(‘db1’),object_id(‘ TB_appel’))
\nWHERE document_id = 549118<\/span><\/p>\n

retourne aucun contenu.<\/span><\/p>\n

Apparement, depuis le ..\/..2012, aucune annonce n’a \u00e9t\u00e9 ind\u00e9x\u00e9e en FT.<\/span><\/p>\n

Pouvez voir ce qui ce passe ?<\/span><\/p>\n

Voici la m\u00e9thode de r\u00e9solution utilis\u00e9e :<\/span><\/p>\n

1- je v\u00e9rifie la requ\u00eate, effectivement aucune ligne retourn\u00e9e …<\/p>\n

2-v\u00e9rification du type de mise \u00e0 jour sur l’index :<\/p>\n

select * from sys.fulltext_indexes where object_id= object_id('dbo.TB_appel')\r\nobject_id\u00a0\u00a0 unique_index_id fulltext_catalog_id is_enabled change_tracking_state change_tracking_state_desc\u00a0\u00a0\u00a0\u00a0 ...\r\n----------- --------------- ------------------- ---------- --------------------- ------------------------------ ...\r\n1694733190\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 13\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AUTO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...<\/pre>\n
La propri\u00e9t\u00e9 change_tracking est bien positionn\u00e9e \u00e0 AUTO,
\nl’index devrait \u00eatre mis \u00e0 jour par le moteur en fonction des mises \u00e0 jour sur la table.<\/p>\n
3 – je v\u00e9rifie l’\u00e9tat \u00a0des populations (mise \u00e0 jour) d’indexes en cours<\/p>\n
  -- populations en cours\r\n\r\n   select database_id, catalog_id , table_id, population_type_description , status, status_description\t\r\n   from sys.dm_fts_index_population where table_id=object_id('TB_appel') and database_id=6\r\n   database_id\tcatalog_id\ttable_id\tpopulation_type_description\tstatus status_description\r\n   ----------- ----------- ----------- ------------------------------ ------------------ --------\r\n   6\t               13\t     1694733190\tAUTO\t                                11\tAborted<\/pre>\n
La colonne status_description contient la valeur Aborted<\/span> <\/strong><\/p>\n
4-\u00a0J’annule la reconstruction sur l’index<\/p>\n
  \r\n-- stop la population\r\n  ALTER FULLTEXT INDEX ON dbo.TB_appel SET CHANGE_TRACKING MANUAL;\r\n  Command(s) completed successfully.<\/pre>\n
5- Je lance une population manuelle de l’index<\/p>\n
-- mise \u00e0 jour de l'index\r\n   set  statistics time on\r\n   ALTER FULLTEXT INDEX ON dbo.TB_appel START UPDATE POPULATION;\r\n\r\n   * info - UPDATE : Sp\u00e9cifie le traitement de toutes les insertions, mises \u00e0 jour ou suppressions depuis la derni\u00e8re mise \u00e0 jour de\r\n    l'index de suivi des modifications. Le remplissage du suivi des modifications doit \u00eatre activ\u00e9 sur une table,\r\n    mais l'index de mise \u00e0 jour en arri\u00e8re-plan ou le suivi automatique des modifications doivent \u00eatre d\u00e9sactiv\u00e9s.<\/pre>\n
6- Je suis l’avancement de la population de l’index<\/p>\n
  \r\nselect * from sys.dm_fts_index_population where table_id=object_id('TB_appel')  and database_id=6\r\n\r\n  select database_id, catalog_id , table_id, population_type_description , status_description\t\r\n  from sys.dm_fts_index_population where table_id=object_id('TB_appel') and database_id=6\r\n  database_id\tcatalog_id\ttable_id\tpopulation_type_description\tstatus status_description \r\n\r\ndatabase_id      catalog_id           table_id     population_type_description   status_description \r\n   ----------- -----------       -----------     ---------------------------  ------------------ \r\n   6                        13           1694733190       MANUAL                          Processing normally ...      \r\n\r\n -- Suivi des ranges de valeurs \u00e0 mettre \u00e0 jour\r\n\r\n  SELECT ranges.*, ranges.*, OBJECT_NAME (table_id)\r\n \u00a0 FROM sys.dm_fts_index_population population\r\n   JOIN sys.dm_fts_population_ranges ranges\r\n   ON (population.memory_address = ranges.parent_memory_address)\r\n   WHERE ranges.session_id <> 0\r\n  memory_address     parent_memory_address is_retry session_id processed_row_count error_count\r\n   ------------------ --------------------- -------- ---------- ------------------- ----------- ------------------------------\r\n   0x078B2040         0x055226A8            0        29         6000                0\t\t\tTB_appel\r\n   0x078B3048         0x055226A8            0        30         6000                0\t\t\tTB_appel\r\n   => on voit le nombre de lignes trait\u00e9es<\/pre>\n
7- Une fois la repopulation de l’indes termin\u00e9e, je remets l’index en mode CHANGE_TRACKING AUTO<\/p>\n
   ALTER FULLTEXT INDEX ON dbo.TB_appel SET CHANGE_TRACKING AUTO;<\/pre>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"
Cet article concerne un probl\u00e8me rencontr\u00e9 chez l’un de nos clients\u00a0sur la mise \u00e0 jour d’un index fulltext : Voici la question du client (les informations confidentielles sont masqu\u00e9es): Suite \u00e0 une plainte d’un client, nous avons d\u00e9couvert que la… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":14,"featured_media":7880,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[226],"class_list":["post-3583","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlserver","tag-ifts"],"yoast_head":"\nRepeupler un index FullText - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Repeupler un index FullText - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"Cet article concerne un probl\u00e8me rencontr\u00e9 chez l’un de nos clients\u00a0sur la mise \u00e0 jour d’un index fulltext : Voici la question du client (les informations confidentielles sont masqu\u00e9es): Suite \u00e0 une plainte d’un client, nous avons d\u00e9couvert que la… Continuer la lecture →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2012-05-22T13:28:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-13T13:17:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2012\/05\/fulltext.png\" \/>\n\t<meta property=\"og:image:width\" content=\"655\" \/>\n\t<meta property=\"og:image:height\" content=\"231\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Benjamin VESAN\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Benjamin VESAN\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/\"},\"author\":{\"name\":\"Benjamin VESAN\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e\"},\"headline\":\"Repeupler un index FullText\",\"datePublished\":\"2012-05-22T13:28:40+00:00\",\"dateModified\":\"2019-09-13T13:17:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/\"},\"wordCount\":216,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"iFTS\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/\",\"name\":\"Repeupler un index FullText - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2012-05-22T13:28:40+00:00\",\"dateModified\":\"2019-09-13T13:17:48+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Repeupler un index FullText\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e\",\"name\":\"Benjamin VESAN\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/bvesan\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Repeupler un index FullText - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/","og_locale":"fr_FR","og_type":"article","og_title":"Repeupler un index FullText - Capdata TECH BLOG","og_description":"Cet article concerne un probl\u00e8me rencontr\u00e9 chez l’un de nos clients\u00a0sur la mise \u00e0 jour d’un index fulltext : Voici la question du client (les informations confidentielles sont masqu\u00e9es): Suite \u00e0 une plainte d’un client, nous avons d\u00e9couvert que la… Continuer la lecture →","og_url":"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2012-05-22T13:28:40+00:00","article_modified_time":"2019-09-13T13:17:48+00:00","og_image":[{"width":655,"height":231,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2012\/05\/fulltext.png","type":"image\/png"}],"author":"Benjamin VESAN","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Benjamin VESAN","Dur\u00e9e de lecture estim\u00e9e":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/"},"author":{"name":"Benjamin VESAN","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e"},"headline":"Repeupler un index FullText","datePublished":"2012-05-22T13:28:40+00:00","dateModified":"2019-09-13T13:17:48+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/"},"wordCount":216,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["iFTS"],"articleSection":["SQL Server"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/","url":"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/","name":"Repeupler un index FullText - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2012-05-22T13:28:40+00:00","dateModified":"2019-09-13T13:17:48+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/repeupler-un-index-fulltext\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Repeupler un index FullText"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e","name":"Benjamin VESAN","url":"https:\/\/blog.capdata.fr\/index.php\/author\/bvesan\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/3583","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=3583"}],"version-history":[{"count":20,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/3583\/revisions"}],"predecessor-version":[{"id":7881,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/3583\/revisions\/7881"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/7880"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=3583"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=3583"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=3583"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}