Hi,
I am confused by the different method of population, of both Full Text
index and Full Text Catalog.
Currently, we are running a application (3rd party) on SQL 2000, and we
have set the Full Text indexing to be "Change Tracking" and "background
update". By having this combination, do we still need to instruct the SQL
Server to do an incremental update on both the Catalog and Indexes? If yes,
which method should I use?
TTS,
The short answer is no. You do not need to run a scheduled, i.e.. instruct
the SQL Server to do an incremental population. In the background, SQL
Server uses an internal (and un-documented) system table to gather changes
based upon the database logged changes (updates, inserts & deletes) for the
FT-enable table column changes and then pushes these changes to the FT
Catalog in near real-time performance.
However and depending upon the amount (number of rows) and the frequency
(once every minute, once ever hour, etc.) you may want to turn off "Update
Index in Background" if the amount and frequency of changes is very high,
say >50% of the FT-enable table would change. In this case, you would
disenable "Update Index in Background" and setup a scheduled Incremental
Population to handle this one time massive update. You can read more about
this in the SQL Server 2000 BOL title "Maintaining Full-Text Indexes" -
specifically, "The method you use depends on factors such as the CPU and
available memory, the amount and rate of change of data, the amount of
available disk space, and the importance of the full-text index being
current. Use these recommendations as a guide for selecting a maintenance
method".
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"tts" <tts@.discussions.microsoft.com> wrote in message
news:C120738B-E239-46F5-BF5D-CFA3F908AFA6@.microsoft.com...
> Hi,
> I am confused by the different method of population, of both Full Text
> index and Full Text Catalog.
> Currently, we are running a application (3rd party) on SQL 2000, and
we
> have set the Full Text indexing to be "Change Tracking" and "background
> update". By having this combination, do we still need to instruct the SQL
> Server to do an incremental update on both the Catalog and Indexes? If
yes,
> which method should I use?
>
|||Thanks John.
So to conclude, even though the status of the Full Text Catelog is
"IDLE", the Full Text index and Full Text Catelog will be populated with the
"Change Tracking" and "background update" configuration. And we will not have
any ways of telling whether the Full Text index and Catelog has been
populated, we can only trust that it has been populated.
"John Kane" wrote:
> TTS,
> The short answer is no. You do not need to run a scheduled, i.e.. instruct
> the SQL Server to do an incremental population. In the background, SQL
> Server uses an internal (and un-documented) system table to gather changes
> based upon the database logged changes (updates, inserts & deletes) for the
> FT-enable table column changes and then pushes these changes to the FT
> Catalog in near real-time performance.
> However and depending upon the amount (number of rows) and the frequency
> (once every minute, once ever hour, etc.) you may want to turn off "Update
> Index in Background" if the amount and frequency of changes is very high,
> say >50% of the FT-enable table would change. In this case, you would
> disenable "Update Index in Background" and setup a scheduled Incremental
> Population to handle this one time massive update. You can read more about
> this in the SQL Server 2000 BOL title "Maintaining Full-Text Indexes" -
> specifically, "The method you use depends on factors such as the CPU and
> available memory, the amount and rate of change of data, the amount of
> available disk space, and the importance of the full-text index being
> current. Use these recommendations as a guide for selecting a maintenance
> method".
> Regards,
> John
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "tts" <tts@.discussions.microsoft.com> wrote in message
> news:C120738B-E239-46F5-BF5D-CFA3F908AFA6@.microsoft.com...
> we
> yes,
>
>
|||Hi,
Can anybody confirm my conclusion?
"tts" wrote:
[vbcol=seagreen]
> Thanks John.
> So to conclude, even though the status of the Full Text Catelog is
> "IDLE", the Full Text index and Full Text Catelog will be populated with the
> "Change Tracking" and "background update" configuration. And we will not have
> any ways of telling whether the Full Text index and Catelog has been
> populated, we can only trust that it has been populated.
> "John Kane" wrote:
|||Hi TTS,
Sorry for the delay in replying! No, and depending upon how much content you
changed, you would see the following status values as well (from SQL 2000
BOL titles "sp_help_fulltext_catalogs" and FULLTEXTCATALOGPROPERTY)
Property Description
PopulateStatus 0 = Idle
1 = Full population in progress
2 = Paused
3 = Throttled
4 = Recovering
5 = Shutdown
6 = Incremental population in progress
7 = Building index
8 = Disk is full. Paused.
9 = Change tracking
For CT with UIiB and depending upon what was occurring, you could see
PopulateStatus values of 1, 6, 7 and 9. You can also use the
FULLTEXTCATALOGPROPERTY metadata query to get these values as well, for
example:
select
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
Hope that help!
John
"tts" <tts@.discussions.microsoft.com> wrote in message
news:F8A58279-7077-4CFC-9F3F-57F60787F615@.microsoft.com...[vbcol=seagreen]
> Hi,
> Can anybody confirm my conclusion?
> "tts" wrote:
the[vbcol=seagreen]
have[vbcol=seagreen]
instruct[vbcol=seagreen]
changes[vbcol=seagreen]
for the[vbcol=seagreen]
frequency[vbcol=seagreen]
"Update[vbcol=seagreen]
high,[vbcol=seagreen]
Incremental[vbcol=seagreen]
about[vbcol=seagreen]
Indexes" -[vbcol=seagreen]
and[vbcol=seagreen]
maintenance[vbcol=seagreen]
Full Text[vbcol=seagreen]
and[vbcol=seagreen]
"background[vbcol=seagreen]
the SQL[vbcol=seagreen]
If[vbcol=seagreen]
|||Hello John,
Thanks for your explanation. I have encountered a strange situation.
The index is not populated everytime with the insertion of record.
Please correct me if I am wrong. From what I understand, if I am
using Change Tracking and Background update, the index will be updated almost
immediately, if not, when the CPU is free to do the processing. However, this
do not apply all the time. I have tested out quite a number of time, not all
the insertion will cause the population of the indexes. From what I have
tested, if the keyword has been inserted into index, then the index will be
updated. Otherwise, the keyword will not be inserted into the index. Please
advise me what I should lookup for in the population and using of Full Text
Search.
Although I have done quite a number of testing, the size and
datetime stamp of the Full Text Catalog remain the same (as of last Friday),
and the status is IDLE. So I am not sure whether the population did take
place or not.
"John Kane" wrote:
> Hi TTS,
> Sorry for the delay in replying! No, and depending upon how much content you
> changed, you would see the following status values as well (from SQL 2000
> BOL titles "sp_help_fulltext_catalogs" and FULLTEXTCATALOGPROPERTY)
> Property Description
> PopulateStatus 0 = Idle
> 1 = Full population in progress
> 2 = Paused
> 3 = Throttled
> 4 = Recovering
> 5 = Shutdown
> 6 = Incremental population in progress
> 7 = Building index
> 8 = Disk is full. Paused.
> 9 = Change tracking
>
> For CT with UIiB and depending upon what was occurring, you could see
> PopulateStatus values of 1, 6, 7 and 9. You can also use the
> FULLTEXTCATALOGPROPERTY metadata query to get these values as well, for
> example:
> select
> objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
> Hope that help!
> John
>
>
> "tts" <tts@.discussions.microsoft.com> wrote in message
> news:F8A58279-7077-4CFC-9F3F-57F60787F615@.microsoft.com...
> the
> have
> instruct
> changes
> for the
> frequency
> "Update
> high,
> Incremental
> about
> Indexes" -
> and
> maintenance
> Full Text
> and
> "background
> the SQL
> If
>
>
|||You're welcome, TTS,
Is it not populating every time or just some of the time? If it is the
former, then this is not only possible, but can also occur when CT & UIiB is
not enabled. The usual symptom is that the FT Catalog property indicates
that the FT Catalog only contains an item count of 1 (row count + 1) row and
a unique key count (unique non-noise word count) of 0.
If this is the case, the most likely the FT Catalog is not populating for
several reasons, but the most likely is that the MSSQLServer service account
&/or password has been changed by Win2K's Component services (or control
panel) and not via the Enterprise Manager's server security tab as this is
the only place that the security of the two services (MSSQLServer &
MSSearch) are sync'ed up.
Also, could you confirm that your server's "Microsoft Search" (mssearch.exe)
service is started & under the "system account" (LocalSystem) and not some
other account? If it is not started under the LocalSystem, please change it
to LocalSystem and stop & restart the service. See the following KB article
for more details:
277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component
Services] at http://support.microsoft.com/default...B;EN-US;277549
Yes, your assumption is correct, but the actual *near* real-time update can
sometimes take up to 5 seconds before you can issue a SQL FTS query and see
the inserted row. This can vary to as much as 7 to 10 seconds, depending
upon the amount & frequency of updates.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"tts" <tts@.discussions.microsoft.com> wrote in message
news:449F08E2-2246-4393-B383-CE8779E80933@.microsoft.com...
> Hello John,
> Thanks for your explanation. I have encountered a strange
situation.
> The index is not populated everytime with the insertion of record.
> Please correct me if I am wrong. From what I understand, if I am
> using Change Tracking and Background update, the index will be updated
almost
> immediately, if not, when the CPU is free to do the processing. However,
this
> do not apply all the time. I have tested out quite a number of time, not
all
> the insertion will cause the population of the indexes. From what I have
> tested, if the keyword has been inserted into index, then the index will
be
> updated. Otherwise, the keyword will not be inserted into the index.
Please
> advise me what I should lookup for in the population and using of Full
Text
> Search.
> Although I have done quite a number of testing, the size and
> datetime stamp of the Full Text Catalog remain the same (as of last
Friday),[vbcol=seagreen]
> and the status is IDLE. So I am not sure whether the population did take
> place or not.
> "John Kane" wrote:
you[vbcol=seagreen]
2000[vbcol=seagreen]
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')[vbcol=seagreen]
is[vbcol=seagreen]
with[vbcol=seagreen]
not[vbcol=seagreen]
SQL[vbcol=seagreen]
deletes)[vbcol=seagreen]
the FT[vbcol=seagreen]
very[vbcol=seagreen]
would[vbcol=seagreen]
more[vbcol=seagreen]
CPU[vbcol=seagreen]
amount of[vbcol=seagreen]
being[vbcol=seagreen]
2000,[vbcol=seagreen]
instruct[vbcol=seagreen]
Indexes?[vbcol=seagreen]
|||Hello John,
Sorry, did not get back to you regarding this issue. The problem that
I have raised is because of the software bug, so the indexes are not
populated correctly.
However, this lead me to another few questions.
1) If a table is Change Tracking enabled, when we specify a "On
demand" or "Schedule" population, is it performing a Full Population? The
table does not have any date-time stamp.
2) If a full population is taking place, and there are records going
into the Indexed table, how will the FTS behave? Does that means that the FTS
will redo the full population of the index again, or it will just include the
new text, and perform a incremental population?
I am trying to determine which type of population method has the best
perfomance since there is a slight flaw in the software.
"John Kane" wrote:
> You're welcome, TTS,
> Is it not populating every time or just some of the time? If it is the
> former, then this is not only possible, but can also occur when CT & UIiB is
> not enabled. The usual symptom is that the FT Catalog property indicates
> that the FT Catalog only contains an item count of 1 (row count + 1) row and
> a unique key count (unique non-noise word count) of 0.
> If this is the case, the most likely the FT Catalog is not populating for
> several reasons, but the most likely is that the MSSQLServer service account
> &/or password has been changed by Win2K's Component services (or control
> panel) and not via the Enterprise Manager's server security tab as this is
> the only place that the security of the two services (MSSQLServer &
> MSSearch) are sync'ed up.
> Also, could you confirm that your server's "Microsoft Search" (mssearch.exe)
> service is started & under the "system account" (LocalSystem) and not some
> other account? If it is not started under the LocalSystem, please change it
> to LocalSystem and stop & restart the service. See the following KB article
> for more details:
> 277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
> MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component
> Services] at http://support.microsoft.com/default...B;EN-US;277549
> Yes, your assumption is correct, but the actual *near* real-time update can
> sometimes take up to 5 seconds before you can issue a SQL FTS query and see
> the inserted row. This can vary to as much as 7 to 10 seconds, depending
> upon the amount & frequency of updates.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "tts" <tts@.discussions.microsoft.com> wrote in message
> news:449F08E2-2246-4393-B383-CE8779E80933@.microsoft.com...
> situation.
> almost
> this
> all
> be
> Please
> Text
> Friday),
> you
> 2000
> objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
> is
> with
> not
> SQL
> deletes)
> the FT
> very
> would
> more
> CPU
> amount of
> being
> 2000,
> instruct
> Indexes?
>
>
|||Not to worry, TTS, as Christmas was this past weekend!
1) If a table is Change Tracking enabled, when we specify a "On demand" or
"Schedule" population, is it performing a Full Population? The table does
not have any date-time stamp.
A. Yes. Because the table does not have a timestamp column, a Full
Population is always done, even if you select an Incremental Population.
2) If a full population is taking place, and there are records going into
the Indexed table, how will the FTS behave?
A. An interesting question... I've not actually tested this, as a Full
Population removes all previous data in the FT Catalog (much like a Truncate
table in T-SQL does to a sql table's data), and then starts reading from the
SQL Table's data from the beginning. I do *suspect* that the new data will
be read from the database log and populated to the FT Catalog after Full
Population is completed, but I think this may only be true if a timestamp
column was present. You should test this yourself before making any
decisions.
Hope this helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"tts" <tts@.discussions.microsoft.com> wrote in message
news:5B0EC1EA-0DA4-4A5E-B3DC-52A3F8C6C5C0@.microsoft.com...
> Hello John,
> Sorry, did not get back to you regarding this issue. The problem
that
> I have raised is because of the software bug, so the indexes are not
> populated correctly.
> However, this lead me to another few questions.
> 1) If a table is Change Tracking enabled, when we specify a "On
> demand" or "Schedule" population, is it performing a Full Population? The
> table does not have any date-time stamp.
> 2) If a full population is taking place, and there are records going
> into the Indexed table, how will the FTS behave? Does that means that the
FTS
> will redo the full population of the index again, or it will just include
the
> new text, and perform a incremental population?
> I am trying to determine which type of population method has the
best[vbcol=seagreen]
> perfomance since there is a slight flaw in the software.
> "John Kane" wrote:
UIiB is[vbcol=seagreen]
and[vbcol=seagreen]
for[vbcol=seagreen]
account[vbcol=seagreen]
is[vbcol=seagreen]
(mssearch.exe)[vbcol=seagreen]
some[vbcol=seagreen]
it[vbcol=seagreen]
article[vbcol=seagreen]
Component[vbcol=seagreen]
http://support.microsoft.com/default...B;EN-US;277549[vbcol=seagreen]
can[vbcol=seagreen]
see[vbcol=seagreen]
am[vbcol=seagreen]
However,[vbcol=seagreen]
not[vbcol=seagreen]
have[vbcol=seagreen]
will[vbcol=seagreen]
take[vbcol=seagreen]
content[vbcol=seagreen]
see[vbcol=seagreen]
for[vbcol=seagreen]
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')[vbcol=seagreen]
Catelog[vbcol=seagreen]
populated[vbcol=seagreen]
will[vbcol=seagreen]
been[vbcol=seagreen]
i.e..[vbcol=seagreen]
background,[vbcol=seagreen]
gather[vbcol=seagreen]
to[vbcol=seagreen]
off[vbcol=seagreen]
you[vbcol=seagreen]
read[vbcol=seagreen]
the[vbcol=seagreen]
index[vbcol=seagreen]
both[vbcol=seagreen]
SQL[vbcol=seagreen]
2012年3月26日星期一
Full Text Search know which row has error in indexing
Hello everybody,
I have installed SQLServer 2005 for take advantage of full text seach
features.
When I start full population, there are some rows that cause error and not
are indexed.
I see the log in C:\Programmi\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\SQLFT0000700005.LOG. In this file there are seven
rows that have error and for everyone a line that say:
"Error '0x8004fd01: The IFilter::Init() function call failed.' ...
full-text key value 0x495532303045594154504B55. Attempt will be made to
reindex it. The component 'pdffilt.dll' reported error while indexing.
Component path 'C:\Programmi\Foxit Software\Foxit PDF IFilter\pdffilt.dll'."
With different full-text key value for every line.
My question is:
1) Start from a full-text key (ie. 0x495532303045594154504B55), how I arrive
to the record in the source table for understand which is the
problem?Otherwise how can I examine the problem if I don't know the row that
cause the error?
2) What is the error 0x8004fd01?
3) Could be that the PDF filter of Foxit is better than Adobe? With Adobe
filter (version 6) my full pupulation is endless!
Thanks!
OK resolved !!!
1)The hexadecimal value must to be converted to decimal and then to ASCII
(http://www.vortex.prodigynet.co.uk/misc/ascii_conv.html)
2) Probably due to the incorrect format because changing the type column
value from ".pdf" to ".txt" the problem has been resolved!!!
3) Wait your opinions :-)
NicolaZap
"NicolaZap" wrote:
> Hello everybody,
> I have installed SQLServer 2005 for take advantage of full text seach
> features.
> When I start full population, there are some rows that cause error and not
> are indexed.
> I see the log in C:\Programmi\Microsoft SQL
> Server\MSSQL.1\MSSQL\LOG\SQLFT0000700005.LOG. In this file there are seven
> rows that have error and for everyone a line that say:
> "Error '0x8004fd01: The IFilter::Init() function call failed.' ...
> full-text key value 0x495532303045594154504B55. Attempt will be made to
> reindex it. The component 'pdffilt.dll' reported error while indexing.
> Component path 'C:\Programmi\Foxit Software\Foxit PDF IFilter\pdffilt.dll'."
> With different full-text key value for every line.
>
> My question is:
> 1) Start from a full-text key (ie. 0x495532303045594154504B55), how I arrive
> to the record in the source table for understand which is the
> problem?Otherwise how can I examine the problem if I don't know the row that
> cause the error?
> 2) What is the error 0x8004fd01?
> 3) Could be that the PDF filter of Foxit is better than Adobe? With Adobe
> filter (version 6) my full pupulation is endless!
>
> Thanks!
>
|||Is this the 64 bit version of SQL server? If so you should be using Foxit.
"NicolaZap" <NicolaZap@.discussions.microsoft.com> wrote in message
news:091F34B8-1140-4A74-9D2A-03F02B00DB8A@.microsoft.com...[vbcol=seagreen]
> OK resolved !!!
> 1)The hexadecimal value must to be converted to decimal and then to ASCII
> (http://www.vortex.prodigynet.co.uk/misc/ascii_conv.html)
> 2) Probably due to the incorrect format because changing the type column
> value from ".pdf" to ".txt" the problem has been resolved!!!
> 3) Wait your opinions :-)
>
> NicolaZap
>
> "NicolaZap" wrote:
|||NO, it is the 32 bit versione of SQLServer2005.
"Hilary Cotter" wrote:
> Is this the 64 bit version of SQL server? If so you should be using Foxit.
> "NicolaZap" <NicolaZap@.discussions.microsoft.com> wrote in message
> news:091F34B8-1140-4A74-9D2A-03F02B00DB8A@.microsoft.com...
>
>
I have installed SQLServer 2005 for take advantage of full text seach
features.
When I start full population, there are some rows that cause error and not
are indexed.
I see the log in C:\Programmi\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\SQLFT0000700005.LOG. In this file there are seven
rows that have error and for everyone a line that say:
"Error '0x8004fd01: The IFilter::Init() function call failed.' ...
full-text key value 0x495532303045594154504B55. Attempt will be made to
reindex it. The component 'pdffilt.dll' reported error while indexing.
Component path 'C:\Programmi\Foxit Software\Foxit PDF IFilter\pdffilt.dll'."
With different full-text key value for every line.
My question is:
1) Start from a full-text key (ie. 0x495532303045594154504B55), how I arrive
to the record in the source table for understand which is the
problem?Otherwise how can I examine the problem if I don't know the row that
cause the error?
2) What is the error 0x8004fd01?
3) Could be that the PDF filter of Foxit is better than Adobe? With Adobe
filter (version 6) my full pupulation is endless!
Thanks!
OK resolved !!!
1)The hexadecimal value must to be converted to decimal and then to ASCII
(http://www.vortex.prodigynet.co.uk/misc/ascii_conv.html)
2) Probably due to the incorrect format because changing the type column
value from ".pdf" to ".txt" the problem has been resolved!!!
3) Wait your opinions :-)
NicolaZap
"NicolaZap" wrote:
> Hello everybody,
> I have installed SQLServer 2005 for take advantage of full text seach
> features.
> When I start full population, there are some rows that cause error and not
> are indexed.
> I see the log in C:\Programmi\Microsoft SQL
> Server\MSSQL.1\MSSQL\LOG\SQLFT0000700005.LOG. In this file there are seven
> rows that have error and for everyone a line that say:
> "Error '0x8004fd01: The IFilter::Init() function call failed.' ...
> full-text key value 0x495532303045594154504B55. Attempt will be made to
> reindex it. The component 'pdffilt.dll' reported error while indexing.
> Component path 'C:\Programmi\Foxit Software\Foxit PDF IFilter\pdffilt.dll'."
> With different full-text key value for every line.
>
> My question is:
> 1) Start from a full-text key (ie. 0x495532303045594154504B55), how I arrive
> to the record in the source table for understand which is the
> problem?Otherwise how can I examine the problem if I don't know the row that
> cause the error?
> 2) What is the error 0x8004fd01?
> 3) Could be that the PDF filter of Foxit is better than Adobe? With Adobe
> filter (version 6) my full pupulation is endless!
>
> Thanks!
>
|||Is this the 64 bit version of SQL server? If so you should be using Foxit.
"NicolaZap" <NicolaZap@.discussions.microsoft.com> wrote in message
news:091F34B8-1140-4A74-9D2A-03F02B00DB8A@.microsoft.com...[vbcol=seagreen]
> OK resolved !!!
> 1)The hexadecimal value must to be converted to decimal and then to ASCII
> (http://www.vortex.prodigynet.co.uk/misc/ascii_conv.html)
> 2) Probably due to the incorrect format because changing the type column
> value from ".pdf" to ".txt" the problem has been resolved!!!
> 3) Wait your opinions :-)
>
> NicolaZap
>
> "NicolaZap" wrote:
|||NO, it is the 32 bit versione of SQLServer2005.
"Hilary Cotter" wrote:
> Is this the 64 bit version of SQL server? If so you should be using Foxit.
> "NicolaZap" <NicolaZap@.discussions.microsoft.com> wrote in message
> news:091F34B8-1140-4A74-9D2A-03F02B00DB8A@.microsoft.com...
>
>
Full Text Search know which row has error in indexing
Hello everybody,
I have installed SQLServer 2005 for take advantage of full text seach features.
When I start full population, there are some rows that cause error and not are indexed.
I see the log in C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLFT0000700005.LOG. In this file there are seven rows that have error and for everyone a line that say:
"Error '0x8004fd01: The IFilter::Init() function call failed.' ... full-text key value 0x495532303045594154504B55. Attempt will be made to reindex it.
The component 'pdffilt.dll' reported error while indexing. Component path 'C:\Programmi\Foxit Software\Foxit PDF IFilter\pdffilt.dll'."With different full-text key value for every line.
My question is:
1) Start from a full-text key (ie. 0x495532303045594154504B55), how I arrive to the record in the source table for understand which is the problem?Otherwise how can I examine the problem if I don't know the row that cause the error?
2) What is the error 0x8004fd01?
3) Could be that the PDF filter of Foxit is better than Adobe? With Adobe filter (version 6) my full pupulation is endless!
Thanks!
OK resolved
1)The hexadecimal value must to be converted to decimal and then to ASCII (http://www.vortex.prodigynet.co.uk/misc/ascii_conv.html)
2) Probably due to the incorrect format because changing the type column value from ".pdf" to ".txt" the problem has been resolved!!!
3) Wait your opinions :-)
NicolaZap
2012年3月19日星期一
Full text indexing scheduler SQL
Hi all,
I'm hosting a site on DiscountASP and since I have full text enabled
I
need to schedule the population. However, they don't allow using SQL
Enterprise Manager and said I have to use SQL to do it. I'm really
having a hard time finding a sample code. Can someone please show me
an example of doing this? Let's say my database is called
'DatabaseA',
my table is call 'TableA' and I have full text enabled on two columns
'ColA' and 'ColB'.
Thank you
Maz"maflatoun@.gmail.com" wrote:
> Hi all,
> I'm hosting a site on DiscountASP and since I have full text enabled
> I
> need to schedule the population. However, they don't allow using SQL
> Enterprise Manager and said I have to use SQL to do it. I'm really
> having a hard time finding a sample code. Can someone please show me
> an example of doing this? Let's say my database is called
> 'DatabaseA',
> my table is call 'TableA' and I have full text enabled on two columns
> 'ColA' and 'ColB'.
>
> Thank you
> Maz
>
Hi
Look at the topic "Administering Full-Text Features Using SQL Enterprise
Manager" which also has all the Transact-SQL examples for the stored
procedures used to administer FTS.
John
I'm hosting a site on DiscountASP and since I have full text enabled
I
need to schedule the population. However, they don't allow using SQL
Enterprise Manager and said I have to use SQL to do it. I'm really
having a hard time finding a sample code. Can someone please show me
an example of doing this? Let's say my database is called
'DatabaseA',
my table is call 'TableA' and I have full text enabled on two columns
'ColA' and 'ColB'.
Thank you
Maz"maflatoun@.gmail.com" wrote:
> Hi all,
> I'm hosting a site on DiscountASP and since I have full text enabled
> I
> need to schedule the population. However, they don't allow using SQL
> Enterprise Manager and said I have to use SQL to do it. I'm really
> having a hard time finding a sample code. Can someone please show me
> an example of doing this? Let's say my database is called
> 'DatabaseA',
> my table is call 'TableA' and I have full text enabled on two columns
> 'ColA' and 'ColB'.
>
> Thank you
> Maz
>
Hi
Look at the topic "Administering Full-Text Features Using SQL Enterprise
Manager" which also has all the Transact-SQL examples for the stored
procedures used to administer FTS.
John
Full Text Indexing not Populating
When I start the population it doesn't appear to stop. I don't think it even
starts. What I mean is when I right-click on the table and choose start full
population, I go back to check and the only active choice is to stop the
population. After 24 hours it appears to still be running (or trying to
run). I'm using SQL 2000. I tried to do this on a copy of the same table on
a different server and it works fine. I tried this on a different table (and
different database) on the same server and get the same issue.
Any suggestions?
JayB
JayB,
How many rows are in the FT-enable SQL Server 2000 table? The time for the
"Microsoft Search" (mssearch.exe) service to complete a Full Population is
highly dependent upon the number of rows as well as where you have the FT
Catalog defined. Could you post the output of -- select count(*) from
<your_table> -- as that will give us some idea of how long it should take to
complete.
Also, you should review your server's Application event log for "Microsoft
Search" or MssCi source events, such as the following two events as this
will indicate that the final stage (Master Merge) of a Full Population has
completed.
Event Type: Information
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3018
Date: 9/16/2004
Time: 9:10:32 AM
User: N/A
Computer: JTKWIN2003
Description:
The end of crawl for project <SQLServer$SQL2K SQL0000500007> has been
detected. The Gatherer successfully processed 24 documents totaling 0K. It
failed to filter 0 documents. 0 URLs could not be reached or were denied
access.
and
Event Type: Information
Event Source: MssCi
Event Category: None
Event ID: 4103
Date: 9/16/2004
Time: 9:10:32 AM
User: N/A
Computer: JTKWIN2003
Description:
Master merge has completed on
f:\mssql80\mssql$sql2k\ftdata\sql0000500007\build\ indexer\cifiles.
You can also issue the following SQL metadata query to get the FT Population
status on a per FT Catalog basis:
USE Northwind
GO
SELECT fulltextcatalogproperty('<your_FT_Catalog_name>', 'PopulateStatus')
Regards,
John
"JayB" <jerry280@.yahoo.com> wrote in message
news:Mxi2d.4150$uM5.1342@.news01.roc.ny...
> When I start the population it doesn't appear to stop. I don't think it
even
> starts. What I mean is when I right-click on the table and choose start
full
> population, I go back to check and the only active choice is to stop the
> population. After 24 hours it appears to still be running (or trying to
> run). I'm using SQL 2000. I tried to do this on a copy of the same table
on
> a different server and it works fine. I tried this on a different table
(and
> different database) on the same server and get the same issue.
> Any suggestions?
> --
> JayB
>
|||The record count is 1107. It does the same thing with a table containing 4
rows.
The catalog is defined at C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA
(default).
In event viewer I have the 3018 event ID but not the 4103 event ID.
Using your SQL metadata query it produced a value of 0. I changed it to use
my database and catalog name.
I hope this information is helpful.
JayB
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OmrswgAnEHA.3988@.TK2MSFTNGP10.phx.gbl...
> JayB,
> How many rows are in the FT-enable SQL Server 2000 table? The time for the
> "Microsoft Search" (mssearch.exe) service to complete a Full Population is
> highly dependent upon the number of rows as well as where you have the FT
> Catalog defined. Could you post the output of -- select count(*) from
> <your_table> -- as that will give us some idea of how long it should take
to
> complete.
> Also, you should review your server's Application event log for "Microsoft
> Search" or MssCi source events, such as the following two events as this
> will indicate that the final stage (Master Merge) of a Full Population has
> completed.
> Event Type: Information
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3018
> Date: 9/16/2004
> Time: 9:10:32 AM
> User: N/A
> Computer: JTKWIN2003
> Description:
> The end of crawl for project <SQLServer$SQL2K SQL0000500007> has been
> detected. The Gatherer successfully processed 24 documents totaling 0K. It
> failed to filter 0 documents. 0 URLs could not be reached or were denied
> access.
> and
> Event Type: Information
> Event Source: MssCi
> Event Category: None
> Event ID: 4103
> Date: 9/16/2004
> Time: 9:10:32 AM
> User: N/A
> Computer: JTKWIN2003
> Description:
> Master merge has completed on
> f:\mssql80\mssql$sql2k\ftdata\sql0000500007\build\ indexer\cifiles.
> You can also issue the following SQL metadata query to get the FT
Population
> status on a per FT Catalog basis:
> USE Northwind
> GO
> SELECT fulltextcatalogproperty('<your_FT_Catalog_name>', 'PopulateStatus')
> Regards,
> John
>
>
>
> "JayB" <jerry280@.yahoo.com> wrote in message
> news:Mxi2d.4150$uM5.1342@.news01.roc.ny...
> even
> full
> on
> (and
>
|||After stopping and restarting the population I looked at the event viewer
and have one event ID of 3019 indicating that the crawl has started. That
was at 12:06pm. It is now 12:20pm. There are no other messages related to
this population.
JayB
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OmrswgAnEHA.3988@.TK2MSFTNGP10.phx.gbl...
> JayB,
> How many rows are in the FT-enable SQL Server 2000 table? The time for the
> "Microsoft Search" (mssearch.exe) service to complete a Full Population is
> highly dependent upon the number of rows as well as where you have the FT
> Catalog defined. Could you post the output of -- select count(*) from
> <your_table> -- as that will give us some idea of how long it should take
to
> complete.
> Also, you should review your server's Application event log for "Microsoft
> Search" or MssCi source events, such as the following two events as this
> will indicate that the final stage (Master Merge) of a Full Population has
> completed.
> Event Type: Information
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3018
> Date: 9/16/2004
> Time: 9:10:32 AM
> User: N/A
> Computer: JTKWIN2003
> Description:
> The end of crawl for project <SQLServer$SQL2K SQL0000500007> has been
> detected. The Gatherer successfully processed 24 documents totaling 0K. It
> failed to filter 0 documents. 0 URLs could not be reached or were denied
> access.
> and
> Event Type: Information
> Event Source: MssCi
> Event Category: None
> Event ID: 4103
> Date: 9/16/2004
> Time: 9:10:32 AM
> User: N/A
> Computer: JTKWIN2003
> Description:
> Master merge has completed on
> f:\mssql80\mssql$sql2k\ftdata\sql0000500007\build\ indexer\cifiles.
> You can also issue the following SQL metadata query to get the FT
Population
> status on a per FT Catalog basis:
> USE Northwind
> GO
> SELECT fulltextcatalogproperty('<your_FT_Catalog_name>', 'PopulateStatus')
> Regards,
> John
>
>
>
> "JayB" <jerry280@.yahoo.com> wrote in message
> news:Mxi2d.4150$uM5.1342@.news01.roc.ny...
> even
> full
> on
> (and
>
|||You're welcome, JayB,
Have you or anyone else changed the MSSQLServer service startup account &/or
password via Win2K's components service? Also, does the SQL Server login
BUILTIN\Administrator exist on this server with all of it's normal defaults:
master database, SysAdmin rights? Depending upon your answers, you should
review the following KB articles:
317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate Catalogs
http://support.microsoft.com/default...b;en-us;317746
277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component
Services]
http://support.microsoft.com/default...B;EN-US;277549
If the BUILTIN\Administrator account was removed for security reasons, you
can use the following SQL code to get FTS to work without it:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
Regards,
John
"JayB" <jerry280@.yahoo.com> wrote in message
news:7Yj2d.4154$wK5.3659@.news01.roc.ny...[vbcol=seagreen]
> After stopping and restarting the population I looked at the event viewer
> and have one event ID of 3019 indicating that the crawl has started. That
> was at 12:06pm. It is now 12:20pm. There are no other messages related to
> this population.
> --
> JayB
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OmrswgAnEHA.3988@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
is[vbcol=seagreen]
FT[vbcol=seagreen]
take[vbcol=seagreen]
> to
"Microsoft[vbcol=seagreen]
this[vbcol=seagreen]
has[vbcol=seagreen]
It[vbcol=seagreen]
> Population
'PopulateStatus')[vbcol=seagreen]
it[vbcol=seagreen]
start[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
table[vbcol=seagreen]
table
>
|||Thanks John,
We haven't changed the startup account and we made sure that the
BUILTIN\Administrator exists. I'll take a look at the suggested articles you
included.
Thanks again!
JayB
"John Kane" <jt-kane@.comcast.net> wrote in message
news:eKJ1mOBnEHA.3868@.TK2MSFTNGP11.phx.gbl...
> You're welcome, JayB,
> Have you or anyone else changed the MSSQLServer service startup account
&/or
> password via Win2K's components service? Also, does the SQL Server login
> BUILTIN\Administrator exist on this server with all of it's normal
defaults:
> master database, SysAdmin rights? Depending upon your answers, you should
> review the following KB articles:
> 317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate
Catalogs
> http://support.microsoft.com/default...b;en-us;317746
> 277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
> MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K
Component[vbcol=seagreen]
> Services]
> http://support.microsoft.com/default...B;EN-US;277549
> If the BUILTIN\Administrator account was removed for security reasons, you
> can use the following SQL code to get FTS to work without it:
> exec sp_grantlogin N'NT Authority\System'
> exec sp_defaultdb N'NT Authority\System', N'master'
> exec sp_defaultlanguage N'NT Authority\System','us_english'
> exec sp_addsrvrolemember N'NT Authority\System', sysadmin
> Regards,
> John
>
> "JayB" <jerry280@.yahoo.com> wrote in message
> news:7Yj2d.4154$wK5.3659@.news01.roc.ny...
viewer[vbcol=seagreen]
That[vbcol=seagreen]
to[vbcol=seagreen]
> the
Population[vbcol=seagreen]
> is
> FT
> take
> "Microsoft
> this
> has
0K.[vbcol=seagreen]
> It
denied
> 'PopulateStatus')
> it
> start
> the
> to
> table
> table
>
starts. What I mean is when I right-click on the table and choose start full
population, I go back to check and the only active choice is to stop the
population. After 24 hours it appears to still be running (or trying to
run). I'm using SQL 2000. I tried to do this on a copy of the same table on
a different server and it works fine. I tried this on a different table (and
different database) on the same server and get the same issue.
Any suggestions?
JayB
JayB,
How many rows are in the FT-enable SQL Server 2000 table? The time for the
"Microsoft Search" (mssearch.exe) service to complete a Full Population is
highly dependent upon the number of rows as well as where you have the FT
Catalog defined. Could you post the output of -- select count(*) from
<your_table> -- as that will give us some idea of how long it should take to
complete.
Also, you should review your server's Application event log for "Microsoft
Search" or MssCi source events, such as the following two events as this
will indicate that the final stage (Master Merge) of a Full Population has
completed.
Event Type: Information
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3018
Date: 9/16/2004
Time: 9:10:32 AM
User: N/A
Computer: JTKWIN2003
Description:
The end of crawl for project <SQLServer$SQL2K SQL0000500007> has been
detected. The Gatherer successfully processed 24 documents totaling 0K. It
failed to filter 0 documents. 0 URLs could not be reached or were denied
access.
and
Event Type: Information
Event Source: MssCi
Event Category: None
Event ID: 4103
Date: 9/16/2004
Time: 9:10:32 AM
User: N/A
Computer: JTKWIN2003
Description:
Master merge has completed on
f:\mssql80\mssql$sql2k\ftdata\sql0000500007\build\ indexer\cifiles.
You can also issue the following SQL metadata query to get the FT Population
status on a per FT Catalog basis:
USE Northwind
GO
SELECT fulltextcatalogproperty('<your_FT_Catalog_name>', 'PopulateStatus')
Regards,
John
"JayB" <jerry280@.yahoo.com> wrote in message
news:Mxi2d.4150$uM5.1342@.news01.roc.ny...
> When I start the population it doesn't appear to stop. I don't think it
even
> starts. What I mean is when I right-click on the table and choose start
full
> population, I go back to check and the only active choice is to stop the
> population. After 24 hours it appears to still be running (or trying to
> run). I'm using SQL 2000. I tried to do this on a copy of the same table
on
> a different server and it works fine. I tried this on a different table
(and
> different database) on the same server and get the same issue.
> Any suggestions?
> --
> JayB
>
|||The record count is 1107. It does the same thing with a table containing 4
rows.
The catalog is defined at C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA
(default).
In event viewer I have the 3018 event ID but not the 4103 event ID.
Using your SQL metadata query it produced a value of 0. I changed it to use
my database and catalog name.
I hope this information is helpful.
JayB
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OmrswgAnEHA.3988@.TK2MSFTNGP10.phx.gbl...
> JayB,
> How many rows are in the FT-enable SQL Server 2000 table? The time for the
> "Microsoft Search" (mssearch.exe) service to complete a Full Population is
> highly dependent upon the number of rows as well as where you have the FT
> Catalog defined. Could you post the output of -- select count(*) from
> <your_table> -- as that will give us some idea of how long it should take
to
> complete.
> Also, you should review your server's Application event log for "Microsoft
> Search" or MssCi source events, such as the following two events as this
> will indicate that the final stage (Master Merge) of a Full Population has
> completed.
> Event Type: Information
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3018
> Date: 9/16/2004
> Time: 9:10:32 AM
> User: N/A
> Computer: JTKWIN2003
> Description:
> The end of crawl for project <SQLServer$SQL2K SQL0000500007> has been
> detected. The Gatherer successfully processed 24 documents totaling 0K. It
> failed to filter 0 documents. 0 URLs could not be reached or were denied
> access.
> and
> Event Type: Information
> Event Source: MssCi
> Event Category: None
> Event ID: 4103
> Date: 9/16/2004
> Time: 9:10:32 AM
> User: N/A
> Computer: JTKWIN2003
> Description:
> Master merge has completed on
> f:\mssql80\mssql$sql2k\ftdata\sql0000500007\build\ indexer\cifiles.
> You can also issue the following SQL metadata query to get the FT
Population
> status on a per FT Catalog basis:
> USE Northwind
> GO
> SELECT fulltextcatalogproperty('<your_FT_Catalog_name>', 'PopulateStatus')
> Regards,
> John
>
>
>
> "JayB" <jerry280@.yahoo.com> wrote in message
> news:Mxi2d.4150$uM5.1342@.news01.roc.ny...
> even
> full
> on
> (and
>
|||After stopping and restarting the population I looked at the event viewer
and have one event ID of 3019 indicating that the crawl has started. That
was at 12:06pm. It is now 12:20pm. There are no other messages related to
this population.
JayB
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OmrswgAnEHA.3988@.TK2MSFTNGP10.phx.gbl...
> JayB,
> How many rows are in the FT-enable SQL Server 2000 table? The time for the
> "Microsoft Search" (mssearch.exe) service to complete a Full Population is
> highly dependent upon the number of rows as well as where you have the FT
> Catalog defined. Could you post the output of -- select count(*) from
> <your_table> -- as that will give us some idea of how long it should take
to
> complete.
> Also, you should review your server's Application event log for "Microsoft
> Search" or MssCi source events, such as the following two events as this
> will indicate that the final stage (Master Merge) of a Full Population has
> completed.
> Event Type: Information
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3018
> Date: 9/16/2004
> Time: 9:10:32 AM
> User: N/A
> Computer: JTKWIN2003
> Description:
> The end of crawl for project <SQLServer$SQL2K SQL0000500007> has been
> detected. The Gatherer successfully processed 24 documents totaling 0K. It
> failed to filter 0 documents. 0 URLs could not be reached or were denied
> access.
> and
> Event Type: Information
> Event Source: MssCi
> Event Category: None
> Event ID: 4103
> Date: 9/16/2004
> Time: 9:10:32 AM
> User: N/A
> Computer: JTKWIN2003
> Description:
> Master merge has completed on
> f:\mssql80\mssql$sql2k\ftdata\sql0000500007\build\ indexer\cifiles.
> You can also issue the following SQL metadata query to get the FT
Population
> status on a per FT Catalog basis:
> USE Northwind
> GO
> SELECT fulltextcatalogproperty('<your_FT_Catalog_name>', 'PopulateStatus')
> Regards,
> John
>
>
>
> "JayB" <jerry280@.yahoo.com> wrote in message
> news:Mxi2d.4150$uM5.1342@.news01.roc.ny...
> even
> full
> on
> (and
>
|||You're welcome, JayB,
Have you or anyone else changed the MSSQLServer service startup account &/or
password via Win2K's components service? Also, does the SQL Server login
BUILTIN\Administrator exist on this server with all of it's normal defaults:
master database, SysAdmin rights? Depending upon your answers, you should
review the following KB articles:
317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate Catalogs
http://support.microsoft.com/default...b;en-us;317746
277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component
Services]
http://support.microsoft.com/default...B;EN-US;277549
If the BUILTIN\Administrator account was removed for security reasons, you
can use the following SQL code to get FTS to work without it:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
Regards,
John
"JayB" <jerry280@.yahoo.com> wrote in message
news:7Yj2d.4154$wK5.3659@.news01.roc.ny...[vbcol=seagreen]
> After stopping and restarting the population I looked at the event viewer
> and have one event ID of 3019 indicating that the crawl has started. That
> was at 12:06pm. It is now 12:20pm. There are no other messages related to
> this population.
> --
> JayB
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OmrswgAnEHA.3988@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
is[vbcol=seagreen]
FT[vbcol=seagreen]
take[vbcol=seagreen]
> to
"Microsoft[vbcol=seagreen]
this[vbcol=seagreen]
has[vbcol=seagreen]
It[vbcol=seagreen]
> Population
'PopulateStatus')[vbcol=seagreen]
it[vbcol=seagreen]
start[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
table[vbcol=seagreen]
table
>
|||Thanks John,
We haven't changed the startup account and we made sure that the
BUILTIN\Administrator exists. I'll take a look at the suggested articles you
included.
Thanks again!
JayB
"John Kane" <jt-kane@.comcast.net> wrote in message
news:eKJ1mOBnEHA.3868@.TK2MSFTNGP11.phx.gbl...
> You're welcome, JayB,
> Have you or anyone else changed the MSSQLServer service startup account
&/or
> password via Win2K's components service? Also, does the SQL Server login
> BUILTIN\Administrator exist on this server with all of it's normal
defaults:
> master database, SysAdmin rights? Depending upon your answers, you should
> review the following KB articles:
> 317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate
Catalogs
> http://support.microsoft.com/default...b;en-us;317746
> 277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
> MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K
Component[vbcol=seagreen]
> Services]
> http://support.microsoft.com/default...B;EN-US;277549
> If the BUILTIN\Administrator account was removed for security reasons, you
> can use the following SQL code to get FTS to work without it:
> exec sp_grantlogin N'NT Authority\System'
> exec sp_defaultdb N'NT Authority\System', N'master'
> exec sp_defaultlanguage N'NT Authority\System','us_english'
> exec sp_addsrvrolemember N'NT Authority\System', sysadmin
> Regards,
> John
>
> "JayB" <jerry280@.yahoo.com> wrote in message
> news:7Yj2d.4154$wK5.3659@.news01.roc.ny...
viewer[vbcol=seagreen]
That[vbcol=seagreen]
to[vbcol=seagreen]
> the
Population[vbcol=seagreen]
> is
> FT
> take
> "Microsoft
> this
> has
0K.[vbcol=seagreen]
> It
denied
> 'PopulateStatus')
> it
> start
> the
> to
> table
> table
>
2012年3月11日星期日
Full text index population is slow
I have 1 milion rows on a SQL server 2005 SP1 with 2 Text columns to
be indexed.
The DB is replicated on another server. Transactional.
On the Publisher the population take the CPU near the 100% for 3 day
and then the work is done.
On the subscriber, that is also the distributor the work don't end.
The CPU of the server is at the 100% (2 XEON 4GB Ram).
In the profiler i see many commands SP_fulltext_getdbdata ma the
sp_fulltext_pending_changes return ever the same numbre of pending
rows or more.
The columns contains italian word.
Someone can help me.
Something is very wrong here. What do the gatherer logs say. You can find
them in c:\Program Files\Microsoft sql server\mssql.X\mssql\log
and they look like SQLFT0018700017.log.X
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<innocenzo.daraio@.gmail.com> wrote in message
news:1176273986.698099.26940@.l77g2000hsb.googlegro ups.com...
>I have 1 milion rows on a SQL server 2005 SP1 with 2 Text columns to
> be indexed.
> The DB is replicated on another server. Transactional.
> On the Publisher the population take the CPU near the 100% for 3 day
> and then the work is done.
> On the subscriber, that is also the distributor the work don't end.
> The CPU of the server is at the 100% (2 XEON 4GB Ram).
> In the profiler i see many commands SP_fulltext_getdbdata ma the
> sp_fulltext_pending_changes return ever the same numbre of pending
> rows or more.
> The columns contains italian word.
> Someone can help me.
>
be indexed.
The DB is replicated on another server. Transactional.
On the Publisher the population take the CPU near the 100% for 3 day
and then the work is done.
On the subscriber, that is also the distributor the work don't end.
The CPU of the server is at the 100% (2 XEON 4GB Ram).
In the profiler i see many commands SP_fulltext_getdbdata ma the
sp_fulltext_pending_changes return ever the same numbre of pending
rows or more.
The columns contains italian word.
Someone can help me.
Something is very wrong here. What do the gatherer logs say. You can find
them in c:\Program Files\Microsoft sql server\mssql.X\mssql\log
and they look like SQLFT0018700017.log.X
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<innocenzo.daraio@.gmail.com> wrote in message
news:1176273986.698099.26940@.l77g2000hsb.googlegro ups.com...
>I have 1 milion rows on a SQL server 2005 SP1 with 2 Text columns to
> be indexed.
> The DB is replicated on another server. Transactional.
> On the Publisher the population take the CPU near the 100% for 3 day
> and then the work is done.
> On the subscriber, that is also the distributor the work don't end.
> The CPU of the server is at the 100% (2 XEON 4GB Ram).
> In the profiler i see many commands SP_fulltext_getdbdata ma the
> sp_fulltext_pending_changes return ever the same numbre of pending
> rows or more.
> The columns contains italian word.
> Someone can help me.
>
Full Text index catalog population error
Does anyone know what the services are involved during the SQL full
text index catalog population?
Some of my application event log errors are "Error: 80070426 - The
service has not been started.".
I know that my SQL server is running and MSsearch is running. I don't
know any other services needed for this operation.
Thank you in advance for any help!
Its MSSearch, SQL Server, SQL Server Agent (if you are scheduling your
populations).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"discover" <hongc@.lanproinc.com> wrote in message
news:1163533752.261545.225040@.f16g2000cwb.googlegr oups.com...
> Does anyone know what the services are involved during the SQL full
> text index catalog population?
> Some of my application event log errors are "Error: 80070426 - The
> service has not been started.".
> I know that my SQL server is running and MSsearch is running. I don't
> know any other services needed for this operation.
> Thank you in advance for any help!
>
text index catalog population?
Some of my application event log errors are "Error: 80070426 - The
service has not been started.".
I know that my SQL server is running and MSsearch is running. I don't
know any other services needed for this operation.
Thank you in advance for any help!
Its MSSearch, SQL Server, SQL Server Agent (if you are scheduling your
populations).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"discover" <hongc@.lanproinc.com> wrote in message
news:1163533752.261545.225040@.f16g2000cwb.googlegr oups.com...
> Does anyone know what the services are involved during the SQL full
> text index catalog population?
> Some of my application event log errors are "Error: 80070426 - The
> service has not been started.".
> I know that my SQL server is running and MSsearch is running. I don't
> know any other services needed for this operation.
> Thank you in advance for any help!
>
2012年3月9日星期五
full text incremental population
Hi!
Is there any possibility to populate indexes for inserted rows only
(or not to updated rows)
Regards
MaxOn Apr 4, 10:27 am, maxl...@.gmail.com wrote:
> Hi!
> Is there any possibility to populate indexes for inserted rows only
> (or not to updated rows)
> Regards
> Max
pls... if anyone has came up with same problem. share your knowledge
Is there any possibility to populate indexes for inserted rows only
(or not to updated rows)
Regards
MaxOn Apr 4, 10:27 am, maxl...@.gmail.com wrote:
> Hi!
> Is there any possibility to populate indexes for inserted rows only
> (or not to updated rows)
> Regards
> Max
pls... if anyone has came up with same problem. share your knowledge
full text incremental population
Hi!
Is there any possibility to populate indexes for inserted rows only
(or not to updated rows)
Regards
Max
On Apr 4, 10:27 am, maxl...@.gmail.com wrote:
> Hi!
> Is there any possibility to populate indexes for inserted rows only
> (or not to updated rows)
> Regards
> Max
pls... if anyone has came up with same problem. share your knowledge
Is there any possibility to populate indexes for inserted rows only
(or not to updated rows)
Regards
Max
On Apr 4, 10:27 am, maxl...@.gmail.com wrote:
> Hi!
> Is there any possibility to populate indexes for inserted rows only
> (or not to updated rows)
> Regards
> Max
pls... if anyone has came up with same problem. share your knowledge
2012年3月7日星期三
Full Text - Index Population - HDD Access
Hi,
I'm indexing a single field on a table with 10 million rows in it, its
currently taking about 48 hours to build.
I've been looking into performance bottlenecks on the system and see that
this delay is being mainly caused by MSSearch writing massive amounts of
data to the FT Index location. Since the finished index is only about 400mb
I can only imagine that the population process is rewriting the data again
and again to the disk as it is updated.
Is this a fair assumption? If so is there anyway around it, maybe to tell
MSSearch to use ram until the population is build then write the index to
disk?
Thanks
Michael
No.
There is the resource usage setting which may increase performance during
the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
The more resources you can spend to improve the indexing process the better,
normally you get the best bang for your buck by investing in the disk
subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
01 is not considered to be a good choice as it does not offer the the
protection 10 does.
Also your database should be raid 5 depending on the level of write activity
on it.
What type of content are you indexing. You will get best performance by
indexing text content as opposed to indexing documents in their native
format (doc, xls, pdf, etc).
Please review the full text white paper on SQL FTS performance at:
http://support.microsoft.com/support...er.asp?GSSNB=1
Save this document as a zip and extract it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Mike
What hardware is it running on?
Is the Full-Text Index on the same physical drive/lun as the database? It
should be possible to increase IO throughput by placing the Full-Text Index
on its own drive.
Are you using SCSI disks? If so are you using the faster 15k RPM disks as
apposed to 10k RPM disks.
How much RAM does the machine have?
How many processors and what spec are they?
Is SQL Server configured to leave some RAM free for the Operating System and
other Applications, i.e. if you have 2GB RAM is SQL configured to use max
1.5GB?
You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
Search is much faster at indexing.
http://msdn.microsoft.com/library/en...asp?frame=true
Rich
"Hilary Cotter" wrote:
> No.
> There is the resource usage setting which may increase performance during
> the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
> The more resources you can spend to improve the indexing process the better,
> normally you get the best bang for your buck by investing in the disk
> subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
> 01 is not considered to be a good choice as it does not offer the the
> protection 10 does.
> Also your database should be raid 5 depending on the level of write activity
> on it.
> What type of content are you indexing. You will get best performance by
> indexing text content as opposed to indexing documents in their native
> format (doc, xls, pdf, etc).
> Please review the full text white paper on SQL FTS performance at:
> http://support.microsoft.com/support...er.asp?GSSNB=1
> Save this document as a zip and extract it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
> news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> 400mb
>
>
|||Mike,
Yes, it is a fair assumption. You should review the SQL Server 2000 BOL
title "Full-text Search Recommendations" as well as the FTS Deployment white
paper. Are there workarounds? Yes. You can ensure that the disk drive that
your FT Catalog resides is separate from your database files as well as very
fast (>10,000 RPMs) and configured as RAID0 or RAID10. The Full Text
Indexing process is primarily I/O bound, but can also consume much memory
and CPU usage from time-to-time during Shadow Merges and at the end of the
process due to Master Merge.
Regards,
John
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Hi Rich,
It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
currently has access to all system RAM but is sat at approx 1.7Gb during the
indexing process.
Both the FullText index and the SQL database are on separate physical SATA
drives, however because of an apparent incompatibility issue with Windows
and running the two SATA drives on their own bus they are both running
through the standard IDE bus, I imagine this is not doing the performance
any favours.
Thanks for the 2005 tip, I'll give that a try.
Can you offer any other suggestions to the setup?
Thanks
Mike
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Mike
> What hardware is it running on?
> Is the Full-Text Index on the same physical drive/lun as the database? It
> should be possible to increase IO throughput by placing the Full-Text
Index
> on its own drive.
> Are you using SCSI disks? If so are you using the faster 15k RPM disks as
> apposed to 10k RPM disks.
> How much RAM does the machine have?
> How many processors and what spec are they?
> Is SQL Server configured to leave some RAM free for the Operating System
and
> other Applications, i.e. if you have 2GB RAM is SQL configured to use max
> 1.5GB?
> You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
> Search is much faster at indexing.
>
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
> Rich
>
> "Hilary Cotter" wrote:
during[vbcol=seagreen]
'catalogname','resource_usgae',5)[vbcol=seagreen]
better,[vbcol=seagreen]
array.[vbcol=seagreen]
activity[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
that[vbcol=seagreen]
of[vbcol=seagreen]
again[vbcol=seagreen]
tell[vbcol=seagreen]
to[vbcol=seagreen]
|||Mike
Have you implemented the /3GB switch in the OS boot.ini? If not by default
2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
aside for apps and 1GB for OS. This probably explains why SQL Server is only
using 1.7GB RAM. It would probably be using more if you had the /3GB switch
set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
apps including MSSearch (I think), file system caching, etc.
Even if you don't have the budget for a 2 proc SQL license I would always
recommend purchasing a second physical processor and configuring SQL Server
to only use one of the processors. This leaves the second processor free for
other applications, OS, remote administration.
You might want to try the manufacturers web-site regarding BIOS / firmware
updates for your SATA / IDE issue.
Rich
"Mike Davies" wrote:
> Hi Rich,
> It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
> currently has access to all system RAM but is sat at approx 1.7Gb during the
> indexing process.
> Both the FullText index and the SQL database are on separate physical SATA
> drives, however because of an apparent incompatibility issue with Windows
> and running the two SATA drives on their own bus they are both running
> through the standard IDE bus, I imagine this is not doing the performance
> any favours.
> Thanks for the 2005 tip, I'll give that a try.
> Can you offer any other suggestions to the setup?
> Thanks
> Mike
>
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Index
> and
> http://msdn.microsoft.com/library/en...asp?frame=true
> during
> 'catalogname','resource_usgae',5)
> better,
> array.
> activity
> http://support.microsoft.com/support...er.asp?GSSNB=1
> that
> of
> again
> tell
> to
>
>
|||Mike,
Rich, while the /3GB switch can help SQL Server 2000, it has no affect on
the "Microsoft Search" (mssearch.exe) FT Search engine as it can only use up
to a max of 512MB of physical RAM. However, you must set the resource_usage
value to 5 (dedicated) via sp_fulltext_service 'resource_usage', 5 and you
must have at least 512Mb of RAM reserved for the MSSearch service that is
NOT used by the OS or by SQL Server.
If you can purchase an additional CPU, you can set the MSSearch service's to
use one CPU and then use sp_configure to set the affinity of SQL Server to
that cpu to avoid cpu contention during heavy MSSearch CPU usage via the
following method:
You can launch the Tskmgr.exe from the AT command on the multi-proc server
where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers.
Regards,
John
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for[vbcol=seagreen]
> other applications, OS, remote administration.
> You might want to try the manufacturers web-site regarding BIOS / firmware
> updates for your SATA / IDE issue.
> Rich
>
> "Mike Davies" wrote:
Server[vbcol=seagreen]
the[vbcol=seagreen]
SATA[vbcol=seagreen]
Windows[vbcol=seagreen]
performance[vbcol=seagreen]
It[vbcol=seagreen]
as[vbcol=seagreen]
System[vbcol=seagreen]
max[vbcol=seagreen]
Full-Text[vbcol=seagreen]
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
by[vbcol=seagreen]
native[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
its[vbcol=seagreen]
see[vbcol=seagreen]
amounts[vbcol=seagreen]
about[vbcol=seagreen]
data[vbcol=seagreen]
to[vbcol=seagreen]
index[vbcol=seagreen]
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
>
<SNIP>
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
> Server
> to only use one of the processors. This leaves the second processor free
> for
> other applications, OS, remote administration.
>
</SNIP>
Please be careful about that because, in general, you have to purchase a
processor license for every processor that the OS can see, whether that
processor is configured to run SQL Server or not. For more information,
please refer to the SQL Server 2000 Licensing FAQ web page at
http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
Licensing is reproduced below for your benefit.
Q. Do I have to license all of the processors in a server?
A. You only have to acquire licenses for processors that are
accessible to any copy of the operating system upon which SQL Server 2000 is
installed.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for
> other applications, OS, remote administration.
Unless thigns have changed, last I knew, MS didn't allow that.
|||Stephen
Thanks for clarifying that.
Obviously there is a difference between what the product is capable of and
what the licensing folk think the product is or should be capable of.
In this instance I feel the Product Team got it right. In the real world
people often have to run more than one application on a server and should not
be forced to buy additional licenses.
With the move in the industry to consolidate this could a lot of unnecessary
expense.
Lets hope this license issue is addressed in the next release of SQL Server
2005.
Rich
"Stephen Dybing [MSFT]" wrote:
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> <SNIP>
> </SNIP>
> Please be careful about that because, in general, you have to purchase a
> processor license for every processor that the OS can see, whether that
> processor is configured to run SQL Server or not. For more information,
> please refer to the SQL Server 2000 Licensing FAQ web page at
> http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
> Licensing is reproduced below for your benefit.
> Q. Do I have to license all of the processors in a server?
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000 is
> installed.
>
>
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights
I'm indexing a single field on a table with 10 million rows in it, its
currently taking about 48 hours to build.
I've been looking into performance bottlenecks on the system and see that
this delay is being mainly caused by MSSearch writing massive amounts of
data to the FT Index location. Since the finished index is only about 400mb
I can only imagine that the population process is rewriting the data again
and again to the disk as it is updated.
Is this a fair assumption? If so is there anyway around it, maybe to tell
MSSearch to use ram until the population is build then write the index to
disk?
Thanks
Michael
No.
There is the resource usage setting which may increase performance during
the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
The more resources you can spend to improve the indexing process the better,
normally you get the best bang for your buck by investing in the disk
subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
01 is not considered to be a good choice as it does not offer the the
protection 10 does.
Also your database should be raid 5 depending on the level of write activity
on it.
What type of content are you indexing. You will get best performance by
indexing text content as opposed to indexing documents in their native
format (doc, xls, pdf, etc).
Please review the full text white paper on SQL FTS performance at:
http://support.microsoft.com/support...er.asp?GSSNB=1
Save this document as a zip and extract it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Mike
What hardware is it running on?
Is the Full-Text Index on the same physical drive/lun as the database? It
should be possible to increase IO throughput by placing the Full-Text Index
on its own drive.
Are you using SCSI disks? If so are you using the faster 15k RPM disks as
apposed to 10k RPM disks.
How much RAM does the machine have?
How many processors and what spec are they?
Is SQL Server configured to leave some RAM free for the Operating System and
other Applications, i.e. if you have 2GB RAM is SQL configured to use max
1.5GB?
You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
Search is much faster at indexing.
http://msdn.microsoft.com/library/en...asp?frame=true
Rich
"Hilary Cotter" wrote:
> No.
> There is the resource usage setting which may increase performance during
> the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
> The more resources you can spend to improve the indexing process the better,
> normally you get the best bang for your buck by investing in the disk
> subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
> 01 is not considered to be a good choice as it does not offer the the
> protection 10 does.
> Also your database should be raid 5 depending on the level of write activity
> on it.
> What type of content are you indexing. You will get best performance by
> indexing text content as opposed to indexing documents in their native
> format (doc, xls, pdf, etc).
> Please review the full text white paper on SQL FTS performance at:
> http://support.microsoft.com/support...er.asp?GSSNB=1
> Save this document as a zip and extract it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
> news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> 400mb
>
>
|||Mike,
Yes, it is a fair assumption. You should review the SQL Server 2000 BOL
title "Full-text Search Recommendations" as well as the FTS Deployment white
paper. Are there workarounds? Yes. You can ensure that the disk drive that
your FT Catalog resides is separate from your database files as well as very
fast (>10,000 RPMs) and configured as RAID0 or RAID10. The Full Text
Indexing process is primarily I/O bound, but can also consume much memory
and CPU usage from time-to-time during Shadow Merges and at the end of the
process due to Master Merge.
Regards,
John
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Hi Rich,
It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
currently has access to all system RAM but is sat at approx 1.7Gb during the
indexing process.
Both the FullText index and the SQL database are on separate physical SATA
drives, however because of an apparent incompatibility issue with Windows
and running the two SATA drives on their own bus they are both running
through the standard IDE bus, I imagine this is not doing the performance
any favours.
Thanks for the 2005 tip, I'll give that a try.
Can you offer any other suggestions to the setup?
Thanks
Mike
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Mike
> What hardware is it running on?
> Is the Full-Text Index on the same physical drive/lun as the database? It
> should be possible to increase IO throughput by placing the Full-Text
Index
> on its own drive.
> Are you using SCSI disks? If so are you using the faster 15k RPM disks as
> apposed to 10k RPM disks.
> How much RAM does the machine have?
> How many processors and what spec are they?
> Is SQL Server configured to leave some RAM free for the Operating System
and
> other Applications, i.e. if you have 2GB RAM is SQL configured to use max
> 1.5GB?
> You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
> Search is much faster at indexing.
>
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
> Rich
>
> "Hilary Cotter" wrote:
during[vbcol=seagreen]
'catalogname','resource_usgae',5)[vbcol=seagreen]
better,[vbcol=seagreen]
array.[vbcol=seagreen]
activity[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
that[vbcol=seagreen]
of[vbcol=seagreen]
again[vbcol=seagreen]
tell[vbcol=seagreen]
to[vbcol=seagreen]
|||Mike
Have you implemented the /3GB switch in the OS boot.ini? If not by default
2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
aside for apps and 1GB for OS. This probably explains why SQL Server is only
using 1.7GB RAM. It would probably be using more if you had the /3GB switch
set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
apps including MSSearch (I think), file system caching, etc.
Even if you don't have the budget for a 2 proc SQL license I would always
recommend purchasing a second physical processor and configuring SQL Server
to only use one of the processors. This leaves the second processor free for
other applications, OS, remote administration.
You might want to try the manufacturers web-site regarding BIOS / firmware
updates for your SATA / IDE issue.
Rich
"Mike Davies" wrote:
> Hi Rich,
> It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
> currently has access to all system RAM but is sat at approx 1.7Gb during the
> indexing process.
> Both the FullText index and the SQL database are on separate physical SATA
> drives, however because of an apparent incompatibility issue with Windows
> and running the two SATA drives on their own bus they are both running
> through the standard IDE bus, I imagine this is not doing the performance
> any favours.
> Thanks for the 2005 tip, I'll give that a try.
> Can you offer any other suggestions to the setup?
> Thanks
> Mike
>
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Index
> and
> http://msdn.microsoft.com/library/en...asp?frame=true
> during
> 'catalogname','resource_usgae',5)
> better,
> array.
> activity
> http://support.microsoft.com/support...er.asp?GSSNB=1
> that
> of
> again
> tell
> to
>
>
|||Mike,
Rich, while the /3GB switch can help SQL Server 2000, it has no affect on
the "Microsoft Search" (mssearch.exe) FT Search engine as it can only use up
to a max of 512MB of physical RAM. However, you must set the resource_usage
value to 5 (dedicated) via sp_fulltext_service 'resource_usage', 5 and you
must have at least 512Mb of RAM reserved for the MSSearch service that is
NOT used by the OS or by SQL Server.
If you can purchase an additional CPU, you can set the MSSearch service's to
use one CPU and then use sp_configure to set the affinity of SQL Server to
that cpu to avoid cpu contention during heavy MSSearch CPU usage via the
following method:
You can launch the Tskmgr.exe from the AT command on the multi-proc server
where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers.
Regards,
John
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for[vbcol=seagreen]
> other applications, OS, remote administration.
> You might want to try the manufacturers web-site regarding BIOS / firmware
> updates for your SATA / IDE issue.
> Rich
>
> "Mike Davies" wrote:
Server[vbcol=seagreen]
the[vbcol=seagreen]
SATA[vbcol=seagreen]
Windows[vbcol=seagreen]
performance[vbcol=seagreen]
It[vbcol=seagreen]
as[vbcol=seagreen]
System[vbcol=seagreen]
max[vbcol=seagreen]
Full-Text[vbcol=seagreen]
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
by[vbcol=seagreen]
native[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
its[vbcol=seagreen]
see[vbcol=seagreen]
amounts[vbcol=seagreen]
about[vbcol=seagreen]
data[vbcol=seagreen]
to[vbcol=seagreen]
index[vbcol=seagreen]
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
>
<SNIP>
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
> Server
> to only use one of the processors. This leaves the second processor free
> for
> other applications, OS, remote administration.
>
</SNIP>
Please be careful about that because, in general, you have to purchase a
processor license for every processor that the OS can see, whether that
processor is configured to run SQL Server or not. For more information,
please refer to the SQL Server 2000 Licensing FAQ web page at
http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
Licensing is reproduced below for your benefit.
Q. Do I have to license all of the processors in a server?
A. You only have to acquire licenses for processors that are
accessible to any copy of the operating system upon which SQL Server 2000 is
installed.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for
> other applications, OS, remote administration.
Unless thigns have changed, last I knew, MS didn't allow that.
|||Stephen
Thanks for clarifying that.
Obviously there is a difference between what the product is capable of and
what the licensing folk think the product is or should be capable of.
In this instance I feel the Product Team got it right. In the real world
people often have to run more than one application on a server and should not
be forced to buy additional licenses.
With the move in the industry to consolidate this could a lot of unnecessary
expense.
Lets hope this license issue is addressed in the next release of SQL Server
2005.
Rich
"Stephen Dybing [MSFT]" wrote:
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> <SNIP>
> </SNIP>
> Please be careful about that because, in general, you have to purchase a
> processor license for every processor that the OS can see, whether that
> processor is configured to run SQL Server or not. For more information,
> please refer to the SQL Server 2000 Licensing FAQ web page at
> http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
> Licensing is reproduced below for your benefit.
> Q. Do I have to license all of the processors in a server?
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000 is
> installed.
>
>
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights
2012年2月26日星期日
Full population very slow
I've got SQL2000 Enterprise edition running on windows 2000 advanced server,
we've recently upgraded from SQL sp2 to sp3a, this caused a catalog rebuild
but full population is taking forever. The column with Full text search
enabled is of image type, there are 2.5 million record in the table, about
7GB in size.
I've started full population 2 days ago and it's still showing "Population
in progress", when I view the property page of the catalog, I can see ~
2300000 items, catalog size: 190MB, unique key count: 362586. It appears to
be processing 1000 items per minute.
The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
disks.
mssdmn.exe is the most active process, it's using between 30-40 CPU on one
processor only, disk utilization is minimal and plenty of physical memory
available for use. This process is using 6 threads, 10MB RAM, mssearch.exe is
using 50MB RAM and 16 threads.
Is there any way of speeding up population?
Thanks,
Ad
set sp_fulltext_service 'resource_usage', 5
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
> server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
> rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing "Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
> to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
> is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||Ad,
There are a couple of thing you can do to improve the FT Indexing
performance without stopping & re-locating the FT Catalog. You can set the
MSSearch service's to use one CPU and then use sp_configure to set the
affinity of SQL Server to that cpu to avoid cpu contention during heavy
MSSearch CPU usage via the following method - launch the Tskmgr.exe from the
AT command on the multi-proc server where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.
Additionally, as your server has lots of RAM, you can use
sp_fulltext_service 'resource_usage', 5 to set the MSSearch service to 5
(dedicated), and it will use the max amount of RAM (512Mb), but only if the
RAM is not being used by either the OS or by SQL Server.
Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing
"Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
it need stop and re-start of MSSearch service?
Thanks,
Ad
|||Ad,
I didn't think it would make much difference. However, where you get the
biggest bang for your buck, is ensuring that the FT Catalog is on a separate
disk drive from your database files. Can you stop the Full Population, drop
and re-create the FT Catalog on separate disk array or better yet, on a
separate disk controller? See SQL 2000 BOL title "Full-text Search
Recommendations" for more info on this.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> sp_fulltext_service 'resource_usage', 5 , did not make any difference,
does
> it need stop and re-start of MSSearch service?
> Thanks,
> Ad
>
>
|||The FT Catalog is on a dedicated SAN disk which is almost idle, in fact the
whole disk is cached. So there are plenty of resources on the server that FT
can't utilize, this leads me to believe FT has scalability issues.
Regards,
Ad
"John Kane" wrote:
> Ad,
> I didn't think it would make much difference. However, where you get the
> biggest bang for your buck, is ensuring that the FT Catalog is on a separate
> disk drive from your database files. Can you stop the Full Population, drop
> and re-create the FT Catalog on separate disk array or better yet, on a
> separate disk controller? See SQL 2000 BOL title "Full-text Search
> Recommendations" for more info on this.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Ad" <Ad@.discussions.microsoft.com> wrote in message
> news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> does
>
>
we've recently upgraded from SQL sp2 to sp3a, this caused a catalog rebuild
but full population is taking forever. The column with Full text search
enabled is of image type, there are 2.5 million record in the table, about
7GB in size.
I've started full population 2 days ago and it's still showing "Population
in progress", when I view the property page of the catalog, I can see ~
2300000 items, catalog size: 190MB, unique key count: 362586. It appears to
be processing 1000 items per minute.
The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
disks.
mssdmn.exe is the most active process, it's using between 30-40 CPU on one
processor only, disk utilization is minimal and plenty of physical memory
available for use. This process is using 6 threads, 10MB RAM, mssearch.exe is
using 50MB RAM and 16 threads.
Is there any way of speeding up population?
Thanks,
Ad
set sp_fulltext_service 'resource_usage', 5
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
> server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
> rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing "Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
> to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
> is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||Ad,
There are a couple of thing you can do to improve the FT Indexing
performance without stopping & re-locating the FT Catalog. You can set the
MSSearch service's to use one CPU and then use sp_configure to set the
affinity of SQL Server to that cpu to avoid cpu contention during heavy
MSSearch CPU usage via the following method - launch the Tskmgr.exe from the
AT command on the multi-proc server where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.
Additionally, as your server has lots of RAM, you can use
sp_fulltext_service 'resource_usage', 5 to set the MSSearch service to 5
(dedicated), and it will use the max amount of RAM (512Mb), but only if the
RAM is not being used by either the OS or by SQL Server.
Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing
"Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
it need stop and re-start of MSSearch service?
Thanks,
Ad
|||Ad,
I didn't think it would make much difference. However, where you get the
biggest bang for your buck, is ensuring that the FT Catalog is on a separate
disk drive from your database files. Can you stop the Full Population, drop
and re-create the FT Catalog on separate disk array or better yet, on a
separate disk controller? See SQL 2000 BOL title "Full-text Search
Recommendations" for more info on this.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> sp_fulltext_service 'resource_usage', 5 , did not make any difference,
does
> it need stop and re-start of MSSearch service?
> Thanks,
> Ad
>
>
|||The FT Catalog is on a dedicated SAN disk which is almost idle, in fact the
whole disk is cached. So there are plenty of resources on the server that FT
can't utilize, this leads me to believe FT has scalability issues.
Regards,
Ad
"John Kane" wrote:
> Ad,
> I didn't think it would make much difference. However, where you get the
> biggest bang for your buck, is ensuring that the FT Catalog is on a separate
> disk drive from your database files. Can you stop the Full Population, drop
> and re-create the FT Catalog on separate disk array or better yet, on a
> separate disk controller? See SQL 2000 BOL title "Full-text Search
> Recommendations" for more info on this.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Ad" <Ad@.discussions.microsoft.com> wrote in message
> news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> does
>
>
Full population just hangs...unique count and item count don't move
I create a catalog. The exact same database tables work in another
environment for the full population.
But this server when I do a full population...it just hangs and says
population in progress.
However the item count, unique key count and catalog size does not
grow.
There's nothing in the application logs.
Where else can I look?
I'll also notice on the server where the population works...under the
properties for the catalog...it says the location of the catalog is on
the C:\program files\microsoft sql server\mssql\ftdata.
But there's nothing in the folder. The catalog files are actually on
D:\mssql\FtDATA.
Is that normal?
Also...the status just shows paused after I start the repopulation.
I don't see anything in the gather logs either.
mchi55@.hotmail.com wrote:
> I create a catalog. The exact same database tables work in another
> environment for the full population.
> But this server when I do a full population...it just hangs and says
> population in progress.
> However the item count, unique key count and catalog size does not
> grow.
> There's nothing in the application logs.
> Where else can I look?
> I'll also notice on the server where the population works...under the
> properties for the catalog...it says the location of the catalog is on
> the C:\program files\microsoft sql server\mssql\ftdata.
> But there's nothing in the folder. The catalog files are actually on
> D:\mssql\FtDATA.
> Is that normal?
|||One more piece of info...
I tried creating a full text index in the Northwind database...and that
won't populate either with the same issues.
Perhaps there's something wrong with the MSSearch?
Is there something to reinstall it? Any more ideas on how to
troubleshoot?
mchi55@.hotmail.com wrote:[vbcol=seagreen]
> Also...the status just shows paused after I start the repopulation.
> I don't see anything in the gather logs either.
> mchi55@.hotmail.com wrote:
|||When you say there is nothing in C:\Program Files\Microsoft SQL
Server\MSSQL\FTDATA, there should be a folder within this directory called
SQLServer.
It sounds like there is something badly wrong here. Check again to verify
that there is nothing in the application log from MSSCI or MSSearch. If not
I suggest you re-install.
Follow the below for more info on how to do this.
http://support.microsoft.com/kb/827449/en-us
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<mchi55@.hotmail.com> wrote in message
news:1162875462.965441.327440@.e3g2000cwe.googlegro ups.com...
>I create a catalog. The exact same database tables work in another
> environment for the full population.
> But this server when I do a full population...it just hangs and says
> population in progress.
> However the item count, unique key count and catalog size does not
> grow.
> There's nothing in the application logs.
> Where else can I look?
> I'll also notice on the server where the population works...under the
> properties for the catalog...it says the location of the catalog is on
> the C:\program files\microsoft sql server\mssql\ftdata.
> But there's nothing in the folder. The catalog files are actually on
> D:\mssql\FtDATA.
> Is that normal?
>
environment for the full population.
But this server when I do a full population...it just hangs and says
population in progress.
However the item count, unique key count and catalog size does not
grow.
There's nothing in the application logs.
Where else can I look?
I'll also notice on the server where the population works...under the
properties for the catalog...it says the location of the catalog is on
the C:\program files\microsoft sql server\mssql\ftdata.
But there's nothing in the folder. The catalog files are actually on
D:\mssql\FtDATA.
Is that normal?
Also...the status just shows paused after I start the repopulation.
I don't see anything in the gather logs either.
mchi55@.hotmail.com wrote:
> I create a catalog. The exact same database tables work in another
> environment for the full population.
> But this server when I do a full population...it just hangs and says
> population in progress.
> However the item count, unique key count and catalog size does not
> grow.
> There's nothing in the application logs.
> Where else can I look?
> I'll also notice on the server where the population works...under the
> properties for the catalog...it says the location of the catalog is on
> the C:\program files\microsoft sql server\mssql\ftdata.
> But there's nothing in the folder. The catalog files are actually on
> D:\mssql\FtDATA.
> Is that normal?
|||One more piece of info...
I tried creating a full text index in the Northwind database...and that
won't populate either with the same issues.
Perhaps there's something wrong with the MSSearch?
Is there something to reinstall it? Any more ideas on how to
troubleshoot?
mchi55@.hotmail.com wrote:[vbcol=seagreen]
> Also...the status just shows paused after I start the repopulation.
> I don't see anything in the gather logs either.
> mchi55@.hotmail.com wrote:
|||When you say there is nothing in C:\Program Files\Microsoft SQL
Server\MSSQL\FTDATA, there should be a folder within this directory called
SQLServer.
It sounds like there is something badly wrong here. Check again to verify
that there is nothing in the application log from MSSCI or MSSearch. If not
I suggest you re-install.
Follow the below for more info on how to do this.
http://support.microsoft.com/kb/827449/en-us
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<mchi55@.hotmail.com> wrote in message
news:1162875462.965441.327440@.e3g2000cwe.googlegro ups.com...
>I create a catalog. The exact same database tables work in another
> environment for the full population.
> But this server when I do a full population...it just hangs and says
> population in progress.
> However the item count, unique key count and catalog size does not
> grow.
> There's nothing in the application logs.
> Where else can I look?
> I'll also notice on the server where the population works...under the
> properties for the catalog...it says the location of the catalog is on
> the C:\program files\microsoft sql server\mssql\ftdata.
> But there's nothing in the folder. The catalog files are actually on
> D:\mssql\FtDATA.
> Is that normal?
>
Full Population - completes but never ends
We rebuilt a catalog this weekend after upgrading to Adobe iFilter
version 6. Have attempted the rebuild twice, both times the result has
been that all rows are indexed, but the population never 'ends'. The
status remains at '1 - Full Population in Progress'. The item count
indicates 74,926 - which is one more than our (current) row count of
74,925. The mssearch and mssdmn processes continue to consume CPU
(~50%).
We're on Win2K, Sql2K, sp3A. There do not appear to be any error
messages in the event log.
The catalog contains one table, and it is configured with Change
Tracking. The table has one indexed column, an image column containing
primarily Word and PDF binaries.
Any help/suggestions appreciated!
Dave Hensler
dhensler@.prestongates.com
The item count reflects the numbers of rows being indexed and the numbers of
tables being full text indexed, so the number seems correct.
Can you use profiler to see if anything is being pulled? Look for events
coming with an Application Name of PKM.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave Hensler" <dhensler@.prestongates.com> wrote in message
news:1118069691.983721.165480@.o13g2000cwo.googlegr oups.com...
> We rebuilt a catalog this weekend after upgrading to Adobe iFilter
> version 6. Have attempted the rebuild twice, both times the result has
> been that all rows are indexed, but the population never 'ends'. The
> status remains at '1 - Full Population in Progress'. The item count
> indicates 74,926 - which is one more than our (current) row count of
> 74,925. The mssearch and mssdmn processes continue to consume CPU
> (~50%).
> We're on Win2K, Sql2K, sp3A. There do not appear to be any error
> messages in the event log.
> The catalog contains one table, and it is configured with Change
> Tracking. The table has one indexed column, an image column containing
> primarily Word and PDF binaries.
> Any help/suggestions appreciated!
> Dave Hensler
> dhensler@.prestongates.com
>
|||Profiler shows lots of these under PKM:
exec sp_fulltext_getdata 29, 1835153583, 0x00002D31, 1
They are firing many times per second and seem to be originating in the
master database.
|||This is interesting - I have found 3 pdf files in the FTDATA directory
that are corrupt. They will not open successfully in Acrobat. It
looks like they have been there for about 29 hours.
Seems reasonable to try to remove these files from the database and see
if it frees up the search service. However, I need to identify the
rows containing these binaries. The file names have the form
'0000001f7d78a4e70000BF9D00000003.pdf'. Any idea how to locate these
in the table? Or is there another log that may identify the row where
these binaries came from?
Thanks for any advice.
|||This means that the population is still in progress.
go to
http://msdn.microsoft.com/library/de...tml/sp04f9.asp
Download the code sample and run it in VB 6. It will tell you where you are
in your population.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave Hensler" <dhensler@.prestongates.com> wrote in message
news:1118078599.081054.208540@.g49g2000cwa.googlegr oups.com...
> Profiler shows lots of these under PKM:
> exec sp_fulltext_getdata 29, 1835153583, 0x00002D31, 1
> They are firing many times per second and seem to be originating in the
> master database.
>
|||Thanks for the tool, Hilary. Using it I can see that the population is
cycling within a small set of database rows. The rows are not
necessarily recent, in fact they are spread all over the table. I've
looked at the individual binaries and do not see any pattern - some are
newer versions of the pdf file format, some are older, etc.
Have you seen anything like this before? How about Adobe's latest
filter (ifilter 6.0) - any known issues?
Thanks - Dave
|||The problem seems to be solved. The indexer was repeatedly cycling
over 5 rows containing pdf binaries. Once the 5 rows were identified
and the binaries removed from the table, the indexer went into the Idle
state. When they are reinserted into the table, the indexer hangs up
again until they are removed. These pdf binaries do not appear to be
damaged or unusual when they are opened in Acrobat, however.
version 6. Have attempted the rebuild twice, both times the result has
been that all rows are indexed, but the population never 'ends'. The
status remains at '1 - Full Population in Progress'. The item count
indicates 74,926 - which is one more than our (current) row count of
74,925. The mssearch and mssdmn processes continue to consume CPU
(~50%).
We're on Win2K, Sql2K, sp3A. There do not appear to be any error
messages in the event log.
The catalog contains one table, and it is configured with Change
Tracking. The table has one indexed column, an image column containing
primarily Word and PDF binaries.
Any help/suggestions appreciated!
Dave Hensler
dhensler@.prestongates.com
The item count reflects the numbers of rows being indexed and the numbers of
tables being full text indexed, so the number seems correct.
Can you use profiler to see if anything is being pulled? Look for events
coming with an Application Name of PKM.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave Hensler" <dhensler@.prestongates.com> wrote in message
news:1118069691.983721.165480@.o13g2000cwo.googlegr oups.com...
> We rebuilt a catalog this weekend after upgrading to Adobe iFilter
> version 6. Have attempted the rebuild twice, both times the result has
> been that all rows are indexed, but the population never 'ends'. The
> status remains at '1 - Full Population in Progress'. The item count
> indicates 74,926 - which is one more than our (current) row count of
> 74,925. The mssearch and mssdmn processes continue to consume CPU
> (~50%).
> We're on Win2K, Sql2K, sp3A. There do not appear to be any error
> messages in the event log.
> The catalog contains one table, and it is configured with Change
> Tracking. The table has one indexed column, an image column containing
> primarily Word and PDF binaries.
> Any help/suggestions appreciated!
> Dave Hensler
> dhensler@.prestongates.com
>
|||Profiler shows lots of these under PKM:
exec sp_fulltext_getdata 29, 1835153583, 0x00002D31, 1
They are firing many times per second and seem to be originating in the
master database.
|||This is interesting - I have found 3 pdf files in the FTDATA directory
that are corrupt. They will not open successfully in Acrobat. It
looks like they have been there for about 29 hours.
Seems reasonable to try to remove these files from the database and see
if it frees up the search service. However, I need to identify the
rows containing these binaries. The file names have the form
'0000001f7d78a4e70000BF9D00000003.pdf'. Any idea how to locate these
in the table? Or is there another log that may identify the row where
these binaries came from?
Thanks for any advice.
|||This means that the population is still in progress.
go to
http://msdn.microsoft.com/library/de...tml/sp04f9.asp
Download the code sample and run it in VB 6. It will tell you where you are
in your population.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave Hensler" <dhensler@.prestongates.com> wrote in message
news:1118078599.081054.208540@.g49g2000cwa.googlegr oups.com...
> Profiler shows lots of these under PKM:
> exec sp_fulltext_getdata 29, 1835153583, 0x00002D31, 1
> They are firing many times per second and seem to be originating in the
> master database.
>
|||Thanks for the tool, Hilary. Using it I can see that the population is
cycling within a small set of database rows. The rows are not
necessarily recent, in fact they are spread all over the table. I've
looked at the individual binaries and do not see any pattern - some are
newer versions of the pdf file format, some are older, etc.
Have you seen anything like this before? How about Adobe's latest
filter (ifilter 6.0) - any known issues?
Thanks - Dave
|||The problem seems to be solved. The indexer was repeatedly cycling
over 5 rows containing pdf binaries. Once the 5 rows were identified
and the binaries removed from the table, the indexer went into the Idle
state. When they are reinserted into the table, the indexer hangs up
again until they are removed. These pdf binaries do not appear to be
damaged or unusual when they are opened in Acrobat, however.
2012年2月19日星期日
FTS Index Build on SQL 2000 and performance questions
I was under the impression that among the improvements in FTS under SQL 2000
was faster population of an index.
I started a full text index on a table Monday evening at around 6:00PM.
30 hours later it's at an item count 391678. (actually that's about where
it was 12 hours ago as I recall.)
In any case, this is as slow, if not SLOWER than the SQL 7.0 box we were
running this on.
And this box has better CPUs and presumably better disks (not sure about the
latter.)
There's nothing in the logs that appear to show anything stuck or hung.
We're looking to move our application to SQL 2000, but if it takes as long
to build indices on SQL 2000 as it does on SQL 7.0, we'll have to look at a
3rd party package.
At this it really looks like nothing's happening.
This copy of the DB was restored from our production server. Would that
make a difference?
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.com
Greg,
Yes, that is true. Although it was not as significant as SQL 2000 to SQL
2005 will be. Primarily, the improvements from SQL Sever 7.0 FTS to SQL
Server 2000 FTS were made in the areas of additional reliability and added
features.
For a 400K item count, it should of completed well before 12 hours. Could
you check your server's Application event log for any "Microsoft Search" and
especially MssCi (review the details for stack traces) source events -
errors, warnings AND informational and post anything that seems out of the
ordinary. If nothing is obvious, then it is possible that the true log entry
may of rolled off your server's application event log, if so, then clear the
log and stop the FT Population if it is in progress, and re-run a Full
Population.
Yes, depending upon how the DB was restored (from a backup or via Copy DB
Wizard, DTS, etc.) as well as any differences in hardware configurations
(especially a difference in the drive letters on where the FT Catalogs
resided) can affect and cause problems.
Regards,
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:VXgkd.95690$l07.9241@.twister.nyroc.rr.com...
> I was under the impression that among the improvements in FTS under SQL
2000
> was faster population of an index.
> I started a full text index on a table Monday evening at around 6:00PM.
> 30 hours later it's at an item count 391678. (actually that's about where
> it was 12 hours ago as I recall.)
> In any case, this is as slow, if not SLOWER than the SQL 7.0 box we were
> running this on.
> And this box has better CPUs and presumably better disks (not sure about
the
> latter.)
> There's nothing in the logs that appear to show anything stuck or hung.
> We're looking to move our application to SQL 2000, but if it takes as long
> to build indices on SQL 2000 as it does on SQL 7.0, we'll have to look at
a
> 3rd party package.
> At this it really looks like nothing's happening.
> This copy of the DB was restored from our production server. Would that
> make a difference?
>
> --
> --
> Greg D. Moore
> President Green Mountain Software
> Personal: http://stratton.greenms.com
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:uMTtEHuxEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Greg,
> Yes, that is true. Although it was not as significant as SQL 2000 to SQL
> 2005 will be. Primarily, the improvements from SQL Sever 7.0 FTS to SQL
> Server 2000 FTS were made in the areas of additional reliability and added
> features.
Well, I can't wait until SQL 2005 unfortunately. :-)
> For a 400K item count, it should of completed well before 12 hours.
That's what I thought.
>Could
> you check your server's Application event log for any "Microsoft Search"
and
> especially MssCi (review the details for stack traces) source events -
> errors, warnings AND informational and post anything that seems out of the
> ordinary. If nothing is obvious, then it is possible that the true log
entry
> may of rolled off your server's application event log, if so, then clear
the
> log and stop the FT Population if it is in progress, and re-run a Full
> Population.
Nope, as I stated, there's nothing in there out of the ordinary. Only real
event was a master merge right after midnight last night.
> Yes, depending upon how the DB was restored (from a backup or via Copy DB
> Wizard, DTS, etc.) as well as any differences in hardware configurations
> (especially a difference in the drive letters on where the FT Catalogs
> resided) can affect and cause problems.
>
It was a restore from a backup. Does an update stats change things as far
as FT Index is concerned?
In any case, since my first post I gave up, cancelled the obviously failling
population and moved the index to a different drive on the server.
In 20 minutes it's already done 113K rows. So, this is FAR better than what
I was seeing with my first attempt. At this point if it continues like
this, I think performance will be adequate for our needs.
I'll keep you posted on this build.
> Regards,
> John
>
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:VXgkd.95690$l07.9241@.twister.nyroc.rr.com...
> 2000
where[vbcol=seagreen]
> the
long[vbcol=seagreen]
at
> a
>
|||Greg,
Yea, we all have been waiting for Yukon, but alas it's been delayed now for
years...
Q. Does an update stats change things as far as FT Index is concerned?
A. No. update stats are db only and do not affect the FT Index as most
likely the FT Indexing process was "paused", and the posted app. log message
rolled off the event log.
Regards,
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Mshkd.1$qv5.0@.twister.nyroc.rr.com...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uMTtEHuxEHA.1204@.TK2MSFTNGP10.phx.gbl...
added[vbcol=seagreen]
> Well, I can't wait until SQL 2005 unfortunately. :-)
>
> That's what I thought.
> and
the
> entry
> the
> Nope, as I stated, there's nothing in there out of the ordinary. Only
real[vbcol=seagreen]
> event was a master merge right after midnight last night.
>
DB
> It was a restore from a backup. Does an update stats change things as far
> as FT Index is concerned?
> In any case, since my first post I gave up, cancelled the obviously
failling
> population and moved the index to a different drive on the server.
> In 20 minutes it's already done 113K rows. So, this is FAR better than
what[vbcol=seagreen]
> I was seeing with my first attempt. At this point if it continues like
> this, I think performance will be adequate for our needs.
> I'll keep you posted on this build.
>
> message
SQL[vbcol=seagreen]
6:00PM.[vbcol=seagreen]
> where
were[vbcol=seagreen]
about[vbcol=seagreen]
hung.[vbcol=seagreen]
> long
> at
that
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:ue7E0muxEHA.2568@.TK2MSFTNGP10.phx.gbl...
> Greg,
> Yea, we all have been waiting for Yukon, but alas it's been delayed now
for
> years...
> Q. Does an update stats change things as far as FT Index is concerned?
> A. No. update stats are db only and do not affect the FT Index as most
> likely the FT Indexing process was "paused", and the posted app. log
message
> rolled off the event log.
John, I'll repeat myself again. NOTHING has rolled off the application log.
In any case, I'm not suspecting a DB issue since it started great (doing on
average 6000 rows/minute). In the first 1/2 hour it did over 180K rows.
That was at 12:20 AM.
Since then it appears stuck at 371952 and the CPU usage has dropped to
basically zero. (when it ran for the 1/2 hour I observed it, CPU usage was
about 100% across all 4 CPUs).
The PopulateStatus is still 1 and the size of the errorlog appears to be 103
bytes.
I'm starting to suspect it's the DB, not the FT, but just want to make sure
to see if there's anything else I should be looking at.
|||Greg,
Ok, is it possible that you could save the current Application event log as
a .evt file as well as all non-zero length *.gthr files located under
\FTDATA\SQLServer\GatherLogs and zip these files together and post it here?
If there has been no events roll-off, then an event should be there, and as
Albert Einstein (said?) "God [or the devil] is in the details" as it does
appear that this process is stuck (or paused) for some reason at 371,952,
which is very close to the number (391,678) that you originally posted.
The CPU usage is usually low, except when there are Shadow Merges and at the
end of the Full Population during the Master Merge. A PopulateStatus of 1
means that the "Full population in progress", so hopefully a MSSearch or
MssCi source event will be present to help explain this!
Thanks,
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:dXmkd.223$qv5.36@.twister.nyroc.rr.com...
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ue7E0muxEHA.2568@.TK2MSFTNGP10.phx.gbl...
> for
> message
> John, I'll repeat myself again. NOTHING has rolled off the application
log.
> In any case, I'm not suspecting a DB issue since it started great (doing
on
> average 6000 rows/minute). In the first 1/2 hour it did over 180K rows.
> That was at 12:20 AM.
> Since then it appears stuck at 371952 and the CPU usage has dropped to
> basically zero. (when it ran for the 1/2 hour I observed it, CPU usage
was
> about 100% across all 4 CPUs).
> The PopulateStatus is still 1 and the size of the errorlog appears to be
103
> bytes.
> I'm starting to suspect it's the DB, not the FT, but just want to make
sure
> to see if there's anything else I should be looking at.
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:eznFI00xEHA.1192@.tk2msftngp13.phx.gbl...
> Greg,
> Ok, is it possible that you could save the current Application event log
as
> a .evt file as well as all non-zero length *.gthr files located under
> \FTDATA\SQLServer\GatherLogs and zip these files together and post it
here?
> If there has been no events roll-off, then an event should be there, and
as
> Albert Einstein (said?) "God [or the devil] is in the details" as it does
> appear that this process is stuck (or paused) for some reason at 371,952,
> which is very close to the number (391,678) that you originally posted.
Trust me John, there was NOTHING in the Event log. :-) I can read an event
log.
I also checked the Gatherlogs and nothing was out of the ordinary.
HOWEVER... when I manually stop the population and checked the event log
THEN I got an error.
"The crawl seed <MSSQL75://SQLServer/04659998> in project <SQLServer
SQL0001000007> cannot be accessed. Error: 80040d19 - The filtering process
was stopped because its memory quota was exceeded. To increase the memory
quota of the filtering process, increase the value for the following
registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\Search\1.0\G athering
Manager\FilterProcessMemoryQuota. .
There's not much in the KB about this, but
http://support.microsoft.com/default...b;en-us;308771 appeared to
be close.
So I upped it from 1900000 to 2900000. So far things are working better.
(I did have to rebuild the catalog, but that may be a small price to pay on
a test box. :-)
It's up to 712417 records indexed and still going.
Yeah, I was struck by the total number in the earlier two runs being fairly
close. Not sure if it's coincidence or not.
If this population is still working as of tomorrow I'll call this one
closed.
> The CPU usage is usually low, except when there are Shadow Merges and at
the
> end of the Full Population during the Master Merge. A PopulateStatus of 1
> means that the "Full population in progress", so hopefully a MSSearch or
> MssCi source event will be present to help explain this!
Hopefully, but like I say, there was nothing until I manually stopped it.
Anyway, thanks for the help so far.
> Thanks,
> John
>
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:dXmkd.223$qv5.36@.twister.nyroc.rr.com...
now
> log.
> on
> was
> 103
> sure
>
|||Greg,
Ok, while you may be able to read (all events), many who post here do not
seem to be able to do so... ;-)
Stopping the Full Population and then reviewing the event log was a very
good idea! Are you FT Indexing large (MS Office, Adobe PDF or other file
types?) files stored in an IMAGE column? The KB article doesn't specifically
mention Error: 80040d19, but the recently enhanced (SP3) message text does
make reference to the FilterProcessMemoryQuota registry key, so a connection
between the error and fix could be made, so I will doubt you no moore :-)
I'm not sure how many rows you have left to complete past the 712,417 mark,
but monitor it closely and if it appears to hang again, stop it and check
the event log, if you get the same message increase the
FilterProcessMemoryQuota value. What type of files are you FT Indexing and
what is their avg. and max sizes?
Good Catch!
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ZQBkd.468$qv5.49@.twister.nyroc.rr.com...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:eznFI00xEHA.1192@.tk2msftngp13.phx.gbl...
> as
> here?
> as
does[vbcol=seagreen]
371,952,
> Trust me John, there was NOTHING in the Event log. :-) I can read an
event
> log.
> I also checked the Gatherlogs and nothing was out of the ordinary.
> HOWEVER... when I manually stop the population and checked the event log
> THEN I got an error.
> "The crawl seed <MSSQL75://SQLServer/04659998> in project <SQLServer
> SQL0001000007> cannot be accessed. Error: 80040d19 - The filtering process
> was stopped because its memory quota was exceeded. To increase the memory
> quota of the filtering process, increase the value for the following
> registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\Search\1.0\G athering
> Manager\FilterProcessMemoryQuota. .
> There's not much in the KB about this, but
> http://support.microsoft.com/default...b;en-us;308771 appeared to
> be close.
> So I upped it from 1900000 to 2900000. So far things are working better.
> (I did have to rebuild the catalog, but that may be a small price to pay
on
> a test box. :-)
> It's up to 712417 records indexed and still going.
> Yeah, I was struck by the total number in the earlier two runs being
fairly[vbcol=seagreen]
> close. Not sure if it's coincidence or not.
> If this population is still working as of tomorrow I'll call this one
> closed.
>
> the
1[vbcol=seagreen]
> Hopefully, but like I say, there was nothing until I manually stopped it.
> Anyway, thanks for the help so far.
>
> message
> now
concerned?[vbcol=seagreen]
most[vbcol=seagreen]
application[vbcol=seagreen]
(doing[vbcol=seagreen]
rows.[vbcol=seagreen]
usage[vbcol=seagreen]
be
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:eXHkxK7xEHA.3808@.TK2MSFTNGP15.phx.gbl...
> Greg,
> Ok, while you may be able to read (all events), many who post here do not
> seem to be able to do so... ;-)
> Stopping the Full Population and then reviewing the event log was a very
> good idea! Are you FT Indexing large (MS Office, Adobe PDF or other file
> types?) files stored in an IMAGE column? The KB article doesn't
specifically
> mention Error: 80040d19, but the recently enhanced (SP3) message text does
> make reference to the FilterProcessMemoryQuota registry key, so a
connection
> between the error and fix could be made, so I will doubt you no moore :-)
I have about 8 million rows in this table.
Interestingly enough, last night it appeared to stop at that 712,417 mark
right after I posted.
In fact it was still there when I woke up this morning.
And then I checked a few hours later, it was up over 1 million records and
climbing. But it appears to have done NOTHING for about 8-10 hours. Very
strange.
It's now up to 2,419,059 and still climbing.
> I'm not sure how many rows you have left to complete past the 712,417
mark,
> but monitor it closely and if it appears to hang again, stop it and check
> the event log, if you get the same message increase the
> FilterProcessMemoryQuota value. What type of files are you FT Indexing and
> what is their avg. and max sizes?
>
Not really files, but data uploaded from documents such as Word files,
Quark, Wordperfect, text files, etc.
Not sure avg size, but probably several meg on average. Total table size
is about 44 Gig. The text column is by far the biggest part of the index
(we also have some varchars in there), so I'd guess about 4-5 meg each.
> Good Catch!
> John
>
|||Greg,
Good, so at least you're above the 2.4 million row mark now, with much to
still be processed. Now, that I know you have an 8 million row table, I can
give you a ball park timeframe for when this might take to complete, but
keep in mind that it is a SWAG. As 1 million rows on a poorly configured
server (database & FT Catalog sharing the same disk drive, with 1 CPU and
1GB of RAM) can take up to 14 hours to complete, so expect a similarly
configured server to finish FT Indexing of a table with 8 million rows in
approx. 4 to 5 days. Of course, your mileage may vary and your server's
actual configuration (software & hardware) may be different...
Also, you say that you are not FT Indexing files (i.e.. binary files stored
in an IMAGE datatype), but are in fact storing the text data uploaded from
documents such as Word files, Quark, Wordperfect, text files, etc., so this
textual data is stored in a column defined with the TEXT (or NTEXT)
datatype. Correct? If so, you can get the max and avg metadata via the
following SQL query :
select avg(datalength(Text_column_name)) "Avg. Text Length" from
8_million_row_table_name
select max(datalength(Text_column_name)) "Max. Text Length" from
8_million_row_table_name
While the Text datatype certainly supports well above 4-5 Mb of text in each
row, I'm fairly certain that this is well above what is typical for most FT
Indexing applications and my lie at the heart of the problem. While the BOL
title "Filtering Supported File Types" is referencing "documents" stored in
an IMAGE datatype, when it is stated that "For full-text indexing, a
document must be less than 16 megabytes (MB) in size and must not contain
more than 256 kilobytes (KB) of filtered text." This statement *may* also
apply to plain text stored in a TEXT or NTEXT datatype and this is why
getting both the avg. and especially max values would be most helpful in
understanding this issue.
FYI, I suspect that you've read the SQL 2000 BOL title "Full-text Search
Recommendations" as well as the SQL 2000 FTS deployment white paper at:
http://support.microsoft.com/default...b;en-us;323739 both speak to
software & hardware configuration issues when used with SQL Server 2000 FT
Indexing of tables over 1 million rows.
Thanks,
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:DPWkd.3206$qv5.2464@.twister.nyroc.rr.com...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:eXHkxK7xEHA.3808@.TK2MSFTNGP15.phx.gbl...
not[vbcol=seagreen]
> specifically
does[vbcol=seagreen]
> connection
:-)[vbcol=seagreen]
> I have about 8 million rows in this table.
> Interestingly enough, last night it appeared to stop at that 712,417 mark
> right after I posted.
> In fact it was still there when I woke up this morning.
> And then I checked a few hours later, it was up over 1 million records and
> climbing. But it appears to have done NOTHING for about 8-10 hours. Very
> strange.
> It's now up to 2,419,059 and still climbing.
> mark,
check[vbcol=seagreen]
and
> Not really files, but data uploaded from documents such as Word files,
> Quark, Wordperfect, text files, etc.
> Not sure avg size, but probably several meg on average. Total table size
> is about 44 Gig. The text column is by far the biggest part of the index
> (we also have some varchars in there), so I'd guess about 4-5 meg each.
>
>
was faster population of an index.
I started a full text index on a table Monday evening at around 6:00PM.
30 hours later it's at an item count 391678. (actually that's about where
it was 12 hours ago as I recall.)
In any case, this is as slow, if not SLOWER than the SQL 7.0 box we were
running this on.
And this box has better CPUs and presumably better disks (not sure about the
latter.)
There's nothing in the logs that appear to show anything stuck or hung.
We're looking to move our application to SQL 2000, but if it takes as long
to build indices on SQL 2000 as it does on SQL 7.0, we'll have to look at a
3rd party package.
At this it really looks like nothing's happening.
This copy of the DB was restored from our production server. Would that
make a difference?
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.com
Greg,
Yes, that is true. Although it was not as significant as SQL 2000 to SQL
2005 will be. Primarily, the improvements from SQL Sever 7.0 FTS to SQL
Server 2000 FTS were made in the areas of additional reliability and added
features.
For a 400K item count, it should of completed well before 12 hours. Could
you check your server's Application event log for any "Microsoft Search" and
especially MssCi (review the details for stack traces) source events -
errors, warnings AND informational and post anything that seems out of the
ordinary. If nothing is obvious, then it is possible that the true log entry
may of rolled off your server's application event log, if so, then clear the
log and stop the FT Population if it is in progress, and re-run a Full
Population.
Yes, depending upon how the DB was restored (from a backup or via Copy DB
Wizard, DTS, etc.) as well as any differences in hardware configurations
(especially a difference in the drive letters on where the FT Catalogs
resided) can affect and cause problems.
Regards,
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:VXgkd.95690$l07.9241@.twister.nyroc.rr.com...
> I was under the impression that among the improvements in FTS under SQL
2000
> was faster population of an index.
> I started a full text index on a table Monday evening at around 6:00PM.
> 30 hours later it's at an item count 391678. (actually that's about where
> it was 12 hours ago as I recall.)
> In any case, this is as slow, if not SLOWER than the SQL 7.0 box we were
> running this on.
> And this box has better CPUs and presumably better disks (not sure about
the
> latter.)
> There's nothing in the logs that appear to show anything stuck or hung.
> We're looking to move our application to SQL 2000, but if it takes as long
> to build indices on SQL 2000 as it does on SQL 7.0, we'll have to look at
a
> 3rd party package.
> At this it really looks like nothing's happening.
> This copy of the DB was restored from our production server. Would that
> make a difference?
>
> --
> --
> Greg D. Moore
> President Green Mountain Software
> Personal: http://stratton.greenms.com
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:uMTtEHuxEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Greg,
> Yes, that is true. Although it was not as significant as SQL 2000 to SQL
> 2005 will be. Primarily, the improvements from SQL Sever 7.0 FTS to SQL
> Server 2000 FTS were made in the areas of additional reliability and added
> features.
Well, I can't wait until SQL 2005 unfortunately. :-)
> For a 400K item count, it should of completed well before 12 hours.
That's what I thought.
>Could
> you check your server's Application event log for any "Microsoft Search"
and
> especially MssCi (review the details for stack traces) source events -
> errors, warnings AND informational and post anything that seems out of the
> ordinary. If nothing is obvious, then it is possible that the true log
entry
> may of rolled off your server's application event log, if so, then clear
the
> log and stop the FT Population if it is in progress, and re-run a Full
> Population.
Nope, as I stated, there's nothing in there out of the ordinary. Only real
event was a master merge right after midnight last night.
> Yes, depending upon how the DB was restored (from a backup or via Copy DB
> Wizard, DTS, etc.) as well as any differences in hardware configurations
> (especially a difference in the drive letters on where the FT Catalogs
> resided) can affect and cause problems.
>
It was a restore from a backup. Does an update stats change things as far
as FT Index is concerned?
In any case, since my first post I gave up, cancelled the obviously failling
population and moved the index to a different drive on the server.
In 20 minutes it's already done 113K rows. So, this is FAR better than what
I was seeing with my first attempt. At this point if it continues like
this, I think performance will be adequate for our needs.
I'll keep you posted on this build.
> Regards,
> John
>
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:VXgkd.95690$l07.9241@.twister.nyroc.rr.com...
> 2000
where[vbcol=seagreen]
> the
long[vbcol=seagreen]
at
> a
>
|||Greg,
Yea, we all have been waiting for Yukon, but alas it's been delayed now for
years...
Q. Does an update stats change things as far as FT Index is concerned?
A. No. update stats are db only and do not affect the FT Index as most
likely the FT Indexing process was "paused", and the posted app. log message
rolled off the event log.
Regards,
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Mshkd.1$qv5.0@.twister.nyroc.rr.com...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uMTtEHuxEHA.1204@.TK2MSFTNGP10.phx.gbl...
added[vbcol=seagreen]
> Well, I can't wait until SQL 2005 unfortunately. :-)
>
> That's what I thought.
> and
the
> entry
> the
> Nope, as I stated, there's nothing in there out of the ordinary. Only
real[vbcol=seagreen]
> event was a master merge right after midnight last night.
>
DB
> It was a restore from a backup. Does an update stats change things as far
> as FT Index is concerned?
> In any case, since my first post I gave up, cancelled the obviously
failling
> population and moved the index to a different drive on the server.
> In 20 minutes it's already done 113K rows. So, this is FAR better than
what[vbcol=seagreen]
> I was seeing with my first attempt. At this point if it continues like
> this, I think performance will be adequate for our needs.
> I'll keep you posted on this build.
>
> message
SQL[vbcol=seagreen]
6:00PM.[vbcol=seagreen]
> where
were[vbcol=seagreen]
about[vbcol=seagreen]
hung.[vbcol=seagreen]
> long
> at
that
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:ue7E0muxEHA.2568@.TK2MSFTNGP10.phx.gbl...
> Greg,
> Yea, we all have been waiting for Yukon, but alas it's been delayed now
for
> years...
> Q. Does an update stats change things as far as FT Index is concerned?
> A. No. update stats are db only and do not affect the FT Index as most
> likely the FT Indexing process was "paused", and the posted app. log
message
> rolled off the event log.
John, I'll repeat myself again. NOTHING has rolled off the application log.
In any case, I'm not suspecting a DB issue since it started great (doing on
average 6000 rows/minute). In the first 1/2 hour it did over 180K rows.
That was at 12:20 AM.
Since then it appears stuck at 371952 and the CPU usage has dropped to
basically zero. (when it ran for the 1/2 hour I observed it, CPU usage was
about 100% across all 4 CPUs).
The PopulateStatus is still 1 and the size of the errorlog appears to be 103
bytes.
I'm starting to suspect it's the DB, not the FT, but just want to make sure
to see if there's anything else I should be looking at.
|||Greg,
Ok, is it possible that you could save the current Application event log as
a .evt file as well as all non-zero length *.gthr files located under
\FTDATA\SQLServer\GatherLogs and zip these files together and post it here?
If there has been no events roll-off, then an event should be there, and as
Albert Einstein (said?) "God [or the devil] is in the details" as it does
appear that this process is stuck (or paused) for some reason at 371,952,
which is very close to the number (391,678) that you originally posted.
The CPU usage is usually low, except when there are Shadow Merges and at the
end of the Full Population during the Master Merge. A PopulateStatus of 1
means that the "Full population in progress", so hopefully a MSSearch or
MssCi source event will be present to help explain this!
Thanks,
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:dXmkd.223$qv5.36@.twister.nyroc.rr.com...
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ue7E0muxEHA.2568@.TK2MSFTNGP10.phx.gbl...
> for
> message
> John, I'll repeat myself again. NOTHING has rolled off the application
log.
> In any case, I'm not suspecting a DB issue since it started great (doing
on
> average 6000 rows/minute). In the first 1/2 hour it did over 180K rows.
> That was at 12:20 AM.
> Since then it appears stuck at 371952 and the CPU usage has dropped to
> basically zero. (when it ran for the 1/2 hour I observed it, CPU usage
was
> about 100% across all 4 CPUs).
> The PopulateStatus is still 1 and the size of the errorlog appears to be
103
> bytes.
> I'm starting to suspect it's the DB, not the FT, but just want to make
sure
> to see if there's anything else I should be looking at.
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:eznFI00xEHA.1192@.tk2msftngp13.phx.gbl...
> Greg,
> Ok, is it possible that you could save the current Application event log
as
> a .evt file as well as all non-zero length *.gthr files located under
> \FTDATA\SQLServer\GatherLogs and zip these files together and post it
here?
> If there has been no events roll-off, then an event should be there, and
as
> Albert Einstein (said?) "God [or the devil] is in the details" as it does
> appear that this process is stuck (or paused) for some reason at 371,952,
> which is very close to the number (391,678) that you originally posted.
Trust me John, there was NOTHING in the Event log. :-) I can read an event
log.
I also checked the Gatherlogs and nothing was out of the ordinary.
HOWEVER... when I manually stop the population and checked the event log
THEN I got an error.
"The crawl seed <MSSQL75://SQLServer/04659998> in project <SQLServer
SQL0001000007> cannot be accessed. Error: 80040d19 - The filtering process
was stopped because its memory quota was exceeded. To increase the memory
quota of the filtering process, increase the value for the following
registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\Search\1.0\G athering
Manager\FilterProcessMemoryQuota. .
There's not much in the KB about this, but
http://support.microsoft.com/default...b;en-us;308771 appeared to
be close.
So I upped it from 1900000 to 2900000. So far things are working better.
(I did have to rebuild the catalog, but that may be a small price to pay on
a test box. :-)
It's up to 712417 records indexed and still going.
Yeah, I was struck by the total number in the earlier two runs being fairly
close. Not sure if it's coincidence or not.
If this population is still working as of tomorrow I'll call this one
closed.
> The CPU usage is usually low, except when there are Shadow Merges and at
the
> end of the Full Population during the Master Merge. A PopulateStatus of 1
> means that the "Full population in progress", so hopefully a MSSearch or
> MssCi source event will be present to help explain this!
Hopefully, but like I say, there was nothing until I manually stopped it.
Anyway, thanks for the help so far.
> Thanks,
> John
>
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:dXmkd.223$qv5.36@.twister.nyroc.rr.com...
now
> log.
> on
> was
> 103
> sure
>
|||Greg,
Ok, while you may be able to read (all events), many who post here do not
seem to be able to do so... ;-)
Stopping the Full Population and then reviewing the event log was a very
good idea! Are you FT Indexing large (MS Office, Adobe PDF or other file
types?) files stored in an IMAGE column? The KB article doesn't specifically
mention Error: 80040d19, but the recently enhanced (SP3) message text does
make reference to the FilterProcessMemoryQuota registry key, so a connection
between the error and fix could be made, so I will doubt you no moore :-)
I'm not sure how many rows you have left to complete past the 712,417 mark,
but monitor it closely and if it appears to hang again, stop it and check
the event log, if you get the same message increase the
FilterProcessMemoryQuota value. What type of files are you FT Indexing and
what is their avg. and max sizes?
Good Catch!
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ZQBkd.468$qv5.49@.twister.nyroc.rr.com...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:eznFI00xEHA.1192@.tk2msftngp13.phx.gbl...
> as
> here?
> as
does[vbcol=seagreen]
371,952,
> Trust me John, there was NOTHING in the Event log. :-) I can read an
event
> log.
> I also checked the Gatherlogs and nothing was out of the ordinary.
> HOWEVER... when I manually stop the population and checked the event log
> THEN I got an error.
> "The crawl seed <MSSQL75://SQLServer/04659998> in project <SQLServer
> SQL0001000007> cannot be accessed. Error: 80040d19 - The filtering process
> was stopped because its memory quota was exceeded. To increase the memory
> quota of the filtering process, increase the value for the following
> registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\Search\1.0\G athering
> Manager\FilterProcessMemoryQuota. .
> There's not much in the KB about this, but
> http://support.microsoft.com/default...b;en-us;308771 appeared to
> be close.
> So I upped it from 1900000 to 2900000. So far things are working better.
> (I did have to rebuild the catalog, but that may be a small price to pay
on
> a test box. :-)
> It's up to 712417 records indexed and still going.
> Yeah, I was struck by the total number in the earlier two runs being
fairly[vbcol=seagreen]
> close. Not sure if it's coincidence or not.
> If this population is still working as of tomorrow I'll call this one
> closed.
>
> the
1[vbcol=seagreen]
> Hopefully, but like I say, there was nothing until I manually stopped it.
> Anyway, thanks for the help so far.
>
> message
> now
concerned?[vbcol=seagreen]
most[vbcol=seagreen]
application[vbcol=seagreen]
(doing[vbcol=seagreen]
rows.[vbcol=seagreen]
usage[vbcol=seagreen]
be
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:eXHkxK7xEHA.3808@.TK2MSFTNGP15.phx.gbl...
> Greg,
> Ok, while you may be able to read (all events), many who post here do not
> seem to be able to do so... ;-)
> Stopping the Full Population and then reviewing the event log was a very
> good idea! Are you FT Indexing large (MS Office, Adobe PDF or other file
> types?) files stored in an IMAGE column? The KB article doesn't
specifically
> mention Error: 80040d19, but the recently enhanced (SP3) message text does
> make reference to the FilterProcessMemoryQuota registry key, so a
connection
> between the error and fix could be made, so I will doubt you no moore :-)
I have about 8 million rows in this table.
Interestingly enough, last night it appeared to stop at that 712,417 mark
right after I posted.
In fact it was still there when I woke up this morning.
And then I checked a few hours later, it was up over 1 million records and
climbing. But it appears to have done NOTHING for about 8-10 hours. Very
strange.
It's now up to 2,419,059 and still climbing.
> I'm not sure how many rows you have left to complete past the 712,417
mark,
> but monitor it closely and if it appears to hang again, stop it and check
> the event log, if you get the same message increase the
> FilterProcessMemoryQuota value. What type of files are you FT Indexing and
> what is their avg. and max sizes?
>
Not really files, but data uploaded from documents such as Word files,
Quark, Wordperfect, text files, etc.
Not sure avg size, but probably several meg on average. Total table size
is about 44 Gig. The text column is by far the biggest part of the index
(we also have some varchars in there), so I'd guess about 4-5 meg each.
> Good Catch!
> John
>
|||Greg,
Good, so at least you're above the 2.4 million row mark now, with much to
still be processed. Now, that I know you have an 8 million row table, I can
give you a ball park timeframe for when this might take to complete, but
keep in mind that it is a SWAG. As 1 million rows on a poorly configured
server (database & FT Catalog sharing the same disk drive, with 1 CPU and
1GB of RAM) can take up to 14 hours to complete, so expect a similarly
configured server to finish FT Indexing of a table with 8 million rows in
approx. 4 to 5 days. Of course, your mileage may vary and your server's
actual configuration (software & hardware) may be different...
Also, you say that you are not FT Indexing files (i.e.. binary files stored
in an IMAGE datatype), but are in fact storing the text data uploaded from
documents such as Word files, Quark, Wordperfect, text files, etc., so this
textual data is stored in a column defined with the TEXT (or NTEXT)
datatype. Correct? If so, you can get the max and avg metadata via the
following SQL query :
select avg(datalength(Text_column_name)) "Avg. Text Length" from
8_million_row_table_name
select max(datalength(Text_column_name)) "Max. Text Length" from
8_million_row_table_name
While the Text datatype certainly supports well above 4-5 Mb of text in each
row, I'm fairly certain that this is well above what is typical for most FT
Indexing applications and my lie at the heart of the problem. While the BOL
title "Filtering Supported File Types" is referencing "documents" stored in
an IMAGE datatype, when it is stated that "For full-text indexing, a
document must be less than 16 megabytes (MB) in size and must not contain
more than 256 kilobytes (KB) of filtered text." This statement *may* also
apply to plain text stored in a TEXT or NTEXT datatype and this is why
getting both the avg. and especially max values would be most helpful in
understanding this issue.
FYI, I suspect that you've read the SQL 2000 BOL title "Full-text Search
Recommendations" as well as the SQL 2000 FTS deployment white paper at:
http://support.microsoft.com/default...b;en-us;323739 both speak to
software & hardware configuration issues when used with SQL Server 2000 FT
Indexing of tables over 1 million rows.
Thanks,
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:DPWkd.3206$qv5.2464@.twister.nyroc.rr.com...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:eXHkxK7xEHA.3808@.TK2MSFTNGP15.phx.gbl...
not[vbcol=seagreen]
> specifically
does[vbcol=seagreen]
> connection
:-)[vbcol=seagreen]
> I have about 8 million rows in this table.
> Interestingly enough, last night it appeared to stop at that 712,417 mark
> right after I posted.
> In fact it was still there when I woke up this morning.
> And then I checked a few hours later, it was up over 1 million records and
> climbing. But it appears to have done NOTHING for about 8-10 hours. Very
> strange.
> It's now up to 2,419,059 and still climbing.
> mark,
check[vbcol=seagreen]
and
> Not really files, but data uploaded from documents such as Word files,
> Quark, Wordperfect, text files, etc.
> Not sure avg size, but probably several meg on average. Total table size
> is about 44 Gig. The text column is by far the biggest part of the index
> (we also have some varchars in there), so I'd guess about 4-5 meg each.
>
>
FTS Catalogs won't populate
Hi
After creating a full text catalog in SQL Server 2000 and performing the initial population, the catalog say has an index count of 1 (should be several thousand) and all "contains" queries return no results. The gather log contains the following error message: Error Fetching URL, (80070002 - The system can not find the file specified) . Any catalog I try to make has the same problem. All catalogs were working fine last week. Any help would be very appreciated.
Thanks!
We are having the exact same problem. Did you find a resolution? Thanks. Also, is your SQL 2000 databases in a SQL cluster? Ours is, but don't know if that's part of the problem.|||Hey, we fixed our problem by re-registering tquery.dll on SQL Server. See http://support.microsoft.com/kb/817301/en-us. Hope this helps.|||Our DB is on a cluster too. I will definitely try your solution tomorrow and see what happens. Thanks!|||Hope it fixes it. Best of luck.FTS Catalogs won't populate
Hi
After creating a full text catalog in SQL Server 2000 and performing the initial population, the catalog say has an index count of 1 (should be several thousand) and all "contains" queries return no results. The gather log contains the following error message: Error Fetching URL, (80070002 - The system can not find the file specified) . Any catalog I try to make has the same problem. All catalogs were working fine last week. Any help would be very appreciated.
Thanks!
We are having the exact same problem. Did you find a resolution? Thanks. Also, is your SQL 2000 databases in a SQL cluster? Ours is, but don't know if that's part of the problem.|||Hey, we fixed our problem by re-registering tquery.dll on SQL Server. See http://support.microsoft.com/kb/817301/en-us. Hope this helps.|||Our DB is on a cluster too. I will definitely try your solution tomorrow and see what happens. Thanks!|||Hope it fixes it. Best of luck.
订阅：
博文 (Atom)