SQL Server Full Text Search – The FullTextCatalogProperty Function

There is a useful function built into SQL Server for dealing with Full Text Search Catalogs: FullTextCatalogProperty . It takes two parameters, the first is the name of the catalog, the second is the name of the property you want. While there are quite a few properties in the list, most of them have been depreciated. However, there are still a handful that can provide valuable information.

For todays examples, I am using the AdventureWorks database. I’ve created a catalog named AdvWorksCatalog, using the following command:

create fulltext catalog AdvWorksCatalog as default;

I then created one index, using this command:

create fulltext index on Production.ProductDescription

  ([Description])

  key index PK_ProductDescription_ProductDescriptionID

  on AdvWorksCatalog

  with change_tracking auto

For more info on these commands, see my original series on Full Text Search which began in June of last year. (See the Arcane Lessons page for links to all the lessons).

The first property we will look at is IndexSize. You can call it up with the following command:

select FullTextCatalogProperty(‘AdvWorksCatalog’, ‘IndexSize’)

This will return the size, in megabytes (MB) of the indexes in the catalog. Since we only have one in this example, it is still quite tiny and thus will return a 0 for the result.

Next, let’s say you’d like to monitor the growth of the catalog by monitoring the number of items. To help with that, SQL Server provides a property named ItemCount.

select FullTextCatalogProperty(‘AdvWorksCatalog’, ‘ItemCount’)

In my case, it returned a value of 762 items in the catalog. An item is the same thing as a row in the source table. In my example, a select count from the Production.ProductDescription table also has a count of 762. These match since I’ve only created a full text index on one table in the database. In your system it will be the grand total number of rows for all the tables you have created indexes for.

A similar statistic is the UniqueKeyCount property, produced with this command:

select FullTextCatalogProperty(‘AdvWorksCatalog’, ‘UniqueKeyCount’)

This will tell you how many unique words were found in the catalog. In the Adventure Works database I’m using for SQL Server 2005, the count was 3,195. This would be like doing a select distinct word on the full text catalog. Each word may appear multiple times (once for each row it was found in) but will only be counted once in the UniqueKeyCount total.

The final property that is useful is PopulateStatus. This returns a value from 0 to 9 indicating what the full text engine is doing right now.

select FullTextCatalogProperty(‘AdvWorksCatalog’, ‘PopulateStatus’)

Since I didn’t want to constantly reference the on-line help to decipher the value, I added a little case statement to my SQL and will let it do the work:

select case FullTextCatalogProperty(‘AdvWorksCatalog’, ‘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 ‘Error reading FullTextCatalogProperty PopulateStatus’

        end

There, much more user friendly. I would envision this would be most useful when preparing to do upgrades to the server. You’d want to be sure all full text activity had ceased before doing a server reboot, or applying a lot of SQL Scripts that altered the full text index structures.

In all of these examples, be aware SQL Server will return a NULL if there is an error. For example, you pass in the name of a catalog that does not exist, or the property is incorrect.

About these ads

4 Responses to “SQL Server Full Text Search – The FullTextCatalogProperty Function”

  1. SQL Server Full Text Search – The ObjectPropertyEx Function « Arcane Code Says:

    [...] SQL Server Full Text Search – The FullTextCatalogProperty Function [...]

  2. Creating Custom Thesaurus Entries in SQL Server 2005 and 2008 Full Text Search « Arcane Code Says:

    [...] want to do this when the engine is busy or in use. I have a recent blog post that talks about the SQL Server Full Text Catalog Property function. In it, I have this handy query for you to be able to tell what the full text service is [...]

  3. SQL Server Full Text Searching at the Atlanta Code Camp « Arcane Code Says:

    [...] you hear me now? Checking to see if FTS is installed. Exploring SQL Servers FullTextCatalogProperty Function Using the ObjectPropertyEx Function Using FORMSOF in SQL Server Full Text Searching Creating Custom [...]

  4. Vinod kumar kushawaha Says:

    –1 Create the catalog (unless you already have)

    EXEC sp_fulltext_catalog ‘FTCatalog’,’create’

    –2 Add a full text index to a table

    EXEC sp_fulltext_table ‘Departments’, ‘create’, ‘FTCatalog’, ‘pk_departments’
    EXEC sp_fulltext_table ‘Employees’, ‘create’, ‘FTCatalog’, ‘pk_employees’

    –3 Add a column to the full text index

    EXEC sp_fulltext_column ‘Departments’, ‘ProductName’, ‘add’
    EXEC sp_fulltext_column ‘Employees’, ‘Description’, ‘add’

    –4 Activate the index

    EXEC sp_fulltext_table ‘Departments’,’activate’
    EXEC sp_fulltext_table ‘Employees’,’activate’

    –5 Start full population

    EXEC sp_fulltext_catalog ‘FTCatalog’, ‘start_full’


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: