Full T-Log: options?

An automatic monthly delete has recently grown from 15 to 20 million rows. It is now filling my 70GB T-Log completly. I don't have any space to expand the T-Log. Do I have any options other than reducing the number of rows in the delete?Is the database in full or simple recovery mode?

In either case, batch your deletes in small chunks instead of trying to do them all at once (say 1M or 100K or 10K at a time).

If you are in simple mode, when the batch finishes, a database checkpoint will occur and the log will be truncated and the space allocated for reuse.

If you are in full recovery mode, perform a tran log backup before starting the next batch and the log will be truncated and the space allocated for reuse.|||In either case, batch your deletes in small chunks instead of trying to do them all at once (say 1M or 100K or 10K at a time).


x2.

Also, you should set a performance alert on you transaction log to kick off when the log reaches xx% full. Have the action of the alert set to kick off a transaction log backup. This will NOT work unless you batch your deletes into smaller quantities, but it should work well in conjunction with batching.

For example, my db is set to do a custom defrag Sunday nights. The defrag is broken up into about 50 steps and takes about 45 minutes. During the process, about 50 GB of data is written to the t-log (which is 35 GB in size). I have the performance alert set to trigger when the t-log is 60% full with a ten minute interval set between responses.

This way, when the log file gets to about 60% full, the transaction log backup kicks off automatically. The process repeats itself until the defrags are complete.

Be SURE to set the response interval to something realistic; I once forgot and watched as the response kicked off every second (for something different) and there was nothing I could do to stop it.

Regards,

hmscott

Full Text Search resultset count

For performance reasons I am limiting the resultset to 100 rows. However, I would like to have the performance but need the total count of rows found. How can I keep the performance gain from limiting to top 100 and still get the total count.

Could I do something like this:

select top 100 count(id), field1, field2, field3 from ....

Would this return 100 in the count field?

Thanks

I think T-SQL does not support above sematic. field1, field2, ... will need to be part of group by or aggregate function.

Back to you problem, it seems like you want to know total hit count and want it performs well. The problem is that to get total hit count it requires to get the whole result set which is major part of the cost. By using top 100 really will not save you much. If you can remove the requirement to get total hit count, you can then use containstable with a top_n parameter, it will save you a lot for full-text search part.

thanks,

jingwei

Full Text Search 'Near'

using SQL server 2005 I have a table which has full text search index enabled

when I run a search using the 'Near' option it returns rows which meet the criteria, how ever the criteria are not 'near' each other they are several hundred words apart.

What is the problem

How near is near

thanks in advance

Glyn

Hi,

The NEAR operator is used to find words which are in close proximity to one another. For example, consider the following three rows in a table:

TextValue

==========

The product being used is SQL Server 2005 Enterprise Edition

We are using full text search in SQL Server 2005 Enterprise Edition

We are performing a test with full text search in SQL Server 2005 Enterprise Edition

Now if I have a query of the following type:

select TextValue

from dbo.sample_tbl

where contains (TextValue, 'search NEAR Server')

This will return the 2nd and the 3rd rows as both the words are approximately close to each other. The NEAR operator returns those rows which have both the words approximately close to each other. When several proximity terms are chained, all proximity terms must be near each other.

|||

Thanks but the search I have is not working

I have a text string

'FTS_STRING_SAMPLE1 can control instantiation to suit the system environment. Relying on delayed instantiation minimizes the amount of memory required by the application, although it might trigger many server requests when properties are referenced.

Instance classes, objects that represent real database objects, can exist in three levels of instantiation. These are minimal-instantiated (only the minimal required properties are read in one block), partially instantiated (all the properties that use a relatively large amount of memory are read in one block), and fully instantiated. Un-instantiated and fully instantiated are the traditional states of instantiation. The partially instantiated state increases efficiency because a partially instantiated object does not contain values for the full set of object properties. Partial instantiation is the default state for an object that is not directly referenced. When one of these properties is referenced, a fault is generated that prompts a full instantiation of the object FTS_STRING_SAMPLE2.'

SELECT *
FROM FTS_TEST_TABLE
WHERE CONTAINS(FTS_TEST_TEXT, 'FTS_STRING_SAMPLE1 NEAR FTS_STRING_SAMPLE2');

It returns the row, however we believe it shouldn't

Glyn

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...
>
>

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

