A simple question ...
As part of a database maintenance routine we occasionally completely delete and rebuild a Fulltext Index and its underlying Clustered index.
This works quite well, and there is no problem with it, apart from ONE thing: After we have re-created the Clustered index we execute a statement to re-create the fulltext index:
CREATE FULLTEXT INDEX ON [dbo].[<ourtablename>] (<thefieldswewanttoindex>) KEY INDEX [<theclusteredindex>] ON [<thefulltextcatalog>] WITH CHANGE_TRACKING AUTO
This, again, works perfectly fine, and it takes a number of hours to complete, which is also perfectly fine as this is done during down time and it affects no users. But there is ONE small thing that annoys me. While the fulltext index is repopulating, i.e:
SELECT FULLTEXTCATALOGPROPERTY('<thefulltextcatalog>', 'Populatestatus')
returns 1, the LOG file keeps growing and growing, up to 110GB. After that we just truncate it and the users carry on the next day.
So the question is: would it be OK to occasionally truncate the LOG file during the hours while the Populatestatus returns 1 so that we keep the LOG file size to a manageable level?
It is perfectly fine to do this, however this may not release the log used for rebuilding the index. In other words, truncating the log may not reduce its size until the index is populated.
Deleting such huge amount of data and rebuilding it will always consume IO and log resources. If you try yo avoid deletion/repopulation of your clustered table, this will significantly reduce the log growth. In this case you will not need to recreate the full text index too.