SQL Server 2008R2 fulltext logfile SQLFT*.log maintenance
Question
Thursday, March 1, 2012 8:19 AM
Hi all,
we're running an SQL Server 2008R2 Std with fulltext indexing on various tables, chang tracking is on. The logfile SQLFT*.log of the fulltext indexer grows about 2Gbytes every 2 weeks. Reason for this is that the system has a rather high change rate and the fulltext indexer does not only log errors but loads of informational messages every few minutes. (Last week 1 had one! error message somewhere in there with 1,8 GBytes of info messages surrounding it)
As this logfile cannot be maintained using sql server management studio (does not appear there), we must use standard editors to search for errors, which is a problem with files this size. Apart from that, it starts to consume much more disk space than healthy for the system.
Possible solutions could be:
a) prevent the indexer from producing so much info messages, just log errors
b) switch the file to a new version before it gets to big and purge the versions on a scheduled basis
So far, I found no solution to a) and the only solution to b) was to restart the sql server as a whole. This is absolutely not accepteable as this is a 24x7 system. Switching off change tracking to produce fewer info messages is not an option, as users need to find changes and new arrivals within a few seconds in their searches.
Any suggestions how to solve this problem by configuration or regular maintenance procedure?
All replies (6)
Wednesday, March 7, 2012 8:00 AM ✅Answered
Hi Birgit Straker,
Thank you for your update, cycles the error log extension numbers just like a server restart as I mentioned above. The documentation on the SQL Server Full Text Catalog log files (SQLFT*.LOG#) includes statements as to the default location of these files. The size of these files can directly be attributed to the databases they are associated with (SQLFT<database_ID><SPID>.LOG#), but there is no mechanism to include their space allocations as part of the space used by the database.
>> that this logfile cannot be cycled at all or are there other options
Based on my research cycle error log feature is used to agent_errorlog and errorlog.
For more information, please refer to
- sp_cycle_errorlog (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms182512.aspx
- sp_cycle_agent_errorlog (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms186874.aspx
The fastest way to get this log cleared is to restart the instance in a scheduled outage. This should only take a few minutes.
Regards, Amber zhang
Thursday, December 27, 2012 9:35 PM ✅Answered | 1 vote
Hello,
I am trying to find a way to either delete the SQLFT*.log files which are not in use or move them to a different location. When researching on this issue, I came across this post. I have noticed an undocumented stored procedure which seems to be similar to sp_cycle_errorlog but am not able to find as much information on it as it is undocumented. The other problem is this stored procedure is using an undocumented DbCC command, so even if i created my own stored procedure, not sure for how long will I be able to use this undocumented DBCC Command. I say this, because if MS decides to remove this sp or dbcc command in future versions, then there wont be any option available. The undocumented stored procedure is:
sp_fulltext_recycle_crawl_log
And the DBCC Command it is using
DBCC CALLFULLTEXT ``( 20, ``@ftcat``)
This is a procedure in the mssqlsystemresource database. The sp_fulltext_recycle_crawl_log system stored procedure is used to recycle crawl log for the given full-text catalog name. The sp_fulltext_catalog cannot be executed under master, tempdb or model databases.
If anyone has more information on this stored procedure or alternate solution to move the ft files or delete them, please let me know.. I would appreciate any help on this.
Bijal
Monday, March 5, 2012 5:50 AM
Hi Birgit Straker,
Regarding to your description, the huge log file possibly related to the instance is never stopped and there are real errors logged in these errorlog files.
If the SQL Server isn't restarted regular, the errorlog file can grow up to a very large size. But as you mentioned it doesn’t allowed restarting server, so there is a way to trigger the recycling of the errorlog.
There is a system stored procedure: sp_cycle_errorlog. This command triggers a cycling of the errorlog as a restart of SQL Server would do. You can schedule this stored procedure with the Windows Scheduler service by issuing this stored procedure with sqlcmd.exe command line tool, e.g. sqlcmd -S <servername> -E -Q exec sp_cycle_errorlog on a regular base. With the -E switch the SQL Server must run in Windows or Mixed Authentication mode. You need to run that command with a Windows user account that has administrative privileges on that SQL Server.
For more information, please refer to this blog: SQL Server 2005 Error Log Management
According to real errors logged, the full-text indexer failures can occur when the indexer cannot find or load a filter or word breaker component, which might fail to index one or more rows. For how to troubleshooting Full-text indexer, please refer to this article.
Additional for the disk space, since the default location of the SQLFT logs is the default Error Dump directory path, the default location cannot be changed until the default Error Dump directory path is changed. If you think you do not need you can try to move them. The current log file obviously cannot be moved, as it will give file in use.
Regards, Amber zhang
Monday, March 5, 2012 10:12 AM
Hi Amber,
this is exactly the type of solution I'm looking for, unfortunately, this one does not work for the SQLFT*.log. The procedure sp_cycle_errorlog does indeed cycle the sql server's main errorlog named errorlog.log, but does not cycle the fulltext indexer's errorlog SQLFT*.log. This is in the moment only done when restarting sql server, which is not an option. In older versions of sql server (2005), I could restart the indexer, which was separated process then, but since sql server 2008, the fulltext logfile is writen by mssqlerver.exe itsself. Is it a missing feature, that this logfile cannot be cycled at all or are there other options?
Regards, Birgit Straker
Birgit Straker SCISYS Deutschland GmbH
Monday, April 9, 2012 8:43 PM
Hi, Amber,
It sounds like you are saying there is no way to cycle to full text log without restarting SQL server. The two stored procs you mention do not affect the SQLFT log files.
Is this correct? If so, this is very disappointing. Is there any plan to add a management proc like this for the full text service to Azure?
Thank you,
-Phil
Wednesday, February 20, 2013 2:09 PM | 1 vote
Bijal19: For what it's worth, that stored proc just saved me a bunch of time w/ cleanup.
I strongly suggest (for whoever may be watching) that they add some documentation for it, make it official and be done with it.
- Jesse