0

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?

1 Answers1

0

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.

Andrey Nikolov
  • 12,967
  • 3
  • 20
  • 32
  • Cheers. That might explain some other interesting behaviour I observed in a test scenario. After I deleted the fulltext index and its underlying clustered index I ran a script that creates the clustered index, truncates the Log file and then creates the fulltext index. This produced a log file of 111GB after the Population completed. Then I repeated the same test. This time I first created the Clustered index and truncated the Log file and then I waited. During this I observed that the Log file was increasing in size again. Not a huge amount but still... it increased. I waited ... –  Nov 04 '18 at 11:35
  • ... until I was sure that it was not increasing any further, and I truncated it again. THEN I created the Fulltext index. This morning I looked again and found that the Population had completed, but this time the Log file was less than 2 GB. - by the way ... it had created about 90GB of Log file while creating the Clustered index. Very interesting :) –  Nov 04 '18 at 11:37
  • And yes, I think you're quite right. From what I've seen so far it seems that the best policy is to not do this regularly. Maybe once or twice a year, and PLAN for it when it's done - make sure the server has plenty of free space for the (temporary) log file and so on. Thanks for the help! –  Nov 04 '18 at 11:39