How to determine the size of a Full-Text Index on SQL Server 2008 R2?

Question

I have a SQL 2008 R2 database with some tables on it having some of those tables a Full-Text Index defined. I'd like to know how to determine the size of the index of a specific table, in order to control and predict it's growth.

Is there a way of doing this?

Answer 1

The catalog view sys.fulltext_index_fragments keeps track of the size of each fragment, regardless of catalog, so you can take the SUM this way. This assumes the limitation of one full-text index per table is going to remain the case. The following query will get you the size of each full-text index in the database, again regardless of catalog, but you could use the WHERE clause if you only care about a specific table.

SELECT 
   [table] = OBJECT_SCHEMA_NAME(table_id) + '.' + OBJECT_NAME(table_id), 
   size_in_KB = CONVERT(DECIMAL(12,2), SUM(data_size/1024.0))
 FROM sys.fulltext_index_fragments
 -- WHERE table_id = OBJECT_ID('dbo.specific_table_name')
 GROUP BY table_id;

Also note that if the count of fragments is high you might consider a reorganize.

Answer 2

If you are after a specific Catalogue Use SSMS - Clik on [Database] and expand the objects - Click on [Storage] - Right Click on {Specific Catalogue} - Choose Propertie and click. IN General TAB.. You will find the Catalogue Size = 'nn'