A script to get properties of full text catalog?

Hey, all--

My organization is having a problem with MS-SQL's sporadically stopping full-text catalog population on one of our databases. New content is added to that database constantly, so we have an incremental population schedule set up to update the index once an hour. This works fine for a while and then stops for no apparent reason; we don't notice it having stopped until someone comes along to tell us they can't find something.

We've been trying different things to fix it, but in the meanwhile, is there a way to expose the properties (particularly the Last Population Date) of a full-text catalog to an outside script, so that we could incorporate a check of the catalog status into our daily server health scripts? Or, alternatively, a way to have the database server send out an email when the last incremental update is more than an hour old? It's no good to have our users being the ones to tell us when the indexing has failed.

Thanks much.Hello,

Try

use DatabaseName
select FulltextCatalogProperty(N'CatalogName', N'PopulateCompletionAge')

Its in seconds since 12:00:00 A.M., January 1, 1990.

I'll get you more info after i walk downstairs and get some starbucks :)|||Oh, that's perfect. Thanks so much.

For the reference of future readers of this topic, that first param is actually the catalog name, not the database name. Full Microsoft documentation is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7x15.asp . It's amazing what you can find when you know what you're looking for... thanks again!|||Select dateadd(s,FulltextCatalogProperty(N'CatalogName', N'PopulateCompletionAge'),'1/1/1990')

Alot of times, I'll do a incremental right after the insert. That will only be helpful in certain situations.

Thanks for the note on the Catalog name. It looked fishy but I didnt look into it. I guess my catalog name is the same as my database name ;)