Is there a way to calculate the physical drive space required for a full-text search catalog? Space is cheap, but I'd like to know what I'm getting into.

I'm looking at a table of about 200 articles ranging in length. I'd like to index the title field, and the body of the article.

UPDATE: I'm somewhat looking to predict the future. For example, 200 articles, the title is varchar(500) and the article body is varchar(max). So, without creating the index, can an estimate be done on how large the full-text search catalog may be?

2 Answers 2


Here is some old code I wrote for SQL 2000. It still works on SQL 2005. You can use this to come up with a rough estimate of how much space you are using per document, then multiply that by your estimated number of documents.

   select 'CatalogName'   = left([name],30),
          'Status'        = case(FULLTEXTCATALOGPROPERTY ([name],'populatestatus'))
                              when 0 then 'Idle'
                              when 1 then 'Full population in progress'
                              when 2 then 'Paused'
                              when 3 then 'Throttled'
                              when 4 then 'Recovering'
                              when 5 then 'Shutdown'
                              when 6 then 'Incremental population in progress'
                              when 7 then 'Building index'
                              when 8 then 'Disk is full.  Paused.'
                              when 9 then 'Change tracking'
                              else 'Unknown'       
          'ItemCount'     = FULLTEXTCATALOGPROPERTY (name,'itemcount'),
          'IndexSize(MB)' = FULLTEXTCATALOGPROPERTY (name,'indexsize'),
          'UniqueWords'   = FULLTEXTCATALOGPROPERTY (name,'uniquekeycount'),
          'ErrorLogBytes' = FULLTEXTCATALOGPROPERTY (name,'logsize'),
          'Location'      = left(s.path,50) 
   from sysfulltextcatalogs s
  • That query works well to check the status of an existing catalog. I'm looking to determine how large an uncreated catalog will be, based on the data that will be thrown into it. Sorry for the confusion. I updated my question accordingly.
    – dangowans
    Aug 13, 2012 at 19:11
  • 2
    Can you create a database somewhere else and load a sample amount of data? I don't know of any other way to estimate. It really depends on the average size of your documents as well as the number of noise words you have defined.
    – datagod
    Aug 13, 2012 at 19:32
  • I do have an R&D server available for testing, so that may be the only way. Just wondering if a magic formula existed for calculating such a thing.
    – dangowans
    Aug 13, 2012 at 19:38

Found a link that helps with the estimate.


So, lets play.

Doing some digging into other article-like tables I have, I've found the longest article to be about 4000 characters in length. I'll assume all of the words are unique, and none of the words are noise. Lets say every word is 5 characters. So, there are approximately (4000 / 5) = 800 unique words or tokens.

The longest title, 70 characters. Using the same logic, (70 / 5) = 14 unique words or tokens.

The simplified index structure, as shown in the link, indicates that there are additional columns for tracking columns, documents, and occurrences, along with additional columns not shown. In this crazy example, lets say 10 integers, at 4 bytes a piece.


5 bytes per word + 40 bytes for index details = 45 bytes per record

45 bytes x (800 + 14) records = 36,630 bytes total per article

36,630 x 200 articles = 7,326,000 bytes

Which is approximately 7 MB.

These are all rough numbers, especially as I'm unclear on how many additional columns there are, how the compression works, etc. Even if I'm off by "a long shot", it's still likely less than 10 MB. I think I can spare that.

  • I wonder if there are some statistics out there, like the "average number of unique words in an English article" that would make this calculation more accurate.
    – dangowans
    Aug 14, 2012 at 11:30
  • Or the "average number of noise words in an English article". The final number can only get better.
    – dangowans
    Aug 14, 2012 at 11:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.