How do I know when an SQL Full Text Index Index is populated?

Question

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

In the tested classes, a connection string is specified that connects to the unit test database, and not to the production database.

Our problem is that the database contains a full text index, which must be completely populated with test data so that our tests run as expected.

As far as I can tell, a full-text index is always populated in the background. I would like to be able to:

• Create a full text index, fully populated, using a synchronous (transact-SQL?) Statement or
• Find out when the full-text population is over, is there a callback option, or can I ask several times?

My current solution is to delay the class initialization method at the end - it seems like 5 seconds - because I cannot find anything in the documentation.

Answer 1

You can request a 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 may also need to use SQL Profiler to control which SQL Server Management Studio commands appear when the directory properties dialog box appears. The dialog box includes an indication of population status, and all displayed information is requested using T-SQL.

Answer 2

I would suggest 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 FROM sys.fulltext_catalogs AS cat 

Results:

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

Answer 3

This is the stored procedure that we created based on GarethOwen's answer. It takes a comma-separated list of tables as parameters and waits until the full text indexes on all of them have been updated. It checks every tenth of a second to prevent disk chopping and downtime after 10 seconds, just in case everything works slowly / broken. Useful if your FT searches relate to multiple indexes.

Called as follows:

 EXECUTE [dbo].[WaitForFullTextIndexing] 'MY_TABLE,ALTERNATE_NAMES,TAG_GROUP_VALUES,TAG_GROUPS,FIELD_OPTION'; 

Source:

 CREATE PROCEDURE WaitForFullTextIndexing @TablesStr varchar(max) AS BEGIN DECLARE @Tables AS TABLE( [word] [varchar](8000) NULL) INSERT INTO @Tables (word) SELECT items from dbo.Split(@TablesStr, ','); DECLARE @NumberOfTables int; SELECT @NumberOfTables = COUNT(*) from @Tables; DECLARE @readyCount int; SET @readyCount = 0; DECLARE @waitLoops int; SET @waitLoops = 0; DECLARE @result bit; WHILE @readyCount <> @NumberOfTables AND @waitLoops < 100 BEGIN select @readyCount = COUNT(*) from @Tables tabs where OBJECTPROPERTY(object_id(tabs.word), 'TableFulltextPopulateStatus') = 0; IF @readyCount <> @NumberOfTables BEGIN -- prevent thrashing WAITFOR DELAY '00:00:00.1'; END set @waitLoops = @waitLoops + 1; END END GO 

dbo.split is a table value function that everyone should have by now, which splits the row on the delimiter into a temporary table:

 CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return end GO 

Answer 4

Thanks Daniel, your answer got me on the right track.

I actually use the following T-SQL statement to find out if the population status of the full text index is Idle:

 SELECT OBJECTPROPERTY(object_id('v_doc_desc_de'), 'TableFulltextPopulateStatus') 

'v_doc_desc_de' is the name of the database view we are indexing.

If the population status is not idle, I wait a couple of seconds and ask again until Idle. It is important to wait a short time between checks to ensure that the full text population does not slow down by constantly checking the status of the population.

The MSDN documentation states that the OBJECTPROPERTYEX (table-level) function is recommended for the FULLTEXTCATALOGPROPERTY with the 'PopulateStatus "property. It states the following:

The following properties will be removed in a future version of SQL Server: LogSize and PopulateStatus. Avoid using these features in new developments and plan to modify applications that currently use any of them.

Answer 5

To wait for the full text directory to complete filling out all its tables and views without specifying their names, you can use the following stored procedure. This is a combination of JohnB's answer to this question and cezarm's answer to the question:

 CREATE PROCEDURE WaitForFullTextIndexing @CatalogName VARCHAR(MAX) AS BEGIN DECLARE @status int; SET @status = 1; DECLARE @waitLoops int; SET @waitLoops = 0; WHILE @status > 0 AND @waitLoops < 100 BEGIN SELECT @status = FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus') FROM sys.fulltext_catalogs AS cat; IF @status > 0 BEGIN -- prevent thrashing WAITFOR DELAY '00:00:00.1'; END SET @waitLoops = @waitLoops + 1; END END