5

I checked on a SQL Server 2012 instance recently only to see that the HDD is full and after investigating what happened it was Full Text Search informational lines filling the log files and disk space every minute. It added up to several GB of log information like the following line:

2015-01-26 14:08:58.40 spid42s     Informational: Resuming full-text population for table or indexed view '[db].[dbo].[table]' in database 'db' (table or indexed view ID '690817523', database ID '5'). Prior number of documents processed: 0, error encountered: 0.

My question is how to prevent SQL Server from writing any "informational" details to it's log files by full text search engine or alternatively (less preferred method) how to disable it's logging entirely?

too
  • 161
  • 1
  • 4

2 Answers2

4

If you can't restart the instance, you can schedule a log cleanup with sp_fulltext_recycle_crawl_log. More info: http://www.ruben-j.com/microsoft/t-sql/sql-fulltext-crawl-log/ or https://social.msdn.microsoft.com/Forums/windowsapps/en-US/b9420843-666e-4f33-b987-2624cdfa7acd/sql-server-2008r2-fulltext-logfile-sqlftlog-maintenance?forum=sqlkjmanageability or post the question to dba.stackexchange.

Razvan Zoitanu
  • 635
  • 1
  • 10
  • 26
  • 1
    I'v actually looked at these commands and even scheduling log cleanups won't prevent those log entries from cluttering the logs. I thought there might be an undocumented registry key for setting full text search debug level or enabling/disabling logging. – too May 11 '15 at 13:25
  • Don't think so. How would you be able to troubleshoot FTS without them ? – Razvan Zoitanu May 11 '15 at 14:52
  • If only problems are reported (no informational entries) troubleshooting is sill possible. – too May 19 '15 at 11:16
1

In the end the solution was to add to Task Scheduler a simple one-liner powershell script which will clear the SQL Server logs older than 30 days, executed every day:

Get-ChildItem "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\*" -Recurse | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-30)} | Remove-Item

Change MSSQL11.MSSQLSERVER to match your SQL Server version and instance name.

too
  • 161
  • 1
  • 4