How can I know when SQL Full Text Index Population is finished?

Question

We are writing unit tests for our ASP.NET application that run against a test SQL Server database. That is, the ClassInitialize method creates a new database with test data, and the ClassCleanup deletes the database. We do this by running .bat scripts from code.

The classes under test are given a connection string that connects to the unit test database rather than a production database.

Our problem is, that the database contains a full text index, which needs to be fully populated with the test data in order for our tests to run as expected.

As far as I can tell, the fulltext index is always populated in the background. I would like to be able to either:

1. Create the full text index, fully populated, with a synchronous (transact-SQL?) statement, or
2. Find out when the fulltext population is finished, is there a callback option, or can I ask repeatedly?

My current solution is to force a delay at the end the class initialize method - 5 seconds seems to work - because I can't find anything in the documentation.

Answer 1

I would like to offer an easier-to-read version of @Daniel Renshaw's answer:

DECLARE @CatalogName VARCHAR(MAX) SET     @CatalogName = 'FTS_Demo_Catalog'  SELECT     DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated     ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'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 Full.  Paused'         WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus 

Results:

LastPopulated           PopulateStatus ----------------------- ---------------------------------- 2012-05-08 14:51:37.000 Idle  (1 row(s) affected) 

Answer 2

You can query the status using FULLTEXTCATALOGPROPERTY (see here: http://technet.microsoft.com/en-us/library/ms190370.aspx).

For example:

SELECT     FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') AS [ItemCount],     FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus') AS [MergeStatus],     FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS [PopulateCompletionAge],     FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus') AS [PopulateStatus],     FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus') AS [ImportStatus] FROM sys.fulltext_catalogs AS cat 

You might also like to use SQL Profiler to monitor what commands SQL Server Management Studio issues when you bring up the properties dialog for the catalog. The dialog includes an indicatin of population status and all the information shown is queried using T-SQL.