FullText catalogs generate, in SQL Log folder, files SQLFT*.LOG, one per each catalog, with rotation on 6 files (default value). The rotation happens on each SQL restart. If the SQL restart happens seldomly, the files can become extraordinary huge, e.g. 10-20 Gb or more.

The following stored procedure, compatible with SQL 2008 and newer, forces the log rotation. You can execute it before weekly reorganizing FT Catalogs.

You can expect to recover gigabytes of disk space. In a server farm you can expect 30-60 GB recovered space.

To force the SQLFT*.LOG rotation

Launch the following SP on the selected database, otherwise use USE <dbname> and GO commands:

DECLARE @iTable VARCHAR(255)

DECLARE @cmd NVARCHAR(500)        

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT name FROM sys.fulltext_catalogs'

 

-- create table cursor  

EXEC (@cmd)

 

OPEN TableCursor

 

FETCH NEXT

FROM TableCursor

INTO @iTable

 

WHILE @@FETCH_STATUS = 0

BEGIN

            

       SET @cmd = 'sp_fulltext_recycle_crawl_log ''' + @iTable + ''''

       PRINT @cmd

       EXEC (@cmd)

       FETCH NEXT

       FROM TableCursor

       INTO @iTable

END

CLOSE TableCursor

DEALLOCATE TableCursor

 

There are other two similar command, to execute each now and then.

To rotate ERRORLOG

EXEC sp_cycle_errorlog ;  

GO 

To rotate SQLAgent

EXEC msdb.dbo.sp_cycle_agent_errorlog;