Full Text Index is not working as expected

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Full Text Index is not working as expected

Author

Topic

krishna.netblogs
Starting Member

7 Posts

Posted - 2012-08-12 : 12:07:50

Hi

I have a table having 220 lakshs of records and one of the coulmn is Full Text enabled.We have used ContainsTable() to search for data, but we are unable to get results as expected. so we done rebuild.During Index Rebuild, population is failed.I have found this error in error log and it is saying to do resume population.So I want to know howlong it takes to complete Resume population process.

Please look at the below more details about FT Index table.

Row count - 22155112

Index space - 1,903.250 MB (1.9 GB)

Data space - 87,552.258 MB (87 GB)

sqlserver2008 R2
and the below query we have used

SELECT Distinct top 50 cal.case_id,cal.cas_details
From g_case_action_log cal (READUNCOMMITTED)
inner join containstable(es.g_case_action_log, cas_details,
' ("235355" OR "<br>235355" OR "235355<br> ") ') as key_tbl on cal.log_id = key_tbl.[key]Where cal.product_id = 38810 ORDER By cal.case_id DESC

this query is not going to search in recent inserted/updated rows. this is the actual issue we are facing.

Can any one suggest me how to fix this error and if population need to be resume, then can you please let me know for how long takes to do resume population.

Regards

krishna

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-08-14 : 01:01:27

Dear Krishna,

You may try resume population by issue this command

---------------------
ALTER FULLTEXT INDEX ON dbo.<Table_Name> RESUME POPULATION
---------------------

And let see population status by this script.
---------------------
SELECT FULLTEXTCATALOGPROPERTY('TestCatalog' ,'ItemCount') AS ItemCount
,CASE FULLTEXTCATALOGPROPERTY('TestCatalog' ,'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'
END AS PopulateStatus
---------------------

Wait until status = idle,
and then rebuild FT index.

Second question, how long take to do resume population.
Answer is, you know number or records for each table.
Sum them and compare to ItemCount property in FULLTEXTCATALOGPROPERTY()
Wait for period of time and check value again, you will see number of ItemCount increase.
Now you have information to calculate estimated time to completed.

Good luck

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources