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.
Arcane Code 