special situation... full text search for anything?
-
I'd like to set up a "pulse check" for Full Text indexing.
I want to make sure that the most recent record in our full text index table is no more than 30 minutes old AND that FTIndexing has indexed it (we've got change tracking on). My noise.enu file is empty (single space), so there should be no ignored words.
The query I came up with is:
select top 1 @lastSendTime = SendTime
from tbEmailMessage
where contains (TextData, '"*"')
order by SendTime desc
if datepart(hh,getdate()) between 7 and 17
and datepart(dw,getdate()) not in (1,7)
and datediff(mi, @lastSendTime, getdate()) >= 30
begin
exec master..xp_sendmail
@recipients = 'greg.johnson@severnrivercapital.com',
@subject = 'Email Loader is suspect',
@message = 'It''s been over 30 minutes since the last FT indexed email was inserted into tbEmailMessage. Go check it out. '
return -1
end
else
return 0
go
However, the where clause returns the dreaded "search only contains ignored words" error. I've considered just putting in a popular letter instead of the *, but this isn't ideal. Any ideas?
-
This was removed by the editor as SPAM
-
Not exactly sure about your question, but :
SELECT fulltextcatalogproperty('Catalogue_name', 'PopulateCompletionAge')
Returns the difference in seconds between the completion of the last full-text index population and 01/01/1990 00:00:00.
This can be manipulted according to the requirement.
-
I appreciate the help Gagandeep, and that *would* be exactly what I need, but as I try to implement it, it doesn't seem to be doing what I'd like. Does that function work the same way when change tracking is enabled?
Right now, it's telling me that the last time the FTCatalog was populated was 7am this morning. Change tracking enabled, and we get over 5000 emails a day, so I KNOW the FTCatalog has been updated since then. Maybe my arithmatic is wrong? how does fulltextcatalogproperty go about finding this information?
select fulltextcatalogproperty('FTEmailMessage_2', 'PopulateCompletionAge')
-- 486026736
select dateadd(ss,486026736,'1990-01-01')(
-- 2005-05-27 07:25:36.000 (and it's now 2005-05-27 13:27:36.000)
???
-
Once change tracking is enabled, the system table sysfulltextnotify is updated every time any of the columns in the full-text index is modified. This table basically just keeps track of the table ID and the value of the unique index key for the rows that have been modified. The sysfulltextnotify table gets a simple entry only when a change is made to a full-text indexed column, and each row contains only three values: the ID of the table that was changed, the key value corresponding to the changed row, and a field indicating the operation that took place (insert, update, or delete).
SELECT dateadd(s, FULLTEXTCATALOGPROPERTY ('Northwind_data', 'PopulateCompletionAge' 
, '1/1/1990')The above is the right way. I would suppose Change tracking should not affect the above.
According to thread http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=156698
there is a registry key that keeps track of the info - but I am not certain. I will try to dig up more on that.
-
Question:
- Change tracking population
Maintains a record of the rows that have been modified in a system table, and propagates the changes to the full-text index. You start the change tracking by executing sp_fulltext_table and specify start_change_tracking for the @action parameter. When using change tracking, you also specify when the changes are taken from the history table and populated in the full-text index:
- Background
After starting change tracking with start_change_tracking, you can execute sp_fulltext_table specifying start_background_updateindex for the @action parameter. With this option, changes to rows in the table are propagated to the full-text index as they occur.
- On demand
In this option, all tracked changes are stored in the history, and only propagated to the full-text index when you execute sp_fulltext_table specifying update_index for the @action parameter.
- Scheduled
You can use SQL Agent to schedule periodic jobs that execute sp_fulltext_table specifying update_index for the @action parameter. This will propagate all outstanding tracked changes to the index.
Out of the above which one are you using?
- Change tracking population
-
Background of course!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply