Tracking Disk Space with Full Text Index

Question

Hello all,

How to measure the additional disk space occupied because of implementing Full Text Index on a given table or view?

How is Full Text Index is related to log file?

Thanks

Samba

Answer 1

Hi Samba,

 

Assuming you’re referring to full-text indexes in SQL Server 2008 and above...

 

sys.fulltext_index_fragments provides a data size of the internal inverted index fragments. Full-text index is really a series of internal tables, usually most of the storage is in the fragments (inverted index). Although full-text indexing is not part of the same transaction as the base table, writes to the internal tables are logged like writes to any other tables.

 

Hope this helps, Bob

Answer 2

Hello Bob,

Thanks for your response.

If I use manual change tracking while creating the FTI on a table, where are the tracked changes (after initial full population and next manual populaton)   are stored?

I am observing significant growth of the transaction log file when I insert new records into a table with manual change tracking enabled?

Does it mean tracked changes are preserved in the transaction log file?

Thanks

Samba

Answer 3

Hi Samba,

 

I you use manual change tracking, the docid numbers of the content to be indexed are saved in internal tables. If you insert rows into the base table (the table that you are indexing), you’ll have much more transaction log activity on that table than on the internal tables, where the information kept is small. Only when you choose to update the full-text index (ALTER FULLTEXT INDEX...START UPDATE POPULATION) is the base table’s data read and parsed into the full-text index.

 

If you really think it is the full-text internal tables, you can run sp_spaceused on them (you can find them in sys.internal_tables).

 

Hope this helps,

Bob