Full-text Index Question

Full-text Index Question

• Scythen

  See inline.

  EXEC dbo.sp_fulltext_table @tabname='[dbo].[Log]', @action='start_change_tracking'

  GO

  -- If you have timestamp column on the table, start_change_tracking will start incremental crawl, otherwise, it will kick off a fullcrawl and set changetracking to manual. You should see the index is populated.

  EXEC dbo.sp_fulltext_table @tabname='[dbo].[Log]', @action='start_background_updateindex'

  -- This step will kick off the auto crawl. You should see index size increase if you have inserted new records after the prev. step.

  GO

  
  
  

• sluggy

  Ok, so in order for the Full text index to automatically populate the new changes after the initial Full Population has completed, I need to have a Timestamp column defined in the table Otherwise, I will need to schedule a job to periodically do an incremental population. Is this correct

  Jason

  
  

• TheViewMaster

  This is strange. Can you use the statement from a 2000 client or sqlcmd/osql to see if it repros

  If it repros from sqlcmd, and If you don't mind, please show me your schema, db version and the sql statement. I will try to get a repro and see what is really happening.

  
  
  

• BrianMcCashin

  Ok, it appears to be working. I just went out to check the status of the index, and it was in the middle of "Processing notifications". The numbers don't seem to be changing though, but this could be due to the fact that the column with the index defined on it is storing web-related error messages, and I'm assuming if the error message is the same, the index doesn't increment the unique key count or item count. It apparently doesn't update the Last Population Date either unless one of those values change, which is a little misleading because it makes it seem like the Auto indexing feature isn't running. Maybe someone from Microsoft can address this if they're reading this thread.

  Jason

  
  

• boston123

  Here you go...

  EXEC dbo.sp_fulltext_catalog @ftcat=N'Log_Message', @action=N'create', @path=N'F:\FTData'

  GO

  
  

• imed-deborah

  Ok, that's what I thought too. However, I'm not seeing the index increase in size at all whenever I view the properties of the index, and I know it should increase because records are constantly being written to this table (no updates or deletes as of yet). Based on the code I posted, I have auto change tracking turned on so I should see the index growing, but the only time I see it grow is when I run an incremental population update, which right now is on-demand.

  Jason

  
  

• Fata1Attack

  Ok, that's what I thought too when I read about the topic in BOL. However, when I looked at the Properties of the index the day after I created it, the figures for Population Count, Index Size, etc. were the same from when I ran the first initial population. I then kicked off an incremental population and my figures increased obviously because new records are constantly being written to this particular table / column.

  So that led me to believe that the Auto Change Tracking feature wasn't working and I had to run incremental populations, but maybe I'm wrong and this is just the design of Full-text Indexes.

  Jason

  
  

• Tryin2Bgood

  You dont need to have an incremental population schedule. Auto change tracking should handle it.
  

• Paul Burg

  Sure, but can you share your SQL statements you have used to create the index etc. That would help in figuring things out.
  

• kefren

  I'm using SQL Server 2005 Management Studio to do everything; although, the database server that I'm doing the work against is SQL Server 2000 SP4.

  Jason

  
  

• Проничкин

  Sorry, here's the rest...

  USE [Logging]

  GO

  EXEC dbo.sp_fulltext_table @tabname='[dbo].[Log]', @action='create', @keyname='PK_Log', @ftcat=N'Log_Message'

  GO

  USE [Logging]

  GO

  EXEC dbo.sp_fulltext_column @tabname='[dbo].[Log]', @colname='Message', @action='add'

  GO

  USE [Logging]

  GO

  EXEC dbo.sp_fulltext_table @tabname='[dbo].[Log]', @action='activate'

  GO

  USE [Logging]

  GO

  EXEC dbo.sp_fulltext_table @tabname='[dbo].[Log]', @action='start_change_tracking'

  GO

  EXEC dbo.sp_fulltext_table @tabname='[dbo].[Log]', @action='start_background_updateindex'

  GO

  
  

• Zero_

  No, that is not true.

  You do not need a timestamp column to use auto change tracking. Only Incremental change tracking need timestamp column. If you are using incremental change tracking (timestamp based), you will need to periodically schedule population.

  If you are using auto change tracking, all you need to do is

  EXEC dbo.sp_fulltext_table @tabname='[dbo].[Log]', @action='start_background_updateindex'

  Auto change tracking should pick up all your new insert/del/updates. You do not need to schedule a job yourself.

  
  
  

• MrBrilliant

  What DDLs are you using to create index and run an incremental population
  
• Full-text Index Question