It’s not often that I have the need or opportunity to work with FullText Indexes, and it’s also not often that I come across a new thing to add to regularly schedule maintenance tasks in SQL Server. Today I happened to do at the same time. FullText Indexes in SQL Server are really not something that cause many people problems, until they do. 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. The crawl logs are in the instance ERRORLOG path with the format of:
SQLFTXXXXXYYYYY.LOG[Z] – 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 (see BOL Topic here)
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’t open it. 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. I used Powershell Get-Content -Path “LogFilePath” -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. 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. 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:
EXEC sp_fulltext_recycle_crawl_log @ftcat = ‘FullTextCatalogName’
I know, it’s not just a straightforward task that works across everything, but most databases I deal with don’t have full text catalogs defined, so it wouldn’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. Or you can just look at the file names in the path and get the database ID and catalog ID directly.
6 thoughts on “Recycle Fulltext Catalog Log Files”
Damnit not another maintenance job! Ugh
Thanks Jonathan!
Good point!
This is a revelation, been lucky and just hadn’t run into the crawls failing. This snippet might help automate the recycle commands if you run multiple full text indexes:
select ‘EXEC sp_fulltext_recycle_crawl_log @ftcat = ”’ + name + ”’;
print ”done with ‘ + name + ”’;’
from sys.fulltext_catalogs
order by name
Thanks for the post, Jonathan!
I wrote a T-SQL script thanks to you:
https://github.com/MadeiraData/MadeiraToolbox/blob/master/Ongoing%20Maintenance%20Scripts/fulltext_recycle_crawl_log_all_DBs.sql
Enjoy!
its really useful!