Full-Text Catalog does not build...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

news.microsoft.com - 23 Feb 2005 15:43 GMT

Hello All,
Database size=150 GB
FT indexed Table = 120 millions of records.

On the start of project database was small and FT catalog builds
successfully.
Now building and populating can not finish. The status of FTC is: Populating
in progress, may be paused, but not Idle.

I see this 3 weeks.

What can I do?

Thank you.

Reply to this Message

Oleg Cherkasenko - 23 Feb 2005 15:50 GMT

Its my msg.

> Hello All,
> Database size=150 GB
[quoted text clipped - 10 lines]
>
> Thank you.

Reply to this Message

Hilary Cotter - 23 Feb 2005 18:29 GMT

download the source code found here. It will tell you where in your
population status you are. 120 million records will take quite a few days to
populate.

http://download.microsoft.com/download/5/d/3/5d32aa05-5b4a-4ec7-8a3b-de511fedde3
8/SQLPro04-06Hilary.exe

Signature

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

> Its my msg.
>
[quoted text clipped - 12 lines]
> >
> > Thank you.

Reply to this Message

John Kane - 23 Feb 2005 16:33 GMT

Oleg,
Could you post the full output of the below SQL code as this is most helpful
information in troubleshooting SQL FTS issues.

use <your_database_name_here>
go
SELECT @@language
SELECT @@version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go

You should also review all the KB articles and white paper at "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry

I'd also highly recommend that you consider upgrading to SQL Server 2005
(Yukon) as a FT Indexed table of 120 million rows is well above the upper
tested limits of SQL Server 2000. While SQL Server 2005 is still in beta
(currently Beta2, but a more public Beta3 is due out soon), it has many Full
Text Indexing performance improvements and has been tested with tables well
above 120 million rows.

Finally, when Full Population fails to complete or appears to 'hang', the
first place you should look is the server's Application event log as this is
the only place where such errors are written. You should review all
"Microsoft Search" and MssCi source events (errors, warnings AND
informationals) and especially MssCi events as most likely the FT Catalog is
corrupt and the MSSearch service has paused the FT Indexing. Note, the FT
Indexing of a table size of 120 million rows in SQL Server 2000 can take
several weeks to complete successfully.

Regards,
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Hello All,
> Database size=150 GB
[quoted text clipped - 10 lines]
>
> Thank you.

Reply to this Message

Oleg Cherkasenko - 24 Feb 2005 13:05 GMT

John, thank you.

By the way, from time to time during population I have got event about SQL
Server cant obtain LOCK.
Server machine has 6GB RAM, SQL Server uses AWE and has 5GB memory.

When can I read about languages were realized for FT in Yukon?
Are Vietnamese, Turkish, Arabic languages realized?

Its query output:

1.us_english

2.Microsoft SQL Server 2000 - 8.00.878 (Intel X86) Nov 11 2003 13:37:42
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)

3.default full-text language 0 2147483647 1033 1033

4.
5 cat_neu_prop1 F:\MSSQL\FTCatalog 9 1
6 cat_neu_preset F:\MSSQL\FTCatalog 0 1
7 cat_neu_x F:\MSSQL\FTCatalog 1 1

5.
dbo presets PK_presets 1 1 cat_neu_preset
dbo properties PK_properties 7 1 cat_neu_prop1
dbo xproperties_string PK_xproperties_string 1 1 cat_neu_x

6.
dbo 1310627712 presets name 3 NULL NULL 0
dbo 521768916 properties string_value 4 NULL NULL 0
dbo 521768916 properties text_value 5 NULL NULL 0
dbo 2070298435 xproperties_string string_value 2 NULL NULL 0

7.
properties dbo user table 2003-11-17 18:46:50.360

8.
object_id uniqueidentifier no 16 no (n/a) (n/a) NULL
descriptor_id uniqueidentifier no 16 no (n/a) (n/a) NULL
value sql_variant no 8016 yes no (n/a) NULL
string_value nvarchar no 7000 yes (n/a) (n/a)
SQL_Latin1_General_CP1_CI_AS
text_value ntext no 16 yes (n/a) (n/a)
SQL_Latin1_General_CP1_CI_AS
preset_id uniqueidentifier no 16 yes (n/a) (n/a) NULL
id timestamp no 8 no (n/a) (n/a) NULL

9.
No identity column defined. NULL NULL NULL

10.
No rowguidcol column defined.

11.
PRIMARY

12.
IX_properties_descriptor_id nonclustered located on PRIMARY descriptor_id
IX_properties_object_id nonclustered located on PRIMARY object_id
IX_properties_object_id_descriptor_id nonclustered, unique located on
PRIMARY object_id, descriptor_id
PK_properties clustered, unique, primary key located on PRIMARY id

13.
FOREIGN KEY FK_properties_descriptors Cascade No Action Enabled
Is_For_Replication descriptor_id
REFERENCES mtn_virgin.dbo.descriptors (id)
FOREIGN KEY FK_properties_nodes No Action No Action Disabled
Is_For_Replication object_id
REFERENCES mtn_virgin.dbo.nodes (id)
PRIMARY KEY (clustered) PK_properties (n/a) (n/a) (n/a) (n/a) id

Regards,
Oleg Cherkasenko.

> Oleg,
> Could you post the full output of the below SQL code as this is most
[quoted text clipped - 53 lines]
>>
>> Thank you.

Reply to this Message

John Kane - 24 Feb 2005 16:37 GMT

You're welcome, Oleg,
First of all, thank you for the @@version and @@language and sp_help
information as this is very useful in troubleshooting SQL FTS issues!

You're using SQL Server 2000 (8.00.878) on Win2K SP4 with the default FT
language of US_English and with your column specific "Language for Word
Breaker" set to Neutral.

Your FT-enabled table "properties" has 120 million rows and two columns
(object_id and descriptor_id) and both of the columns are defined with the
uniqueidentifier (or GUID) datatype. Correct? If so, then you may want to
consider altering this table and adding an identity column defined as an INT
datatype and create a non-clustered index on this column and use this as the
key for your FT Index. It is highly recommended that you use as "small"
datatype such as an INT for this key to the FT Index as this column is also
stored in the FT Catalog. This is especially significant for very large (120
million rows) FT-enabled tables as this can cause the FT Catalog to be very
large and affect FTS query performance as well!

Q.When can I read about languages were realized for FT in Yukon? Are
Vietnamese, Turkish, Arabic languages realized?
A. Using SQL Server 2005, Dec 2004 CTP version, the following query lists
all of the *currently* supported languages in SQL Server 2005 for Full Text
Search:

select @@version -- SQL Server 2005 - 9.00.0981 (Intel X86)... Beta Edition
on Windows NT 5.2 (Build 3790: )
go
select * from Sys.Fulltext_Languages
/* -- returns:
lcid name
----------- -------------------------
2052 Simplified Chinese
1028 Traditional Chinese
1030 Danish
1031 German
2057 British English
1033 English
3082 Spanish
1036 French
1040 Italian
1041 Japanese
1042 Korean
0 Neutral
1043 Dutch
1045 Polish
1046 Brazilian
2070 Portuguese
1049 Russian
1053 Swedish
1054 Thai
1055 Turkish
3076 Chinese (Hong Kong SAR, PRC)
5124 Chinese (Macau SAR)
4100 Chinese (Singapore)
(23 row(s) affected)
*/
Possibly, by RTM, there will be more?

Hope that helps!
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> John, thank you.
>
[quoted text clipped - 91 lines]
> > You should also review all the KB articles and white paper at "SQL Server
> > 2000 Full-Text Search Resources and Links" at:

http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry

> > I'd also highly recommend that you consider upgrading to SQL Server 2005
> > (Yukon) as a FT Indexed table of 120 million rows is well above the upper
[quoted text clipped - 33 lines]
> >>
> >> Thank you.

Reply to this Message

Oleg Cherkasenko - 24 Feb 2005 17:18 GMT

