| Author |
 Topic  |
|
|
krishna.netblogs
Starting Member
India
7 Posts |
 Posted - 08/12/2012 : 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
Thailand
60 Posts |
Posted - 08/14/2012 : 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. |
 |
|
| |
Topic |
|
|
|
Full Text Index is not working as expected
