{"id":2168,"date":"2020-05-28T15:24:28","date_gmt":"2020-05-28T19:24:28","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/jonathan\/?p=2168"},"modified":"2020-05-28T15:55:05","modified_gmt":"2020-05-28T19:55:05","slug":"recycle-fulltext-catalog-log-files","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/","title":{"rendered":"Recycle Fulltext Catalog Log Files"},"content":{"rendered":"<p>It&#8217;s not often that I have the need or opportunity to work with FullText Indexes, and it&#8217;s also not often that I come across a new thing to add to regularly schedule maintenance tasks in SQL Server.\u00a0 Today I happened to do at the same time.\u00a0 FullText Indexes in SQL Server are really not something that cause many people problems, until they do.\u00a0 When a problem does happen with a FullText population or crawl, the Log file for the catalog is the starting point for troubleshooting what is causing the problems.\u00a0 The crawl logs are in the instance ERRORLOG path with the format of:<\/p>\n<blockquote><p>SQLFTXXXXXYYYYY.LOG[Z] &#8211; where X is the database ID with leading zeros and Y is the catalog ID with leading zeros and Z is the log file number in sequence (<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/search\/populate-full-text-indexes#crawl\">see BOL Topic here<\/a>)<\/p><\/blockquote>\n<p>The problem is that this file just grows and grows and if your catalog has a lot of indexes that are automatically tracking changes then it can basically so large you can&#8217;t open it.\u00a0 The one I had to deal with today was 4GB in size and made me wish that Windows had the good old Linux tail command.\u00a0 I used Powershell Get-Content -Path &#8220;LogFilePath&#8221; -Last 1000 and then slowly walked backwards in increments of 1000 until I found the point in the file that mattered to me, so all is not lost. However, the LOG file in use had a start date of March 12, 2019 which in my opinion if it was broken that long we have other things to talk about.\u00a0 So just like my recommendation for the SQL Server ErrorLog and calling sp_cycle_errorlog in a job to roll the file over, I made a recommendation to recycle the FullText crawl log.\u00a0 To recycle the crawl log, you have to execute sp_fulltext_recycle_crawl_log for each of the Catalogs in each database that uses FullText:<\/p>\n<blockquote><p>EXEC sp_fulltext_recycle_crawl_log @ftcat = &#8216;FullTextCatalogName&#8217;<\/p><\/blockquote>\n<p>I know, it&#8217;s not just a straightforward task that works across everything, but most databases I deal with don&#8217;t have full text catalogs defined, so it wouldn&#8217;t be difficult to use sys.databases to get a list of databases with is_fulltext_enabled = 1 and check if they have a catalog in them to enable the job.\u00a0 Or you can just look at the file names in the path and get the database ID and catalog ID directly.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>It&#8217;s not often that I have the need or opportunity to work with FullText Indexes, and it&#8217;s also not often that I come across a new thing to add to regularly schedule maintenance tasks in SQL Server.\u00a0 Today I happened to do at the same time.\u00a0 FullText Indexes in SQL Server are really not something [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2168","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Recycle Fulltext Catalog Log Files - Jonathan Kehayias<\/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:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Recycle Fulltext Catalog Log Files - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"It&#8217;s not often that I have the need or opportunity to work with FullText Indexes, and it&#8217;s also not often that I come across a new thing to add to regularly schedule maintenance tasks in SQL Server.\u00a0 Today I happened to do at the same time.\u00a0 FullText Indexes in SQL Server are really not something [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2020-05-28T19:24:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-05-28T19:55:05+00:00\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/recycle-fulltext-catalog-log-files\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/recycle-fulltext-catalog-log-files\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"Recycle Fulltext Catalog Log Files\",\"datePublished\":\"2020-05-28T19:24:28+00:00\",\"dateModified\":\"2020-05-28T19:55:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/recycle-fulltext-catalog-log-files\\\/\"},\"wordCount\":413,\"commentCount\":6,\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/recycle-fulltext-catalog-log-files\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/recycle-fulltext-catalog-log-files\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/recycle-fulltext-catalog-log-files\\\/\",\"name\":\"Recycle Fulltext Catalog Log Files - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"datePublished\":\"2020-05-28T19:24:28+00:00\",\"dateModified\":\"2020-05-28T19:55:05+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/recycle-fulltext-catalog-log-files\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/recycle-fulltext-catalog-log-files\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/recycle-fulltext-catalog-log-files\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Uncategorized\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/uncategorized\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Recycle Fulltext Catalog Log Files\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Recycle Fulltext Catalog Log Files - Jonathan Kehayias","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:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/","og_locale":"en_US","og_type":"article","og_title":"Recycle Fulltext Catalog Log Files - Jonathan Kehayias","og_description":"It&#8217;s not often that I have the need or opportunity to work with FullText Indexes, and it&#8217;s also not often that I come across a new thing to add to regularly schedule maintenance tasks in SQL Server.\u00a0 Today I happened to do at the same time.\u00a0 FullText Indexes in SQL Server are really not something [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/","og_site_name":"Jonathan Kehayias","article_published_time":"2020-05-28T19:24:28+00:00","article_modified_time":"2020-05-28T19:55:05+00:00","author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"Recycle Fulltext Catalog Log Files","datePublished":"2020-05-28T19:24:28+00:00","dateModified":"2020-05-28T19:55:05+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/"},"wordCount":413,"commentCount":6,"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/","name":"Recycle Fulltext Catalog Log Files - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"datePublished":"2020-05-28T19:24:28+00:00","dateModified":"2020-05-28T19:55:05+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/recycle-fulltext-catalog-log-files\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Uncategorized","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/uncategorized\/"},{"@type":"ListItem","position":3,"name":"Recycle Fulltext Catalog Log Files"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/2168","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=2168"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/2168\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=2168"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=2168"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=2168"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}