FTI population: Locking issue shutting down Full-Text Index population

We are implementing FTI on a Linguistic SQL Server Database for performance improvement. The maximum size of database is 20 GB and there are 27 million records in the table on which we are implementing FTI. When We did a small POC with 1.2 million records, the FTI worked fine. But now with 27 million records it' giving the error messages attached in the event logs below.

It looks like the system resources are insufficient for completing the FTI population, but can I pinpoint what is causing problem from the overall system? Can we make some firm conclusions by looking at the attached event logs?

FYI: the free disc space on which the FTI catalogs are kept is 55GB. The SQL Server configuration is 4 processors, 4GB RAM.

Any help in this regard is greatly appreciated. ThanksHi There,

You could always split the process by /8 or /16 different tables and then move them to the new location systematically and not all at one time.

(ISQL/W : Select percentage 25% from blah ...?)

FT indexes are not populating

I've created a couple of full-text indexes using the FT wizards and/or sample
code from BOL. Everything appears to be correct and I've run a full
population on them without getting any errors. However, when I run my
queries to test the results I get no rows returned. When I look at the
properties of the FT Index, the item count states 0.
Did I miss something on setup?
Kevin Z
Are there any messages from MSSCI or MSSEach in the event log?
You also might want to review this kb article.
http://support.microsoft.com/default...b;en-us;317746
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
"kzakoski" <kzakoski@.discussions.microsoft.com> wrote in message
news:F1DD2400-556F-4E0A-998A-CF9A2C43F29F@.microsoft.com...
> I've created a couple of full-text indexes using the FT wizards and/or
sample
> code from BOL. Everything appears to be correct and I've run a full
> population on them without getting any errors. However, when I run my
> queries to test the results I get no rows returned. When I look at the
> properties of the FT Index, the item count states 0.
> Did I miss something on setup?
> --
> Kevin Z
|||Builtin\Administrator account had been removed causing the problem. KB
aritcle had a work around.
Additonal question? Will a FT Index process an entire 'text' datatype
column when building the index or is there a limit on the number of
characters it will process? On the FT index that I just created, I'm
searching the FT columnn that contains SQL scripts. The table name I'm using
in my contains clause is a pretty unique table name, but buried in a lengthy
script. When I run the query, I don't get any matches, but if I use a LIKE
clause with wildcards I can find it. Any suggestions?
Kevin Z
"Hilary Cotter" wrote:

> Are there any messages from MSSCI or MSSEach in the event log?
> You also might want to review this kb article.
> http://support.microsoft.com/default...b;en-us;317746
> --
> 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
> "kzakoski" <kzakoski@.discussions.microsoft.com> wrote in message
> news:F1DD2400-556F-4E0A-998A-CF9A2C43F29F@.microsoft.com...
> sample
>
>
|||From BOL entitled Filtering Supported File Types
Note 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.
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
"kzakoski" <kzakoski@.discussions.microsoft.com> wrote in message
news:1696B0D8-F132-4DAA-9099-44790BDADB65@.microsoft.com...
> Builtin\Administrator account had been removed causing the problem. KB
> aritcle had a work around.
> Additonal question? Will a FT Index process an entire 'text' datatype
> column when building the index or is there a limit on the number of
> characters it will process? On the FT index that I just created, I'm
> searching the FT columnn that contains SQL scripts. The table name I'm
using
> in my contains clause is a pretty unique table name, but buried in a
lengthy
> script. When I run the query, I don't get any matches, but if I use a
LIKE[vbcol=seagreen]
> clause with wildcards I can find it. Any suggestions?
> --
> Kevin Z
>
> "Hilary Cotter" wrote:
the[vbcol=seagreen]

Front-end website search using Full-Text

I am building a front-end search on a website to serach through the
contents of a full-text enabled databsae. I have everything set-up and
working using FREETEXTTABLE.

Here is what I want to do. When a user types in a search phrase that
has a couple words in quotes, I want it to do an AND for those instead
of the normal OR that freetexttable seems to do. Is there something
built into SQL Server that can handle this or do I need to parse the
phrase myself and build up some sort of logical operation with the
words and a combination of AND's and OR's. Has anyone done this or
have freeware that handles it or any suggestions at all.

Thanks in advance for any help.FREETEXTTABLE doesn't match exact words, so if you need that
functionality you'll probably need to check out CONTAINSTABLE as well.
>From what little I know of fulltext searching, there seems to be very
limited support for variables and parameters, so you might find you
have to build up your queries yourself. Having said that, it would
probably be worth posting to microsoft.public.sqlserver.fulltext to see
if people there have more specific advice.

Simon

Front end website full-text search

I have a front-end search set up that takes the users input and runs a
stored procedure that uses FREETEXTTABLE to get the results. I want it
so that when a user types in a search that has words grouped together
inside quotes (i.e. "sql server") it does an AND instead of an OR. I
know that CONTAINSTABLE can have this kind of logic but I was wondering
if there is a way to handle this without having my script (ASP)
building the search phrase by parsing the user input.
Any ideas? Or has someone done this and have some advice.
Thanks.
But if it does an AND it will not be the same as "sql server" which searches
for the two words occurring adjacent to each other.
Here is a proc which you can set whether you want the entire phase, an OR,
or an AND.
CREATE PROCEDURE SearchSQL (@.stringin varchar(200), @.BooleanType int= NULL)
AS
-- a @.boolean type of null means a phrase based search
-- a @.boolean type of 0 means an OR type search
-- a @.boolean type of 1 means an AND type search
DECLARE @.holdingString VARCHAR(2000)
DECLARE @.whitespace INT
DECLARE @.boolean VARCHAR(10)
--returning a syntax message if no search phrase is passed
IF LEN(@.stringin)=0
BEGIN
PRINT 'usage is SimpleSQLFTSSearch ''Your Search Phrase goes here'''
RETURN -1
END
SET @.boolean=case WHEN @.booleantype=1 THEN char(34)+' OR ' + char(34)
WHEN @.booleantype=2 THEN char(34)+' AND ' + char(34)
ELSE ' ' END
DECLARE @.counter INT
DECLARE @.posold int
DECLARE @.posnew int
SET @.holdingstring='SELECT * FROM authors AS a JOIN
CONTAINSTABLE(authors,*,'''+char(34)
SELECT @.whitespace=LEN(@.stringin) - LEN(replace(@.stringin,' ',''))
SELECT @.posold=0
SELECT @.posnew=Charindex(' ',@.stringin)
WHILE @.whitespace >=0
BEGIN
IF @.whitespace=0
BEGIN
SELECT
@.holdingString=@.holdingString+SUBSTRING(@.stringin, @.posold+1,LEN(@.stringin)-@.
posold+1)+char(34)+char(39)+',200) AS t ON '
END
ELSE
BEGIN
SELECT @.holdingString = CASE WHEN LEN(SUBSTRING(@.stringin,@.posold+1,
@.posnew-@.posold-1))>0 THEN @.holdingString+SUBSTRING(@.stringin,@.posold+1,
@.posnew-@.posold-1)+@.boolean ELSE @.holdingstring END
SELECT @.posold=@.posnew, @.posnew=Charindex(' ',@.stringin, @.posold+1)
END
SELECT @.whitespace=@.whitespace-1
END
SELECT @.holdingString = @.holdingString + 't.[KEY]=a.au_id ORDER BY RANK
DESC'
--PRINT @.holdingstring
EXEC(@.holdingstring)
RETURN @.@.rowcount
Usage is:
DECLARE @.returncode int
EXEC @.returncode=SearchSQL 'this is a test'
PRINT @.returncode
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
"JT" <jtreuting@.gmail.com> wrote in message
news:1112316885.228212.171380@.f14g2000cwb.googlegr oups.com...
> I have a front-end search set up that takes the users input and runs a
> stored procedure that uses FREETEXTTABLE to get the results. I want it
> so that when a user types in a search that has words grouped together
> inside quotes (i.e. "sql server") it does an AND instead of an OR. I
> know that CONTAINSTABLE can have this kind of logic but I was wondering
> if there is a way to handle this without having my script (ASP)
> building the search phrase by parsing the user input.
> Any ideas? Or has someone done this and have some advice.
> Thanks.
>

FREETEXTTABLE

Hi..

I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:

Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.

The stored procedure :

CREATE PROCEDURE SearchProject
(
@.SearchString nvarchar(500),
@.CultureName nvarchar(50),
@.HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@.SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@.SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
AND FL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@.SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@.CultureName)
AND PL.CultureID=dbo.GetCultureID(@.CultureName)

SELECT @.HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO

I called the stored procedure using the following(in the Query Analayzer) :

USE nabeel1eagle
DECLARE @.HowManyResults int
EXEC SearchProject '?????','ar-SA',@.HowManyResults OUTPUT

but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:

SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'?????') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')

Could any one help and tell me how to solve this problem?

http://support.microsoft.com/?kbid=892924

HTH

|||

Thank you very much for your respond and important information..I have found a solution for my problem before i view your message..The solution was changing the sql collation and the windows collation.

The Search tool is working fine now in my website:www.engineeringprojects.net.

|||could you provide me the changed code using windows collation and sql collation. plz mail me to ananth.ramasamymeenachi@.cognizant.com|||

Sorry for being too late...

The code is the same but you can change the collation visually in the table designer..I dont remember what was the SqlCollation exactlly but the windows Collation was arabic.

FREETEXTTABLE

Hi..

I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:

Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.

The stored procedure :

CREATE PROCEDURE SearchProject
(
@.SearchString nvarchar(500),
@.CultureName nvarchar(50),
@.HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@.SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@.SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
AND FL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@.SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@.CultureName)
AND PL.CultureID=dbo.GetCultureID(@.CultureName)

SELECT @.HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO

I called the stored procedure using the following(in the Query Analayzer) :

USE nabeel1eagle
DECLARE @.HowManyResults int
EXEC SearchProject '?????','ar-SA',@.HowManyResults OUTPUT

but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:

SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'?????') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')

Could any one help and tell me how to solve this problem?

http://support.microsoft.com/?kbid=892924

HTH

|||

Thank you very much for your respond and important information..I have found a solution for my problem before i view your message..The solution was changing the sql collation and the windows collation.

The Search tool is working fine now in my website:www.engineeringprojects.net.

|||could you provide me the changed code using windows collation and sql collation. plz mail me to ananth.ramasamymeenachi@.cognizant.com|||

Sorry for being too late...

The code is the same but you can change the collation visually in the table designer..I dont remember what was the SqlCollation exactlly but the windows Collation was arabic.

FREETEXTTABLE

Hi..

I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:

Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.

The stored procedure :

CREATE PROCEDURE SearchProject
(
@.SearchString nvarchar(500),
@.CultureName nvarchar(50),
@.HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@.SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@.SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
AND FL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@.SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@.CultureName)
AND PL.CultureID=dbo.GetCultureID(@.CultureName)

SELECT @.HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO

I called the stored procedure using the following(in the Query Analayzer) :

USE nabeel1eagle
DECLARE @.HowManyResults int
EXEC SearchProject '?????','ar-SA',@.HowManyResults OUTPUT

but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:

SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'?????') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')

Could any one help and tell me how to solve this problem?

http://support.microsoft.com/?kbid=892924

HTH

|||

Thank you very much for your respond and important information..I have found a solution for my problem before i view your message..The solution was changing the sql collation and the windows collation.

The Search tool is working fine now in my website:www.engineeringprojects.net.

|||could you provide me the changed code using windows collation and sql collation. plz mail me to ananth.ramasamymeenachi@.cognizant.com|||

Sorry for being too late...

The code is the same but you can change the collation visually in the table designer..I dont remember what was the SqlCollation exactlly but the windows Collation was arabic.