Full text search installed but no languages supported :(

Hi,

I Have the same problem, i execute the T-sql statement select * from sys.fulltext_languages and i get zero rows, i uninstalled and installed again and nothing happend i still have zero when executing that statement, i try another computer and there evrything went fine!.. i do have Microsoft Net framework, and everything but i still can get this to work! help please!!!

Hi Ikeer,

Fulltext Search is not installed by default. You should confirm that you actually selected if from the feature list when you installed it. If you just took the defaults (i.e. clicked Next until you finished) you didn't install it. You'll need to launch maintenance mode from Add/Remove Programs and select Fulltext Search from the feature tree.

Mike

|||

Hi Mike,

Yes, I know i installed the Fullsearch text but still i do not get languages. I follow the same step's in my other computer and it worked in that one, the only difference between the two systems is that i installed the SQL sever 2005 beta in the one that is failing. I realized that and read that it need it to be removed so i removed the SQL server 2005 and then went to a link that i found here and it remove the beta. I double check and the application told me that they were not traces of the Beta products. I though that would work now.. but it did not.. when i reinstalled the SQL server 2005 i had no languages :(. I gave up and worked remotely in my other computer. I would love to know what is preventing me from install the SQL Server because i dont want to reinstall everything.. it's like 20hrs :(. anyway any help would be appriciate.

Thank you.

|||

Hi!

Did you try actualy running a FT query? does it work? try selecting a language at query time different than neutral or English?

If it worked in other computer with Express, it might be because that other computer is using the OS WordBreakers and SQL Server is loading them. It seems that this is not happening in your instance (you dont see the installed ones nor the OS ones). Let me investigate more and come back when I get more answers.

Thanks.

|||

Did this resolve the issue?

Mike

|||

Hi again guys,

We need to be sure that FTS was installed correctly. Please do the following:

-Go to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSearch\Language

And let me know which folders/languages are you seeing there. If you don't see any, then it is not correctly installed, if you see them, please forward them to me and we will investigate here what is going on.

you can reply me as well to fernlope@.microsoft.com

Thanks!

|||

Hi i went to that key and saw many folders, inside the language there is a ke call default type REG_SZ Data value not set

then there are many folders

chs

cht

deu

eng

enu

esn

fra

ita

jpn

kor

neutral

nld

sve

tha

zh-hk

zh-mo

zh-sg

by the way, ansering if i tried to run the Full text search query, i did not since it wont let me create the full search in the table.

P.S i also had sql server 2000 install but i unisntalled it way before install the SQL server 2005

Thank you for your help

|||

I am having the exact same problem as Ikeer described above. I did not previously have any SQL Server installed on the computer. I do have MS Visual Studio 2005 Professional installed, but did not elect to install SQL server from that setup. Has there been any new info on this? I tried reinstalling just FTS for my instance, but that didn't seem to help.

Full text search installed but ni languages supported :(

Hi,

I Have the same problem, i execute the T-sql statement select * from sys.fulltext_languages and i get zero rows, i uninstalled and installed again and nothing happend i still have zero when executing that statement, i try another computer and there evrything went fine!.. i do have Microsoft Net framework, and everything but i still can get this to work! help please!!!

Hi Ikeer,

Fulltext Search is not installed by default. You should confirm that you actually selected if from the feature list when you installed it. If you just took the defaults (i.e. clicked Next until you finished) you didn't install it. You'll need to launch maintenance mode from Add/Remove Programs and select Fulltext Search from the feature tree.

Mike

|||

Hi Mike,

Yes, I know i installed the Fullsearch text but still i do not get languages. I follow the same step's in my other computer and it worked in that one, the only difference between the two systems is that i installed the SQL sever 2005 beta in the one that is failing. I realized that and read that it need it to be removed so i removed the SQL server 2005 and then went to a link that i found here and it remove the beta. I double check and the application told me that they were not traces of the Beta products. I though that would work now.. but it did not.. when i reinstalled the SQL server 2005 i had no languages :(. I gave up and worked remotely in my other computer. I would love to know what is preventing me from install the SQL Server because i dont want to reinstall everything.. it's like 20hrs :(. anyway any help would be appriciate.

Thank you.

|||

Hi!

Did you try actualy running a FT query? does it work? try selecting a language at query time different than neutral or English?

If it worked in other computer with Express, it might be because that other computer is using the OS WordBreakers and SQL Server is loading them. It seems that this is not happening in your instance (you dont see the installed ones nor the OS ones). Let me investigate more and come back when I get more answers.

Thanks.

|||

Did this resolve the issue?

Mike

|||

Hi again guys,

We need to be sure that FTS was installed correctly. Please do the following:

-Go to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSearch\Language

And let me know which folders/languages are you seeing there. If you don't see any, then it is not correctly installed, if you see them, please forward them to me and we will investigate here what is going on.

you can reply me as well to fernlope@.microsoft.com

Thanks!

|||

Hi i went to that key and saw many folders, inside the language there is a ke call default type REG_SZ Data value not set

then there are many folders

chs

cht

deu

eng

enu

esn

fra

ita

jpn

kor

neutral

nld

sve

tha

zh-hk

zh-mo

zh-sg

by the way, ansering if i tried to run the Full text search query, i did not since it wont let me create the full search in the table.

P.S i also had sql server 2000 install but i unisntalled it way before install the SQL server 2005

Thank you for your help

|||

I am having the exact same problem as Ikeer described above. I did not previously have any SQL Server installed on the computer. I do have MS Visual Studio 2005 Professional installed, but did not elect to install SQL server from that setup. Has there been any new info on this? I tried reinstalling just FTS for my instance, but that didn't seem to help.

sql

Full text search installed but ni languages supported :(

Hi,

I Have the same problem, i execute the T-sql statement select * from sys.fulltext_languages and i get zero rows, i uninstalled and installed again and nothing happend i still have zero when executing that statement, i try another computer and there evrything went fine!.. i do have Microsoft Net framework, and everything but i still can get this to work! help please!!!

Hi Ikeer,

Fulltext Search is not installed by default. You should confirm that you actually selected if from the feature list when you installed it. If you just took the defaults (i.e. clicked Next until you finished) you didn't install it. You'll need to launch maintenance mode from Add/Remove Programs and select Fulltext Search from the feature tree.

Mike

|||

Hi Mike,

Yes, I know i installed the Fullsearch text but still i do not get languages. I follow the same step's in my other computer and it worked in that one, the only difference between the two systems is that i installed the SQL sever 2005 beta in the one that is failing. I realized that and read that it need it to be removed so i removed the SQL server 2005 and then went to a link that i found here and it remove the beta. I double check and the application told me that they were not traces of the Beta products. I though that would work now.. but it did not.. when i reinstalled the SQL server 2005 i had no languages :(. I gave up and worked remotely in my other computer. I would love to know what is preventing me from install the SQL Server because i dont want to reinstall everything.. it's like 20hrs :(. anyway any help would be appriciate.

Thank you.

|||

Hi!

Did you try actualy running a FT query? does it work? try selecting a language at query time different than neutral or English?

If it worked in other computer with Express, it might be because that other computer is using the OS WordBreakers and SQL Server is loading them. It seems that this is not happening in your instance (you dont see the installed ones nor the OS ones). Let me investigate more and come back when I get more answers.

Thanks.

|||

Did this resolve the issue?

Mike

|||

Hi again guys,

We need to be sure that FTS was installed correctly. Please do the following:

-Go to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSearch\Language

And let me know which folders/languages are you seeing there. If you don't see any, then it is not correctly installed, if you see them, please forward them to me and we will investigate here what is going on.

you can reply me as well to fernlope@.microsoft.com

Thanks!

|||

Hi i went to that key and saw many folders, inside the language there is a ke call default type REG_SZ Data value not set

then there are many folders

chs

cht

deu

eng

enu

esn

fra

ita

jpn

kor

neutral

nld

sve

tha

zh-hk

zh-mo

zh-sg

by the way, ansering if i tried to run the Full text search query, i did not since it wont let me create the full search in the table.

P.S i also had sql server 2000 install but i unisntalled it way before install the SQL server 2005

Thank you for your help

|||

I am having the exact same problem as Ikeer described above. I did not previously have any SQL Server installed on the computer. I do have MS Visual Studio 2005 Professional installed, but did not elect to install SQL server from that setup. Has there been any new info on this? I tried reinstalling just FTS for my instance, but that didn't seem to help.

Full text search installed but ni languages supported :(

Hi,

I Have the same problem, i execute the T-sql statement select * from sys.fulltext_languages and i get zero rows, i uninstalled and installed again and nothing happend i still have zero when executing that statement, i try another computer and there evrything went fine!.. i do have Microsoft Net framework, and everything but i still can get this to work! help please!!!

Hi Ikeer,

Fulltext Search is not installed by default. You should confirm that you actually selected if from the feature list when you installed it. If you just took the defaults (i.e. clicked Next until you finished) you didn't install it. You'll need to launch maintenance mode from Add/Remove Programs and select Fulltext Search from the feature tree.

Mike

|||

Hi Mike,

Yes, I know i installed the Fullsearch text but still i do not get languages. I follow the same step's in my other computer and it worked in that one, the only difference between the two systems is that i installed the SQL sever 2005 beta in the one that is failing. I realized that and read that it need it to be removed so i removed the SQL server 2005 and then went to a link that i found here and it remove the beta. I double check and the application told me that they were not traces of the Beta products. I though that would work now.. but it did not.. when i reinstalled the SQL server 2005 i had no languages :(. I gave up and worked remotely in my other computer. I would love to know what is preventing me from install the SQL Server because i dont want to reinstall everything.. it's like 20hrs :(. anyway any help would be appriciate.

Thank you.

|||

Hi!

Did you try actualy running a FT query? does it work? try selecting a language at query time different than neutral or English?

If it worked in other computer with Express, it might be because that other computer is using the OS WordBreakers and SQL Server is loading them. It seems that this is not happening in your instance (you dont see the installed ones nor the OS ones). Let me investigate more and come back when I get more answers.

Thanks.

|||

Did this resolve the issue?

Mike

|||

Hi again guys,

We need to be sure that FTS was installed correctly. Please do the following:

-Go to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSearch\Language

And let me know which folders/languages are you seeing there. If you don't see any, then it is not correctly installed, if you see them, please forward them to me and we will investigate here what is going on.

you can reply me as well to fernlope@.microsoft.com

Thanks!

|||

Hi i went to that key and saw many folders, inside the language there is a ke call default type REG_SZ Data value not set

then there are many folders

chs

cht

deu

eng

enu

esn

fra

ita

jpn

kor

neutral

nld

sve

tha

zh-hk

zh-mo

zh-sg

by the way, ansering if i tried to run the Full text search query, i did not since it wont let me create the full search in the table.

P.S i also had sql server 2000 install but i unisntalled it way before install the SQL server 2005

Thank you for your help

|||

I am having the exact same problem as Ikeer described above. I did not previously have any SQL Server installed on the computer. I do have MS Visual Studio 2005 Professional installed, but did not elect to install SQL server from that setup. Has there been any new info on this? I tried reinstalling just FTS for my instance, but that didn't seem to help.

Full text search in SQL Server 2000

I have one question about full text search in SQl
Server 2000/2005.
I have one table which has 7 text columns. I want to
retrieve all rows that have the search word in either
of those 7 columns.
I want to know if there is a way to add weight to
those columns so SQL Server can better rank the result
set. For example, column 1 has weight 1 and column 2
has weight 0.1. So records that have search word in
column 1 will rank higher that those having search
word only in column 2.

Thanks a lot,

Baoxin

Unfortunately, There is no standard way to do this.
You can have to play around with regular expressions..

Full text search

Hi
I am testing the usage of FULL TEXT feature and somehow it is not working
for me.
I have 2 rows in a table zz with values
this is a sta test
test
Now when I run the following query I do not get any rows back
but the full text index is used.
select zz from zz where contains ( *,'sta')
|--Hash Match(Left Semi Join, HASH[zz].[zz])=([FULLTEXT:zz].[KEY]),
RESIDUAL[zz].[zz]=[FULLTEXT:zz].[KEY])) 40 3 1
Hash Match Left Semi Join
HASH[zz].[zz])=([FULLTEXT:zz].[KEY]),
RESIDUAL[zz].[zz]=[FULLTEXT:zz].[KEY]) NULL 1.0
0.0 2.4346894E-2 36 0.42534244
[zz].[zz] NULL PLAN_ROW 0
1.0
|--Clustered Index Scan(OBJECT[base].[dbo].[zz].[PK_zz]))
40 4 3
Clustered Index Scan Clustered Index Scan
OBJECT[base].[dbo].[zz].[PK_zz])
[zz].[zz] 2.0 3.7578501E-2
8.0700003E-5 76 3.7659202E-2 [zz].[zz]
NULL PLAN_ROW 0 1.0
|--Remote Scan(OBJECTCONTAINS))
Can you post the results of the following queries?
select FulltextCatalogProperty('CatalogName', 'ItemCount')
replacing CatalogName with the name of your catalog. If the value is 1 or
the number of tables you are full text indexing you will have to run a
population. If you have already run a population, check the application log
using event viewer to see if there are error messages from MSSCI or
MSSearch.
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
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:3E800AE8-603F-4ADF-BCE8-1C3B27174547@.microsoft.com...
> Hi
> I am testing the usage of FULL TEXT feature and somehow it is not working
> for me.
> I have 2 rows in a table zz with values
> this is a sta test
> test
> Now when I run the following query I do not get any rows back
> but the full text index is used.
> select zz from zz where contains ( *,'sta')
> |--Hash Match(Left Semi Join, HASH[zz].[zz])=([FULLTEXT:zz].[KEY]),
> RESIDUAL[zz].[zz]=[FULLTEXT:zz].[KEY])) 40 3 1
> Hash Match Left Semi Join
> HASH[zz].[zz])=([FULLTEXT:zz].[KEY]),
> RESIDUAL[zz].[zz]=[FULLTEXT:zz].[KEY]) NULL 1.0
> 0.0 2.4346894E-2 36
0.42534244
> [zz].[zz] NULL PLAN_ROW 0
> 1.0
> |--Clustered Index Scan(OBJECT[base].[dbo].[zz].[PK_zz]))
> 40 4 3
> Clustered Index Scan Clustered Index Scan
> OBJECT[base].[dbo].[zz].[PK_zz])
> [zz].[zz] 2.0 3.7578501E-2
> 8.0700003E-5 76 3.7659202E-2 [zz].[zz]
> NULL PLAN_ROW 0 1.0
> |--Remote Scan(OBJECTCONTAINS))
|||Thanks. I did check in event viewer and came across this warning when I tried
to do the full load
The crawl seed <MSSQL75://SQLServer/182c9b23> in project <SQLServer
SQL0000500005> cannot be accessed. Error: 80040e09 - Permission denied. .
also
select FulltextCatalogProperty('zz', 'ItemCount')
0
mangesh
"Hilary Cotter" wrote:

> Can you post the results of the following queries?
> select FulltextCatalogProperty('CatalogName', 'ItemCount')
> replacing CatalogName with the name of your catalog. If the value is 1 or
> the number of tables you are full text indexing you will have to run a
> population. If you have already run a population, check the application log
> using event viewer to see if there are error messages from MSSCI or
> MSSearch.
> --
> 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
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:3E800AE8-603F-4ADF-BCE8-1C3B27174547@.microsoft.com...
> 0.42534244
>
>
|||The below kb addresses the most frequent cause of this problem.
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
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:530DC499-0431-4030-A02A-5658AC5E3EE5@.microsoft.com...
> Thanks. I did check in event viewer and came across this warning when I
tried[vbcol=seagreen]
> to do the full load
> The crawl seed <MSSQL75://SQLServer/182c9b23> in project <SQLServer
> SQL0000500005> cannot be accessed. Error: 80040e09 - Permission denied. .
> also
> select FulltextCatalogProperty('zz', 'ItemCount')
> 0
> mangesh
> "Hilary Cotter" wrote:
or[vbcol=seagreen]
log[vbcol=seagreen]
in[vbcol=seagreen]
working[vbcol=seagreen]
HASH[zz].[zz])=([FULLTEXT:zz].[KEY]),[vbcol=seagreen]
0[vbcol=seagreen]
[zz].[zz][vbcol=seagreen]

Full text search

Hi
I am testing the usage of FULL TEXT feature and somehow it is not working
for me.
I have 2 rows in a table zz with values
this is a sta test
test
Now when I run the following query I do not get any rows back
but the full text index is used.
select zz from zz where contains ( *,'sta')
|--Hash Match(Left Semi Join, HASH[zz].[zz])=([FULLTEXT:zz].&#
91;KEY]),
RESIDUAL[zz].[zz]=[FULLTEXT:zz].[KEY])) 40 3
1
Hash Match Left Semi Join
HASH[zz].[zz])=([FULLTEXT:zz].[KEY]),
RESIDUAL[zz].[zz]=[FULLTEXT:zz].[KEY]) NULL 1.0
0.0 2.4346894E-2 36 0.42534244
[zz].[zz] NULL PLAN_ROW 0
1.0
|--Clustered Index Scan(OBJECT[base].[dbo].[zz].[PK_zz]))
40 4 3
Clustered Index Scan Clustered Index Scan
OBJECT[base].[dbo].[zz].[PK_zz])
[zz].[zz] 2.0 3.7578501E-2
8.0700003E-5 76 3.7659202E-2 [zz].[
zz]
NULL PLAN_ROW 0 1.0
|--Remote Scan(OBJECTCONTAINS))Can you post the results of the following queries?
select FulltextCatalogProperty('CatalogName', 'ItemCount')
replacing CatalogName with the name of your catalog. If the value is 1 or
the number of tables you are full text indexing you will have to run a
population. If you have already run a population, check the application log
using event viewer to see if there are error messages from MSSCI or
MSSearch.
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
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:3E800AE8-603F-4ADF-BCE8-1C3B27174547@.microsoft.com...
> Hi
> I am testing the usage of FULL TEXT feature and somehow it is not working
> for me.
> I have 2 rows in a table zz with values
> this is a sta test
> test
> Now when I run the following query I do not get any rows back
> but the full text index is used.
> select zz from zz where contains ( *,'sta')
> |--Hash Match(Left Semi Join, HASH[zz].[zz])=([FULLTEXT:zz
].[KEY]),
> RESIDUAL[zz].[zz]=[FULLTEXT:zz].[KEY])) 40 3
1
> Hash Match Left Semi Join
> HASH[zz].[zz])=([FULLTEXT:zz].[KEY]),
> RESIDUAL[zz].[zz]=[FULLTEXT:zz].[KEY]) NULL 1.
0
> 0.0 2.4346894E-2 36
0.42534244
> [zz].[zz] NULL PLAN_ROW
0
> 1.0
> |--Clustered Index Scan(OBJECT[base].[dbo].[zz].[
PK_zz]))
> 40 4 3
> Clustered Index Scan Clustered Index Scan
> OBJECT[base].[dbo].[zz].[PK_zz])
> [zz].[zz] 2.0 3.7578501E-2
> 8.0700003E-5 76 3.7659202E-2 [zz].
1;zz]
> NULL PLAN_ROW 0 1.0
> |--Remote Scan(OBJECTCONTAINS))|||Thanks. I did check in event viewer and came across this warning when I trie
d
to do the full load
The crawl seed <MSSQL75://SQLServer/182c9b23> in project <SQLServer
SQL0000500005> cannot be accessed. Error: 80040e09 - Permission denied. .
also
select FulltextCatalogProperty('zz', 'ItemCount')
0
mangesh
"Hilary Cotter" wrote:

> Can you post the results of the following queries?
> select FulltextCatalogProperty('CatalogName', 'ItemCount')
> replacing CatalogName with the name of your catalog. If the value is 1 or
> the number of tables you are full text indexing you will have to run a
> population. If you have already run a population, check the application lo
g
> using event viewer to see if there are error messages from MSSCI or
> MSSearch.
> --
> 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
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:3E800AE8-603F-4ADF-BCE8-1C3B27174547@.microsoft.com...
> 0.42534244
>
>|||The below kb addresses the most frequent cause of this problem.
http://support.microsoft.com/defaul...kb;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
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:530DC499-0431-4030-A02A-5658AC5E3EE5@.microsoft.com...
> Thanks. I did check in event viewer and came across this warning when I
tried[vbcol=seagreen]
> to do the full load
> The crawl seed <MSSQL75://SQLServer/182c9b23> in project <SQLServer
> SQL0000500005> cannot be accessed. Error: 80040e09 - Permission denied. .
> also
> select FulltextCatalogProperty('zz', 'ItemCount')
> 0
> mangesh
> "Hilary Cotter" wrote:
>
or[vbcol=seagreen]
log[vbcol=seagreen]
in[vbcol=seagreen]
working[vbcol=seagreen]
HASH[zz].[zz])=([FULLTEXT:zz].[KEY]),[vbcol=seagreen]
0[vbcol=seagreen]
[zz].[zz][vbcol=seagreen]

Full text search

Hi
I am testing the usage of FULL TEXT feature and somehow it is not working
for me.
I have 2 rows in a table zz with values
this is a sta test
test
Now when I run the following query I do not get any rows back
but the full text index is used.
select zz from zz where contains ( *,'sta')
|--Hash Match(Left Semi Join, HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY])) 40 3 1
Hash Match Left Semi Join
HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY]) NULL 1.0
0.0 2.4346894E-2 36 0.42534244
[zz].[zz] NULL PLAN_ROW 0
1.0
|--Clustered Index Scan(OBJECT:([base].[dbo].[zz].[PK_zz]))
40 4 3
Clustered Index Scan Clustered Index Scan
OBJECT:([base].[dbo].[zz].[PK_zz])
[zz].[zz] 2.0 3.7578501E-2
8.0700003E-5 76 3.7659202E-2 [zz].[zz]
NULL PLAN_ROW 0 1.0
|--Remote Scan(OBJECT:(CONTAINS))Can you post the results of the following queries?
select FulltextCatalogProperty('CatalogName', 'ItemCount')
replacing CatalogName with the name of your catalog. If the value is 1 or
the number of tables you are full text indexing you will have to run a
population. If you have already run a population, check the application log
using event viewer to see if there are error messages from MSSCI or
MSSearch.
--
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
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:3E800AE8-603F-4ADF-BCE8-1C3B27174547@.microsoft.com...
> Hi
> I am testing the usage of FULL TEXT feature and somehow it is not working
> for me.
> I have 2 rows in a table zz with values
> this is a sta test
> test
> Now when I run the following query I do not get any rows back
> but the full text index is used.
> select zz from zz where contains ( *,'sta')
> |--Hash Match(Left Semi Join, HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
> RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY])) 40 3 1
> Hash Match Left Semi Join
> HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
> RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY]) NULL 1.0
> 0.0 2.4346894E-2 36
0.42534244
> [zz].[zz] NULL PLAN_ROW 0
> 1.0
> |--Clustered Index Scan(OBJECT:([base].[dbo].[zz].[PK_zz]))
> 40 4 3
> Clustered Index Scan Clustered Index Scan
> OBJECT:([base].[dbo].[zz].[PK_zz])
> [zz].[zz] 2.0 3.7578501E-2
> 8.0700003E-5 76 3.7659202E-2 [zz].[zz]
> NULL PLAN_ROW 0 1.0
> |--Remote Scan(OBJECT:(CONTAINS))|||Thanks. I did check in event viewer and came across this warning when I tried
to do the full load
The crawl seed <MSSQL75://SQLServer/182c9b23> in project <SQLServer
SQL0000500005> cannot be accessed. Error: 80040e09 - Permission denied. .
also
select FulltextCatalogProperty('zz', 'ItemCount')
0
mangesh
"Hilary Cotter" wrote:
> Can you post the results of the following queries?
> select FulltextCatalogProperty('CatalogName', 'ItemCount')
> replacing CatalogName with the name of your catalog. If the value is 1 or
> the number of tables you are full text indexing you will have to run a
> population. If you have already run a population, check the application log
> using event viewer to see if there are error messages from MSSCI or
> MSSearch.
> --
> 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
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:3E800AE8-603F-4ADF-BCE8-1C3B27174547@.microsoft.com...
> > Hi
> >
> > I am testing the usage of FULL TEXT feature and somehow it is not working
> > for me.
> > I have 2 rows in a table zz with values
> > this is a sta test
> > test
> >
> > Now when I run the following query I do not get any rows back
> > but the full text index is used.
> >
> > select zz from zz where contains ( *,'sta')
> > |--Hash Match(Left Semi Join, HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
> > RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY])) 40 3 1
> > Hash Match Left Semi Join
> > HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
> > RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY]) NULL 1.0
> > 0.0 2.4346894E-2 36
> 0.42534244
> > [zz].[zz] NULL PLAN_ROW 0
> > 1.0
> > |--Clustered Index Scan(OBJECT:([base].[dbo].[zz].[PK_zz]))
> > 40 4 3
> > Clustered Index Scan Clustered Index Scan
> > OBJECT:([base].[dbo].[zz].[PK_zz])
> > [zz].[zz] 2.0 3.7578501E-2
> > 8.0700003E-5 76 3.7659202E-2 [zz].[zz]
> > NULL PLAN_ROW 0 1.0
> > |--Remote Scan(OBJECT:(CONTAINS))
>
>|||The below kb addresses the most frequent cause of this problem.
http://support.microsoft.com/default.aspx?scid=kb;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
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:530DC499-0431-4030-A02A-5658AC5E3EE5@.microsoft.com...
> Thanks. I did check in event viewer and came across this warning when I
tried
> to do the full load
> The crawl seed <MSSQL75://SQLServer/182c9b23> in project <SQLServer
> SQL0000500005> cannot be accessed. Error: 80040e09 - Permission denied. .
> also
> select FulltextCatalogProperty('zz', 'ItemCount')
> 0
> mangesh
> "Hilary Cotter" wrote:
> > Can you post the results of the following queries?
> >
> > select FulltextCatalogProperty('CatalogName', 'ItemCount')
> >
> > replacing CatalogName with the name of your catalog. If the value is 1
or
> > the number of tables you are full text indexing you will have to run a
> > population. If you have already run a population, check the application
log
> > using event viewer to see if there are error messages from MSSCI or
> > MSSearch.
> >
> > --
> > 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
> > "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote
in
> > message news:3E800AE8-603F-4ADF-BCE8-1C3B27174547@.microsoft.com...
> > > Hi
> > >
> > > I am testing the usage of FULL TEXT feature and somehow it is not
working
> > > for me.
> > > I have 2 rows in a table zz with values
> > > this is a sta test
> > > test
> > >
> > > Now when I run the following query I do not get any rows back
> > > but the full text index is used.
> > >
> > > select zz from zz where contains ( *,'sta')
> > > |--Hash Match(Left Semi Join,
HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
> > > RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY])) 40 3 1
> > > Hash Match Left Semi Join
> > > HASH:([zz].[zz])=([FULLTEXT:zz].[KEY]),
> > > RESIDUAL:([zz].[zz]=[FULLTEXT:zz].[KEY]) NULL 1.0
> > > 0.0 2.4346894E-2 36
> > 0.42534244
> > > [zz].[zz] NULL PLAN_ROW
0
> > > 1.0
> > > |--Clustered Index Scan(OBJECT:([base].[dbo].[zz].[PK_zz]))
> > > 40 4 3
> > > Clustered Index Scan Clustered Index Scan
> > > OBJECT:([base].[dbo].[zz].[PK_zz])
> > > [zz].[zz] 2.0 3.7578501E-2
> > > 8.0700003E-5 76 3.7659202E-2
[zz].[zz]
> > > NULL PLAN_ROW 0 1.0
> > > |--Remote Scan(OBJECT:(CONTAINS))
> >
> >
> >

Full Text Max Size?

Hi,

Does anyone have experience with Full Text catalogs on large tables? We have a full text catalog on a table with about 30 million rows. Acording to BOL, once you get over 1 million you'll need to make some adjustements. Our system works, but we randomly get the following errors with ad hoc queries. The server has 8GB of RAM and 4 3GHz processors. Does anyone have experience working with a table this big? Any suggetions as to what could cause these errors? The only thing I could find was BUG#: 469483 on MS's site, but we're not using any OR clauses

Thanks

Here's the errors...

(query)
SELECT * FROM acc_results ar
WHERE CONTAINS(finding_text, 'cell')

#1
Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. Not enough storage is available to process this command.


#2
Server: Msg 7342, Level 16, State 1, Line 1
Unexpected NULL value returned for column '[FULLTEXT:acc_results].KEY' from the OLE DB provider 'Full-text Search Engine'. This column cannot be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL value returned for the column: ProviderName='Full-text Search Engine', TableName='[FULLTEXT:acc_results]', ColumnName='KEY'].


#3
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Full-text Search Engine' reported an error.
[OLE/DB provider returned message: Not enough storage is available to process this command.]
OLE DB error trace [OLE/DB Provider 'Full-text Search Engine' IRowset::GetNextRows returned 0x80004005: ].just taking #1 and #3 at face value (I know nothing is what it seems), I would imagine you need more space on your hard drive.

Full Text Max Size?

Hi,
Does anyone have experience with Full Text catalogs on large tables? We
have a full text catalog on a table with about 30 million rows. Acording
to BOL, once you get over 1 million you'll need to make some
adjustements. Our system works, but often we get the following errors
with ad hoc queries. The server has 8GB of RAM and 4 3GHz processors.
Does anyone have experience working with a table this big? Any
suggetions as to what could cause these errors? The only thing I could
find was BUG#: 469483 on MS's site, but we're not using any OR clauses
Thanks
Here's the errors...
(query)
SELECT * FROM acc_results ar
WHERE CONTAINS(finding_text, 'cell')
#1
Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. Not enough storage is
available to process this command.
#2
Server: Msg 7342, Level 16, State 1, Line 1
Unexpected NULL value returned for column '[FULLTEXT:acc_results].KEY'
from the OLE DB provider 'Full-text Search Engine'. This column cannot
be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL value returned
for the column: ProviderName='Full-text Search Engine',
TableName='[FULLTEXT:acc_results]', ColumnName='KEY'].
#3
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Full-text Search Engine' reported an error.
[OLE/DB provider returned message: Not enough storage is available to
process this command.]
OLE DB error trace [OLE/DB Provider 'Full-text Search Engine'
IRowset::GetNextRows returned 0x80004005: ].
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Not enough storage is a common OS related error - relating to available
memory I think. Can you do things like surf on this machine, map network
drives, etc? I have heard of a 35 million row SQL FTS search solution -
IIRC.
SQL FTS 2005 has been tested to 2 billion rows.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:%23SjvpQb%23EHA.3988@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone have experience with Full Text catalogs on large tables? We
> have a full text catalog on a table with about 30 million rows. Acording
> to BOL, once you get over 1 million you'll need to make some
> adjustements. Our system works, but often we get the following errors
> with ad hoc queries. The server has 8GB of RAM and 4 3GHz processors.
> Does anyone have experience working with a table this big? Any
> suggetions as to what could cause these errors? The only thing I could
> find was BUG#: 469483 on MS's site, but we're not using any OR clauses
> Thanks
> Here's the errors...
> (query)
> SELECT * FROM acc_results ar
> WHERE CONTAINS(finding_text, 'cell')
> #1
> Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. Not enough storage is
> available to process this command.
>
> #2
> Server: Msg 7342, Level 16, State 1, Line 1
> Unexpected NULL value returned for column '[FULLTEXT:acc_results].KEY'
> from the OLE DB provider 'Full-text Search Engine'. This column cannot
> be NULL.
> OLE DB error trace [Non-interface error: Unexpected NULL value returned
> for the column: ProviderName='Full-text Search Engine',
> TableName='[FULLTEXT:acc_results]', ColumnName='KEY'].
>
> #3
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Full-text Search Engine' reported an error.
> [OLE/DB provider returned message: Not enough storage is available to
> process this command.]
> OLE DB error trace [OLE/DB Provider 'Full-text Search Engine'
> IRowset::GetNextRows returned 0x80004005: ].
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Everything else seems to work fine. What is IIRC? I searched on it,
but got a bunch of different results back.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||IIRC = If I Recall Correctly.
Ok, can you review your application log for events from MSSearch saying
master merge paused.
Also is this a cluster?
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:elauz0b%23EHA.3368@.TK2MSFTNGP10.phx.gbl...
> Everything else seems to work fine. What is IIRC? I searched on it,
> but got a bunch of different results back.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Charlie,
Yes, I do. First of all, what is the exact version of SQL Server (7.0 or
2000) and on what OS platform is it deployed? Could you post the full output
of -- SELECT @.@.version -- as this is VERY helpful information in
troubleshooting such SQL FTS performance issues!
Secondly, and depending upon what your is your SQL Server version and SP
level as well as the exact query that your are getting these errors, you may
want to checkout KB article 818883 "PRB: You may receive error 7619 when you
run a full-text query that contains many OR operators in the search string"
at http://support.microsoft.com/default...b;en-us;818883 as one of
the symptoms is error: "Not enough storage is available to complete this
operation" as this occurs on SQL Server 2000 SP3 and when your CONTAINS
query contains many logical OR operators.
However, because of the larger number of rows (30 million), in your
FT-enable table, you may have encountered additional problems related to the
Master Merge. Specifically, you should review your server's Application
Event log for "Microsoft Search" or MssCi source events - errors, warnings
AND informational for any related Master Merge paused conditions.
Furthermore, you should review the Performance Monitor (Perfmon) counters
for Microsoft Gatherer: Reason to back off, Microsoft Gatherer Projects:
Gatherer Paused Flag, and Microsoft Search Indexer Catalogs: Merge Progress
to determine if there is an existing issue with the Master Merge process.
Depending upon your reply, I can provide you with more specific
recommendations.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:#SjvpQb#EHA.3988@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone have experience with Full Text catalogs on large tables? We
> have a full text catalog on a table with about 30 million rows. Acording
> to BOL, once you get over 1 million you'll need to make some
> adjustements. Our system works, but often we get the following errors
> with ad hoc queries. The server has 8GB of RAM and 4 3GHz processors.
> Does anyone have experience working with a table this big? Any
> suggetions as to what could cause these errors? The only thing I could
> find was BUG#: 469483 on MS's site, but we're not using any OR clauses
> Thanks
> Here's the errors...
> (query)
> SELECT * FROM acc_results ar
> WHERE CONTAINS(finding_text, 'cell')
> #1
> Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. Not enough storage is
> available to process this command.
>
> #2
> Server: Msg 7342, Level 16, State 1, Line 1
> Unexpected NULL value returned for column '[FULLTEXT:acc_results].KEY'
> from the OLE DB provider 'Full-text Search Engine'. This column cannot
> be NULL.
> OLE DB error trace [Non-interface error: Unexpected NULL value returned
> for the column: ProviderName='Full-text Search Engine',
> TableName='[FULLTEXT:acc_results]', ColumnName='KEY'].
>
> #3
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Full-text Search Engine' reported an error.
> [OLE/DB provider returned message: Not enough storage is available to
> process this command.]
> OLE DB error trace [OLE/DB Provider 'Full-text Search Engine'
> IRowset::GetNextRows returned 0x80004005: ].
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Charlie,
A quick follow-up... I think you may benefit from this KB article:
303459 (Q303459) "INF: How to Improve the Performance of FTS Queries for
Large Tables" http://support.microsoft.com/?id=303459 and make changes to
the MaxPropStoreCachedSize registry key value as well as use the Top_N_Rank
parameter in all of your SQL FTS queries, per KB article: 240833 (Q240833)
"FIX: Full-Text Search Performance Improved via Support for TOP"
http://support.microsoft.com//defaul...b;EN-US;240833
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Kane" <jt-kane@.comcast.net> wrote in message
news:u3WGySe#EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Charlie,
> Yes, I do. First of all, what is the exact version of SQL Server (7.0 or
> 2000) and on what OS platform is it deployed? Could you post the full
output
> of -- SELECT @.@.version -- as this is VERY helpful information in
> troubleshooting such SQL FTS performance issues!
> Secondly, and depending upon what your is your SQL Server version and SP
> level as well as the exact query that your are getting these errors, you
may
> want to checkout KB article 818883 "PRB: You may receive error 7619 when
you
> run a full-text query that contains many OR operators in the search
string"
> at http://support.microsoft.com/default...b;en-us;818883 as one
of
> the symptoms is error: "Not enough storage is available to complete this
> operation" as this occurs on SQL Server 2000 SP3 and when your CONTAINS
> query contains many logical OR operators.
> However, because of the larger number of rows (30 million), in your
> FT-enable table, you may have encountered additional problems related to
the
> Master Merge. Specifically, you should review your server's Application
> Event log for "Microsoft Search" or MssCi source events - errors, warnings
> AND informational for any related Master Merge paused conditions.
> Furthermore, you should review the Performance Monitor (Perfmon) counters
> for Microsoft Gatherer: Reason to back off, Microsoft Gatherer
Projects:
> Gatherer Paused Flag, and Microsoft Search Indexer Catalogs: Merge
Progress
> to determine if there is an existing issue with the Master Merge process.
> Depending upon your reply, I can provide you with more specific
> recommendations.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Charlie Pyne" <cpyne@.partners.org> wrote in message
> news:#SjvpQb#EHA.3988@.TK2MSFTNGP11.phx.gbl...
>
|||Hi John,
Thanks for the reply. I'm not at work so I can't run @.@.version at this
time, but I can tell you that this server is SQL 2000 Enterprise Edition
with SP3a on Win2k Advanced Server with SP3.
We do have the MaxPropStoreCachedSize parameter in place and currently
it is set to 2000. The size of those two files listed in the article is
> 2GB. One thing I noticed last night when testing this against our test
server, (same hardware/os/sql), is the errors don't seem to happen when
this parameter is removed. The mssearch.exe process stays around 100MB
and the query completes successfully. With this key in place, the
memory used by mssearch grows to around 1 or 1.5GB and then we get the
"not enough storage" error. Mssearch.exe then just sits there holding
that amount of ram until we stop/start the search service.
I haven't had a chance to experiment with this yet, but someone else
thought that both the /3GB and /PAE switches that are currently present
could be causing problems. SQL is configured to only use 4GB of the 8GB
total, but for some reason performance of non-full text queries take a
major hit when we remove /3GB. WE've tried making the same 4GB
available to SQL with just the /PAE switch, but it doesn't seem to work
as well.
I'll check for the event log errors that you mentioned next week.
Thanks for your help. Please let me know if you have any other ideas.
Charlie
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||You're welcome, Charlie,
When you get to work, also check the server's Application event log for
"Microsoft Search" or MssCi source events - errors, warnings AND
informational for any related Master Merge paused conditions as this where
you'll find the true cause of what is causing the MSSearch service to appear
to hang. Additionally, you may want to start with smaller numbers for
setting the MaxPropStoreCachedSize value, say 500 MB to 1000 (or 1GB) and
then test until you get the failure as tuning this parameter is not an exact
science.
No, using the /3GB and /PAE switches is not the problem as the MSSearch
service with the resource_usage level set to 5 (dedicated) can only use a
max. of 512MB of RAM, if and only if this memory is available and not used
by any other process. Furthermore, the MSSearch service cannot use the
memory above the 2Gb limit.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:uVSXfgj#EHA.600@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> Thanks for the reply. I'm not at work so I can't run @.@.version at this
> time, but I can tell you that this server is SQL 2000 Enterprise Edition
> with SP3a on Win2k Advanced Server with SP3.
> We do have the MaxPropStoreCachedSize parameter in place and currently
> it is set to 2000. The size of those two files listed in the article is
> server, (same hardware/os/sql), is the errors don't seem to happen when
> this parameter is removed. The mssearch.exe process stays around 100MB
> and the query completes successfully. With this key in place, the
> memory used by mssearch grows to around 1 or 1.5GB and then we get the
> "not enough storage" error. Mssearch.exe then just sits there holding
> that amount of ram until we stop/start the search service.
> I haven't had a chance to experiment with this yet, but someone else
> thought that both the /3GB and /PAE switches that are currently present
> could be causing problems. SQL is configured to only use 4GB of the 8GB
> total, but for some reason performance of non-full text queries take a
> major hit when we remove /3GB. WE've tried making the same 4GB
> available to SQL with just the /PAE switch, but it doesn't seem to work
> as well.
> I'll check for the event log errors that you mentioned next week.
> Thanks for your help. Please let me know if you have any other ideas.
> Charlie
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi John,
I don't see any errors related to Master Merge. I do see this error a
bunch of times during our initial full population...
The entry <MSSQL75://SQLSERVER/286302EC/00000342> in the hash map on
project <SQLServer SQL0000600005> cannot be updated. Error: 8007054e - A
description for this error could not be found.
MS had told us to set MaxPropStoreCachedSize based on the size of these
two files...
00000001.ps1 = 775MB
00000001.ps2 = 2.26GB
If I set this to 256, the problem seems to go away. With 256 set, I've
let the SELECT * query that we've been using to reproduce the error run
for about 10 minutes before I cancel it. With 2000 set, we'll get the
"not enough storage" error after about 2 minutes.
We also have the priorty of the service set to the default of 3. I
haven't found much in terms of how this should be set other than
1=background and 5=dedicated. Do you have any suggestions? This is a
system that is used by doctors to save case notes and query historical
notes using full text and standard sql queries. We'd like to maximize
the performance of full text, but we don't want to have a negative
effect on regular SQL. We'd also like to cut down on the time needed
for full & incremental rebuilds. Fulls now take 1 week and incrementals
take about 1 day. From what I've read, we should be using change
tracking. We plan on doing this as soon as we have this current problem
resolved. Any other suggestions beyond this?
Thanks for your help
Charlie
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Charlie,
With SQL Server 2000 FTS you're somewhat limited with tables of this scale.
You could try to horizontally partition the table into multiple smaller
tables (say partition on a date range or some other column specific range of
values) and the FT Index the smaller tables using separate FT Catalogs. I'd
also highly recommend that you use CONTAINSTABLE or FREETEXTTABLE with the
Top_N_Rank value (see KB article Q240833) when running FTS SELECT * queries
against the larger FT Catalog. I'd also highly recommend that you look at
SQL Server 2005 as many of these limitations go away with Yukon FTS.
If you're 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. However, this will not fix the error
caused by large *.ps* files, as does setting the MaxPropStoreCachedSize
registry key value.
Once you've resolved this issue, use Change tracking with Update Index in
background, but be aware that an automatic Incremental or Full Population
will be run when you set this feature, so the MSSearch service can re-sync
up the FT Catalog with the SQL table data.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:OeQeKOY$EHA.4092@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> I don't see any errors related to Master Merge. I do see this error a
> bunch of times during our initial full population...
> The entry <MSSQL75://SQLSERVER/286302EC/00000342> in the hash map on
> project <SQLServer SQL0000600005> cannot be updated. Error: 8007054e - A
> description for this error could not be found.
> MS had told us to set MaxPropStoreCachedSize based on the size of these
> two files...
> 00000001.ps1 = 775MB
> 00000001.ps2 = 2.26GB
> If I set this to 256, the problem seems to go away. With 256 set, I've
> let the SELECT * query that we've been using to reproduce the error run
> for about 10 minutes before I cancel it. With 2000 set, we'll get the
> "not enough storage" error after about 2 minutes.
> We also have the priorty of the service set to the default of 3. I
> haven't found much in terms of how this should be set other than
> 1=background and 5=dedicated. Do you have any suggestions? This is a
> system that is used by doctors to save case notes and query historical
> notes using full text and standard sql queries. We'd like to maximize
> the performance of full text, but we don't want to have a negative
> effect on regular SQL. We'd also like to cut down on the time needed
> for full & incremental rebuilds. Fulls now take 1 week and incrementals
> take about 1 day. From what I've read, we should be using change
> tracking. We plan on doing this as soon as we have this current problem
> resolved. Any other suggestions beyond this?
> Thanks for your help
> Charlie
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

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.
>