Thank you for the information, I think about reconstruction...
Neutral language is used because one of our customer has US English and
Arabic texts in database.
Also 2005 I dont see arabic language for FT Indexing and search.

Regards,
Oleg.

> You're welcome, Oleg,
> First of all, thank you for the @@version and @@language and sp_help
[quoted text clipped - 211 lines]
>> >>
>> >> Thank you.

Reply to this Message

John Kane - 25 Feb 2005 02:05 GMT

You're welcome, Oleg,
While, I've not confirmed if the Arabic language will be a FTS supported
language in SQL Server 2005 (perhaps, by RTM), you may want to review the
new "Microsoft Arabic Word-Breaker (Arabic Search Engine) - Beta" at
http://www.microsoft.com/middleeast/arabicdev/beta/search/ and download the
Installation Guide and the Microsoft Arabic Word-Breaker. Under the
"Installation Requirements for SQL Server 2000" section, you will find that
the Arabic Word-Breaker is "...the Full-Text Search service is enabled for
running queries using the new Arabic Word-Breaker."

You might find the following material useful - In addition to the BOL
documentation, there is now on MSDN - "Arabic
Language Support in Microsoft SQL Server 2000" at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sq
l_arabicsupport.asp
that might also be helpful to you.

Hope that helps!
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Thank you for the information, I think about reconstruction...
> Neutral language is used because one of our customer has US English and
[quoted text clipped - 171 lines]
> > Server
> >> > 2000 Full-Text Search Resources and Links" at:

http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry

> >> > I'd also highly recommend that you consider upgrading to SQL Server
> >> > 2005
[quoted text clipped - 41 lines]
> >> >>
> >> >> Thank you.

Reply to this Message

Oleg Cherkasenko - 25 Feb 2005 12:06 GMT

John - its great!!
Thank you for the information.

> You're welcome, Oleg,
> While, I've not confirmed if the Arabic language will be a FTS supported
[quoted text clipped - 250 lines]
>> >> >>
>> >> >> Thank you.

Reply to this Message

Oleg Cherkasenko - 25 Feb 2005 12:29 GMT

Question about arabic word breaker:

I have field in my FTI table: string_value.
This one contains mixed text English+Arabic.

Is it possible to search phrases in both languages?
May be not simultaneously, but using morphology, word forms etc. for 2
languages or I can use only one word-breaker and only one language forms?

For example: I use Arabic. And when I search "send" I never found "sent"?

Thanks.
Regards,Oleg.

> You're welcome, Oleg,
> While, I've not confirmed if the Arabic language will be a FTS supported
[quoted text clipped - 250 lines]
>> >> >>
>> >> >> Thank you.

Reply to this Message

John Kane - 25 Feb 2005 16:38 GMT

You're welcome, Oleg,
Yes, you can FT Search a mixed language column, if you have a mix of English
and Arabic text in one column in SQL Server 2000, you must use the neutral
"Language for Word Breaker". You can only use the Arabic wordbreaker for
FT-enable columns that only contain Arabic text. A draw back of using the
neutral "Language for Word Breaker" is that language-specific word
variations is lost as the Neutral wordbreaker breaks the words based upon
the "white space" between the word as it does not understand
language-specific word variations...

FYI, SQL Server 2005 will be able to search specific languages in a mixed
language column, via using a LCID on the CONTAINS statement, but this is not
available in SQL Server 2000...

Hope that helps!
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Question about arabic word breaker:
>
[quoted text clipped - 25 lines]
> > documentation, there is now on MSDN - "Arabic
> > Language Support in Microsoft SQL Server 2000" at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sq
l_arabicsupport.asp

> > that might also be helpful to you.
> >
[quoted text clipped - 182 lines]
> >> > Server
> >> >> > 2000 Full-Text Search Resources and Links" at:

http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry

> >> >> > I'd also highly recommend that you consider upgrading to SQL Server
> >> >> > 2005
[quoted text clipped - 45 lines]
> >> >> >>
> >> >> >> Thank you.

Reply to this Message