2

In order to speed up a field's datatype change, I:

  1. completely dropped the Full Text Catalog and a few NC indexes.
  2. changed the datatype (from VARCHAR to NVARCHAR).
  3. recreated the NC Indexes.
  4. recreated the FTC.

Before this change the NDF was 100gb and 6gb free space. After the change the NDF was still 100gb from with 99gb free space (and I looked at the FTI status and it was idle and fully populated).

Why would recreating the FTI make the overall size so much smaller? There were a ton of tables in the FTC that I didn't add back in there, but those tables didn't have any columns checked. The FTI only indexes the tables were there are actual columns checked, right?

LowlyDBA - John M
  • 10,922
  • 11
  • 42
  • 62
Chris Woods
  • 1,771
  • 1
  • 16
  • 22
  • Full-text indexes can become very fragmented if there are frequent updates. If that happens, recreating the full-text index can significantly reduce the size. Maybe that's what happened here? This question and answer has more detail: https://dba.stackexchange.com/questions/107808 – Geoff Patterson Jan 17 '18 at 18:50
  • I don't think its very fragmented, though. When I run the following code I get 20 fragments for table1, 4 fragments for table2, 5 fragments for table3 and 2 fragments for table4. – Chris Woods Jan 17 '18 at 21:07
  • It might be that fragmentation does not explain your situation, but I think what would have mattered would be the difference in fragmentation between before you recreated the full-text indexes (which you might not have a measurement of) and now. We've always observed that full-text index sizes grow fairly quickly (and full-text query performance degrades) as underlying tables are updated, but that much of this space can be recovered (and more consistent full-text query performance maintained) by periodically recreating the indexes. – Geoff Patterson Jan 18 '18 at 13:54

0 Answers0