Full text index not tracking changes on an indexed view

We had a peculiar situation today where our lovely indexed view
obviously had textual data in it but some recently added rows were not
being returned in a CONTAINS query when they should have been.
Deleting and recreating the FT index fixed things immediately and I
can't seem to reproduce it but it worries me for our live deployment
(due imminently!).
Has anyone else seen this? Is it reproducible (and fixable?!)
Mark
Hi Mark,
Do you have a timestamp column in underlaying table(s) ?
Have you activated automatic change tracking for your index ?
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
<mbedser@.gmail.com> wrote in message
news:1185555299.154393.224920@.d55g2000hsg.googlegr oups.com...
> We had a peculiar situation today where our lovely indexed view
> obviously had textual data in it but some recently added rows were not
> being returned in a CONTAINS query when they should have been.
> Deleting and recreating the FT index fixed things immediately and I
> can't seem to reproduce it but it worries me for our live deployment
> (due imminently!).
> Has anyone else seen this? Is it reproducible (and fixable?!)
> Mark
>
|||Change tracking does not require a timestamp column.
I think your problem will be solved if you do use change tracking (as the
previous poster points out).
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
"Jean-Pierre Riehl" <jean-pierre.riehl@.b-e-w-i-s-e.fr> wrote in message
news:1C0C02A3-0A2F-4294-97CA-59E453DE6780@.microsoft.com...
> Hi Mark,
> Do you have a timestamp column in underlaying table(s) ?
> Have you activated automatic change tracking for your index ?
> --
> Jean-Pierre Riehl
> http://blog.djeepy1.net
> http://www.bewise.fr
> <mbedser@.gmail.com> wrote in message
> news:1185555299.154393.224920@.d55g2000hsg.googlegr oups.com...
>
|||I apologize for my mistake. Incremental update requires a timestamp column
whereas change tracking doesn't.
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23CQlfIT0HHA.1188@.TK2MSFTNGP04.phx.gbl...
> Change tracking does not require a timestamp column.
> I think your problem will be solved if you do use change tracking (as the
> previous poster points out).
> --
> 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
> "Jean-Pierre Riehl" <jean-pierre.riehl@.b-e-w-i-s-e.fr> wrote in message
> news:1C0C02A3-0A2F-4294-97CA-59E453DE6780@.microsoft.com...
>

Full Text Index hanging status=runnable waittype=NETWORKIO

I have a database with 5 tables, each with full text index. 4 of them build
just fine, the 5th, has 10 million rows. The Full Text index never seems to
finish on that one. I've let it run for as long as 36 hours and it never
finishes. When I look in the Management panel, under process info. It tells
me my status is runnable, and waittype is NETWORKIO. I've checked it over
and over and it always is in this state. The MSSEARCH task is running with a
very high % of CPU. Any advice on where I can go from here to figure out
what the problem is?Brian,
Try running a DBCC CHECKTABLE on the table.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:0CD8143A-1D89-496E-BCA7-EE772790D532@.microsoft.com...
>I have a database with 5 tables, each with full text index. 4 of them
>build
> just fine, the 5th, has 10 million rows. The Full Text index never seems
> to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It
> tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running
> with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?|||I figured it out!!!!!!!!!!!!!!!
I looked at the data, and 1 column that I was indexing on, had one word, the
word 'book' for about 7,000,000 rows! I stopped indexing on that column, and
it works fine now!
"Brian Kitt" wrote:
> I have a database with 5 tables, each with full text index. 4 of them build
> just fine, the 5th, has 10 million rows. The Full Text index never seems to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?

Full Text Index hanging status=runnable waittype=NETWORKIO

I have a database with 5 tables, each with full text index. 4 of them build
just fine, the 5th, has 10 million rows. The Full Text index never seems to
finish on that one. I've let it run for as long as 36 hours and it never
finishes. When I look in the Management panel, under process info. It tell
s
me my status is runnable, and waittype is NETWORKIO. I've checked it over
and over and it always is in this state. The MSSEARCH task is running with
a
very high % of CPU. Any advice on where I can go from here to figure out
what the problem is?Brian,
Try running a DBCC CHECKTABLE on the table.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:0CD8143A-1D89-496E-BCA7-EE772790D532@.microsoft.com...
>I have a database with 5 tables, each with full text index. 4 of them
>build
> just fine, the 5th, has 10 million rows. The Full Text index never seems
> to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It
> tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running
> with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?|||I figured it out!!!!!!!!!!!!!!!
I looked at the data, and 1 column that I was indexing on, had one word, the
word 'book' for about 7,000,000 rows! I stopped indexing on that column, an
d
it works fine now!
"Brian Kitt" wrote:

> I have a database with 5 tables, each with full text index. 4 of them bui
ld
> just fine, the 5th, has 10 million rows. The Full Text index never seems
to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It te
lls
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running wit
h a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?

Full Text Index hanging status=runnable waittype=NETWORKIO

I have a database with 5 tables, each with full text index. 4 of them build
just fine, the 5th, has 10 million rows. The Full Text index never seems to
finish on that one. I've let it run for as long as 36 hours and it never
finishes. When I look in the Management panel, under process info. It tells
me my status is runnable, and waittype is NETWORKIO. I've checked it over
and over and it always is in this state. The MSSEARCH task is running with a
very high % of CPU. Any advice on where I can go from here to figure out
what the problem is?
Brian,
Try running a DBCC CHECKTABLE on the table.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:0CD8143A-1D89-496E-BCA7-EE772790D532@.microsoft.com...
>I have a database with 5 tables, each with full text index. 4 of them
>build
> just fine, the 5th, has 10 million rows. The Full Text index never seems
> to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It
> tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running
> with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?
|||I figured it out!!!!!!!!!!!!!!!
I looked at the data, and 1 column that I was indexing on, had one word, the
word 'book' for about 7,000,000 rows! I stopped indexing on that column, and
it works fine now!
"Brian Kitt" wrote:

> I have a database with 5 tables, each with full text index. 4 of them build
> just fine, the 5th, has 10 million rows. The Full Text index never seems to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?