March 23, 2018 at 10:46 am
I have a full-text index configured.
I am new with full text however I was getting great results and ranking. Now my results appear to have nothing to do with the search phrase.
Also the building of the index gets to be around 350mb or so then completely goes away (Keep hitting F5 in ssms on the query below). Then starts over 20mb, 110mb, and so on.. Building itself until it gets to a point and then drops and starts over.
HELP!!
Over past 20 minutes. Here is some of the rebuilding / Population start times
-- Start_Time Of builds for FT
-- 2018-03-23 12:50:06.870
-- 2018-03-23 12:53:26.227
-- 2018-03-23 12:56:18.567
-- 2018-03-23 12:58:34.423
Anyone have ideas on this please?
Here is the query I am running:
SELECT OBJECT_SCHEMA_NAME(table_id) + '.' + OBJECT_NAME(table_id) AS tableName, COUNT(*) AS num_fragments, SUM(row_count) AS row_count, CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS fulltext_size_mb
FROM sys.fulltext_index_fragments
GROUP BY table_id
ORDER BY fulltext_size_mb DESC
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 23, 2018 at 11:49 am
Jeffery Williams - Friday, March 23, 2018 10:46 AMI have a full-text index configured.I am new with full text however I was getting great results and ranking. Now my results appear to have nothing to do with the search phrase.
Also the building of the index gets to be around 350mb or so then completely goes away (Keep hitting F5 in ssms on the query below). Then starts over 20mb, 110mb, and so on.. Building itself until it gets to a point and then drops and starts over.
HELP!!
Anyone have ideas on this please?
Here is the query I am running:
SELECT OBJECT_SCHEMA_NAME(table_id) + '.' + OBJECT_NAME(table_id) AS tableName, COUNT(*) AS num_fragments, SUM(row_count) AS row_count, CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS fulltext_size_mb
FROM sys.fulltext_index_fragments
GROUP BY table_id
ORDER BY fulltext_size_mb DESC
I'd probably start by checking the full text log and see if it tells you anything. It should at least tell you the type of crawl and if you hit any errors. The full text logs are in the log directory for your instance. They are named like: SQLFT<nnnnnnn>.log
The archived logs have log.1, log.2, etc
Also, sys.fulltext_indexes would tell you the last crawl type, crawl completed, the start date/time, end date/time. That in combination with the log might give you a better idea on what's going on. It could be it's set to auto and doing update crawls. I don't know either way if the size changes like that when doing an update.
Sue
March 23, 2018 at 12:33 pm
Sue_H - Friday, March 23, 2018 11:49 AMJeffery Williams - Friday, March 23, 2018 10:46 AMI have a full-text index configured.I am new with full text however I was getting great results and ranking. Now my results appear to have nothing to do with the search phrase.
Also the building of the index gets to be around 350mb or so then completely goes away (Keep hitting F5 in ssms on the query below). Then starts over 20mb, 110mb, and so on.. Building itself until it gets to a point and then drops and starts over.
HELP!!
Anyone have ideas on this please?
Here is the query I am running:
SELECT OBJECT_SCHEMA_NAME(table_id) + '.' + OBJECT_NAME(table_id) AS tableName, COUNT(*) AS num_fragments, SUM(row_count) AS row_count, CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS fulltext_size_mb
FROM sys.fulltext_index_fragments
GROUP BY table_id
ORDER BY fulltext_size_mb DESCI'd probably start by checking the full text log and see if it tells you anything. It should at least tell you the type of crawl and if you hit any errors. The full text logs are in the log directory for your instance. They are named like: SQLFT<nnnnnnn>.log
The archived logs have log.1, log.2, etc
Also, sys.fulltext_indexes would tell you the last crawl type, crawl completed, the start date/time, end date/time. That in combination with the log might give you a better idea on what's going on. It could be it's set to auto and doing update crawls. I don't know either way if the size changes like that when doing an update.Sue
I can not find anything on a query to check for errors on the crawl.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 23, 2018 at 1:10 pm
Jeffery Williams - Friday, March 23, 2018 12:33 PMSue_H - Friday, March 23, 2018 11:49 AMJeffery Williams - Friday, March 23, 2018 10:46 AMI have a full-text index configured.I am new with full text however I was getting great results and ranking. Now my results appear to have nothing to do with the search phrase.
Also the building of the index gets to be around 350mb or so then completely goes away (Keep hitting F5 in ssms on the query below). Then starts over 20mb, 110mb, and so on.. Building itself until it gets to a point and then drops and starts over.
HELP!!
Anyone have ideas on this please?
Here is the query I am running:
SELECT OBJECT_SCHEMA_NAME(table_id) + '.' + OBJECT_NAME(table_id) AS tableName, COUNT(*) AS num_fragments, SUM(row_count) AS row_count, CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS fulltext_size_mb
FROM sys.fulltext_index_fragments
GROUP BY table_id
ORDER BY fulltext_size_mb DESCI'd probably start by checking the full text log and see if it tells you anything. It should at least tell you the type of crawl and if you hit any errors. The full text logs are in the log directory for your instance. They are named like: SQLFT<nnnnnnn>.log
The archived logs have log.1, log.2, etc
Also, sys.fulltext_indexes would tell you the last crawl type, crawl completed, the start date/time, end date/time. That in combination with the log might give you a better idea on what's going on. It could be it's set to auto and doing update crawls. I don't know either way if the size changes like that when doing an update.Sue
I can not find anything on a query to check for errors on the crawl.
If you have any errors, it would go in the log. Otherwise for a normal update with AUTO, it would just log something like:
<datetime> SPID Informational:Full-text Auto population initialized for table or indexed view '<table or view information'>
followed by
<datetime> SPID Informational: Full-text Auto population completed for table or indexed view '<table or view information'>
Those lines will also tell you the number of documents processed and the number that failed. So you'd look for failures there and see how many rows were processed.
The DMVs will have information on the last crawl. If you have no errors in the log and the change tracking is set for Auto and the sys.fulltext_indexes has it listed as completed, update_crawl for crawl type with a start and completion date/time then it very well could be nothing other than doing updates from changes to the table.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply