incomplete Full text index for large dbs in sql 2005?

we have created a full text catalogue on an Addresses table with exactly same
configuration on 3 databases. Two of them are on SQL 2005 and one on SQL 2000.
My Addresses table has 13 million rows and the full text was on the Full
Address column.
db is around 250GB
FT Index on SQL 2005 database was created very quickly (within 2 hours)
while SQL 2000 FT took 30 hours.
But my issue is SQL 2005 index does not contain all the words. For an
example following 2 queries gives me the exactly same number on the SQL 2000
database while on SQL 2005, first query only return 11 and the 2nd one 274
(274 is same on 2000 server/database as well)
SELECT count(*)
FROM [dbo].[Addresses]
WHERE CONTAINS(FullAddress, 'WARMINSTER')
GO
SELECT count(*)
FROM [dbo].[Addresses]
WHERE FullAddress like '%WARMINSTER%'
Is this a bug?
When i extracted the records which should have retrived in to a seperate
table (few hundred records) and created a FT index on that it works fine.
CTS DBA
I've had problems on very large databases with this. I think we were over
400,000,000 rows when we first noticed it. A reorganize would normally help.
Can you first run this query?
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
See if perhaps you are getting hits to Warministershireontheavon or
something like that. Note that most punctuation will be ignored.
If you are trully missing hits can you check the gatherer log to see if
there are any error messages?
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
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@.microsoft.com...
> we have created a full text catalogue on an Addresses table with exactly
> same
> configuration on 3 databases. Two of them are on SQL 2005 and one on SQL
> 2000.
> My Addresses table has 13 million rows and the full text was on the Full
> Address column.
> db is around 250GB
> FT Index on SQL 2005 database was created very quickly (within 2 hours)
> while SQL 2000 FT took 30 hours.
> But my issue is SQL 2005 index does not contain all the words. For an
> example following 2 queries gives me the exactly same number on the SQL
> 2000
> database while on SQL 2005, first query only return 11 and the 2nd one 274
> (274 is same on 2000 server/database as well)
> SELECT count(*)
> FROM [dbo].[Addresses]
> WHERE CONTAINS(FullAddress, 'WARMINSTER')
> GO
> SELECT count(*)
> FROM [dbo].[Addresses]
> WHERE FullAddress like '%WARMINSTER%'
> Is this a bug?
> When i extracted the records which should have retrived in to a seperate
> table (few hundred records) and created a FT index on that it works fine.
>
> --
> CTS DBA
|||Thanks Hilary,
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
The Query returns only 4 records which means still so many records missing
in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
CATALOG cat_Addresses REORGANIZE) and update you with the results when it’s
completed.
thanks
CTS DBA
"Hilary Cotter" wrote:

> I've had problems on very large databases with this. I think we were over
> 400,000,000 rows when we first noticed it. A reorganize would normally help.
> Can you first run this query?
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> See if perhaps you are getting hits to Warministershireontheavon or
> something like that. Note that most punctuation will be ignored.
> If you are trully missing hits can you check the gatherer log to see if
> there are any error messages?
>
> --
> 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
>
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@.microsoft.com...
>
>
|||Re-organize has been completed. But still no luck. And there is nothing in
the gatherer logs as well. See below from the gatherer logs for the entries
from re-organize and re-building I did today.
RE_BUILDING
2006-11-29 09:28:15.58 spid20s Informational: Full-text Full population
completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]' (table
or indexed view ID '101575400', database ID '41'). Number of documents
processed: 10038019. Number of documents failed: 0. Number of documents need
retry: 0.
2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41). This
is an informational message only. No user action is required.
2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto population
initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]' (table
or indexed view ID '101575400', database ID '41'). Population sub-tasks: 1.
RE-ORGANIZING
2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41). This
is an informational message only. No user action is required.
CTS DBA
"CTS_DBA" wrote:
[vbcol=seagreen]
> Thanks Hilary,
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> The Query returns only 4 records which means still so many records missing
> in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
> CATALOG cat_Addresses REORGANIZE) and update you with the results when it’s
> completed.
> thanks
>
> --
> CTS DBA
>
> "Hilary Cotter" wrote:
|||It looks like all the rows were successfully processed. My concern still is
that perhaps there are some preceding or trailing characters which might
account for these rows not showing up. Can you confirm this?
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
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...[vbcol=seagreen]
> Re-organize has been completed. But still no luck. And there is nothing in
> the gatherer logs as well. See below from the gatherer logs for the
> entries
> from re-organize and re-building I did today.
> RE_BUILDING
> 2006-11-29 09:28:15.58 spid20s Informational: Full-text Full
> population
> completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Number of documents
> processed: 10038019. Number of documents failed: 0. Number of documents
> need
> retry: 0.
> 2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
> 2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto
> population
> initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Population sub-tasks:
> 1.
>
>
> RE-ORGANIZING
> 2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
>
> --
> CTS DBA
>
> "CTS_DBA" wrote:
|||yep, cause as mentioned earlier same DB, sql 2000 FT returns the expected
result
the other thing I'm worried is that sql 2005 took 2 hours and 2000 took
around 30 hours, huge different, and of course servers are , you can sya same
hard ware.
CTS DBA
"Hilary Cotter" wrote:

> It looks like all the rows were successfully processed. My concern still is
> that perhaps there are some preceding or trailing characters which might
> account for these rows not showing up. Can you confirm this?
> --
> 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
>
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...
>
>
|||What if you run
SELECT fulladdress FROM [dbo].[Addresses] WHERE
FullAddress LIKE '%WARMINSTER%'
AND NOT CONTAINS(FullAddress, 'WARMINSTER')
do the fulladdress results look funny in any way, or is WARMINSTER at
the start of a word? Maybe a non-ASCII character in there somehow?
Ben Strackany
CTS_DBA wrote:[vbcol=seagreen]
> Thanks Hilary,
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> The Query returns only 4 records which means still so many records missing
> in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
> CATALOG cat_Addresses REORGANIZE) and update you with the results when it's
> completed.
> thanks
>
> --
> CTS DBA
>
> "Hilary Cotter" wrote:
|||This says everything processed successfully.
I am really not sure how to advice you on where to go from here.
One of my lingering concerns is that this query
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
returns 4 results - what are they?
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
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...[vbcol=seagreen]
> Re-organize has been completed. But still no luck. And there is nothing in
> the gatherer logs as well. See below from the gatherer logs for the
> entries
> from re-organize and re-building I did today.
> RE_BUILDING
> 2006-11-29 09:28:15.58 spid20s Informational: Full-text Full
> population
> completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Number of documents
> processed: 10038019. Number of documents failed: 0. Number of documents
> need
> retry: 0.
> 2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
> 2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto
> population
> initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Population sub-tasks:
> 1.
>
>
> RE-ORGANIZING
> 2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
>
> --
> CTS DBA
>
> "CTS_DBA" wrote:

Incomplete Exports

Hello,
I hope that someone will be able to help us with a perplexing issue. When
generating a reporting in either Excel or PDF every 5th report or so, we will
get a corrupt / damaged file. The issue is repeatable but not reproducible.
The same report will fail a random number of times in a row, but will then
execute correctly. It appears that the corrupt file is really truncated.
The reports are big, but we don't have any very long columns or many rows.
However, they do contain quite a few charts. The final file size in excel
is ~1.4 MBs.
We have been unable to locate any errors in the logs. The web site
behaves like it successfully created the report. I've watched the memory
usage and it does not appear to be memory bound or disk bound.
I'm hoping for any suggestions on avenues to try or finding someone with a
similar problem.
Thank You,
--Chris Swinefurth
MID Technologies, Inc.You may want to try this:
http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
--
Adrian M.
MCP
"Chris Swinefurth" <Chris Swinefurth@.discussions.microsoft.com> wrote in
message news:241B48D8-8163-4952-920D-1FE019DB92D9@.microsoft.com...
> Hello,
> I hope that someone will be able to help us with a perplexing issue.
> When
> generating a reporting in either Excel or PDF every 5th report or so, we
> will
> get a corrupt / damaged file. The issue is repeatable but not
> reproducible.
> The same report will fail a random number of times in a row, but will then
> execute correctly. It appears that the corrupt file is really truncated.
> The reports are big, but we don't have any very long columns or many
> rows.
> However, they do contain quite a few charts. The final file size in excel
> is ~1.4 MBs.
> We have been unable to locate any errors in the logs. The web site
> behaves like it successfully created the report. I've watched the memory
> usage and it does not appear to be memory bound or disk bound.
> I'm hoping for any suggestions on avenues to try or finding someone with
> a
> similar problem.
> Thank You,
> --Chris Swinefurth
> MID Technologies, Inc.
>|||Adrian,
Thank you for your reply. Unfortuntely, we are unable to utilize ether
suggesting in the KB article. 1) Sending a link to a report will not work as
we're pulling data from a non-database provider and we need a point-in-time
report. I believe that sending a link will produce another execution of the
report when the link is clicked and not when the subscription email is sent.
2) We've tried web archives, but unfortunately, printing becomes an issue.
IE renders extra pages and cuts off parts of the graphs.
Thanks again for your help. Any more suggestions would be greatly
appreciated.
--Chris
"Adrian M." wrote:
> You may want to try this:
> http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> --
> Adrian M.
> MCP
>
> "Chris Swinefurth" <Chris Swinefurth@.discussions.microsoft.com> wrote in
> message news:241B48D8-8163-4952-920D-1FE019DB92D9@.microsoft.com...
> > Hello,
> > I hope that someone will be able to help us with a perplexing issue.
> > When
> > generating a reporting in either Excel or PDF every 5th report or so, we
> > will
> > get a corrupt / damaged file. The issue is repeatable but not
> > reproducible.
> > The same report will fail a random number of times in a row, but will then
> > execute correctly. It appears that the corrupt file is really truncated.
> > The reports are big, but we don't have any very long columns or many
> > rows.
> > However, they do contain quite a few charts. The final file size in excel
> > is ~1.4 MBs.
> > We have been unable to locate any errors in the logs. The web site
> > behaves like it successfully created the report. I've watched the memory
> > usage and it does not appear to be memory bound or disk bound.
> > I'm hoping for any suggestions on avenues to try or finding someone with
> > a
> > similar problem.
> > Thank You,
> > --Chris Swinefurth
> > MID Technologies, Inc.
> >
>
>

Incomplete data in excel fileshare -missing many rows

Hello,
I have a report with 35 columns, meant as a datafeed. The report uses a view
on the (separate) SQL server so it only needs to put the values in the table.
When ik run the report for medium datasets, everything works fine. However
when I run it for more then 20,000 rows the exel-sheet only contains about
16300 rows. The report is meant as a subscription with a fileshare on the
local server. When i run the report via the viewer, an export to excel is
timed out, however an export to csv works and contains all rows. Sadly this
is not the format in which i need to supply the data.
can anybody offer any assistance?What version of Excel are you using? Have you passed the row limit?
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
news:AD96B5F8-4A8C-4D2B-87B2-DE343CF8C3F6@.microsoft.com...
> Hello,
> I have a report with 35 columns, meant as a datafeed. The report uses a
> view
> on the (separate) SQL server so it only needs to put the values in the
> table.
> When ik run the report for medium datasets, everything works fine. However
> when I run it for more then 20,000 rows the exel-sheet only contains about
> 16300 rows. The report is meant as a subscription with a fileshare on the
> local server. When i run the report via the viewer, an export to excel is
> timed out, however an export to csv works and contains all rows. Sadly
> this
> is not the format in which i need to supply the data.
> can anybody offer any assistance?|||I kept testing in the weekeind and it's my bad. RS generates the file fine,
Our sending software seems to truncate the file. Sorry.
"Mary Bray [MVP]" wrote:
> What version of Excel are you using? Have you passed the row limit?
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
> news:AD96B5F8-4A8C-4D2B-87B2-DE343CF8C3F6@.microsoft.com...
> > Hello,
> >
> > I have a report with 35 columns, meant as a datafeed. The report uses a
> > view
> > on the (separate) SQL server so it only needs to put the values in the
> > table.
> >
> > When ik run the report for medium datasets, everything works fine. However
> > when I run it for more then 20,000 rows the exel-sheet only contains about
> > 16300 rows. The report is meant as a subscription with a fileshare on the
> > local server. When i run the report via the viewer, an export to excel is
> > timed out, however an export to csv works and contains all rows. Sadly
> > this
> > is not the format in which i need to supply the data.
> >
> > can anybody offer any assistance?
>
>