Estimate time to populate full text index

Can someone give me an estimate time to populate a full-
text index, SQL Server 2000:
-14 million rows,
- each row is about 1 paragraph long. (approx 400 words and is stored in a LONG TEXT field)
- Language of the text: mixed.
- W.H.:a single CPU 2.8GHZ with 2GB Ram and have 300GB disk space
-incremental.
it has been running for 3 days now and who knows where we are...
Any way to speed up..
Thanks
mustafa,
Given your hardware configuration, and a table with 14 million rows will
take a substitational amount of time, approx 5 to 8 days.
Your best bet is to attempt to stop the Full or Incremental population and
consider horzatioanlly partitioning the table into several smaller tables,
perhaps broken up by date or PK range. If you plan to continue this, I'd
highly recommend that you consider upgrading all aspects of your hardware.
Specifically, consider a multiple CPU server with 2 or more GB of RAM with
your FT Catalogs on a separate disk controller and disk array configured as
RAID0 or RAID10. You should also set the resource_usage level of the
MSSearch service to 5 via sp_fulltext_service 'resource_usage', 5. However,
this will not help you now. I'd also recommend that you review the SQL
Server 2000 BOL title "Full-text Search Recommendations" and the following
FT Deployment white paper: INF: SQL Server 2000 Full-Text Search Deployment
White Paper at:
http://support.microsoft.com/default...b;en-us;323739
Regards,
John
"mustafa jarrar" <anonymous@.discussions.microsoft.com> wrote in message
news:6EC893FD-DD03-4FA1-BE01-052A19EB03AB@.microsoft.com...
> Can someone give me an estimate time to populate a full-
> text index, SQL Server 2000:
> -14 million rows,
> - each row is about 1 paragraph long. (approx 400 words and is stored in a
LONG TEXT field)
> - Language of the text: mixed.
> - W.H.:a single CPU 2.8GHZ with 2GB Ram and have 300GB disk space
> -incremental.
> it has been running for 3 days now and who knows where we are...
> Any way to speed up..
> Thanks
>

Errors loading a text file into a Sql Server Destination

I am trying to load 14+ million rows from a text file into local Sql Server. I tried using Sql Server destination because it seemed to be faster, but after about 5 million rows it would always fail. See various errors below which I received while trying different variations of FirstRow/LastRow, Timeout, Table lock etc. After spending two days trying to get it to work, I switched to OLE DB Destination and it worked fine. I would like to get the Sql Server Destination working because it seems much faster, but the error messages aren't much help. Any ideas on how to fix?

Also, when I wanted to try just loading a small sample by specifying first row/last row, it would get to the upper limit and then picked up speed and looked like it kept on reading rows of the source file until it failed. I expected it to just reach the limit I set and then stop processing.

[SS_DST tlkpDNBGlobal [41234]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

--
[SS_DST tlkpDNBGlobal [41234]] Error: The attempt to send a row to SQL Server failed with error code 0x80004005.
[DTS.Pipeline] Error: The ProcessInput method on component "SS_DST tlkpDNBGlobal" (41234) failed with error code 0xC02020C7. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
...
[FF_SRC DNBGlobal [6899]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: The PrimeOutput method on component "FF_SRC DNBGlobal" (6899) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

-
After first row/last row (from 1 to 1000000) limit is reached:
[SS_DST tlkpDNBGlobal [41234]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

When trying to do a MaximumCommit = 1000000. Runs up to 1000000 OK then slows down and then error.
[SS_DST tlkpDNBGlobal [41234]] Error: Unable to prepare the SSIS bulk insert for data insertion.

[DTS.Pipeline] Error: The PrimeOutput method on component "FF_SRC DNBGlobal" (6899) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

-
When attempting all in a single batch:
[OLE_DST tlkpDNBGlobal [57133]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.SORT temporary run storage: 156362715561984' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".

The commit size is most likely the problem. SQL Server will be storing those rows in memory until it is time to commit them. I suggest trying different commit sizes until you find one that is optimum.

You tried 1M? Try 500,000 rows - if that works, maybe try more, or if it fails try less. You should find a good value fairly quickly.

if this does not help, let us know more.

Donald Farmer

|||Thanks Donald. I set the MaxInsertCommitSize to 10K and it worked fine.|||

Hi, Donald

I have had the same problem, how / where do i set the commit size ?

Thanks

Agbeko

|||

It wasn't available until SP1. You need to have SP1 installed then you will get MaxInsertCommitSize as a property of SQL Server Destination.

Thanks

Sutha

|||

Thanks for pointing that out Sutha. Remember also that commit size has always been available on the OLEDB Destination with the SQL Server provider.

Donald

|||

Hi, Sutha,

I am new to SQL Server world. Dummy question:

refering to SP1, is it for Windows, SQL Server 2005, or Integration Service?

Thanks

Chuan

|||

They are talking about SP1 for SQL Server 2005

Rafael Salas

|||Thanks Rafael|||

Rafael Salas wrote:

They are talking about SP1 for SQL Server 2005

Rafael Salas

Which includes Integration Services by the way.

John, the only dumb questions are the ones that don't get asked.

-Jamie

Errors loading a text file into a Sql Server Destination

I am trying to load 14+ million rows from a text file into local Sql Server. I tried using Sql Server destination because it seemed to be faster, but after about 5 million rows it would always fail. See various errors below which I received while trying different variations of FirstRow/LastRow, Timeout, Table lock etc. After spending two days trying to get it to work, I switched to OLE DB Destination and it worked fine. I would like to get the Sql Server Destination working because it seems much faster, but the error messages aren't much help. Any ideas on how to fix?

Also, when I wanted to try just loading a small sample by specifying first row/last row, it would get to the upper limit and then picked up speed and looked like it kept on reading rows of the source file until it failed. I expected it to just reach the limit I set and then stop processing.

[SS_DST tlkpDNBGlobal [41234]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

--
[SS_DST tlkpDNBGlobal [41234]] Error: The attempt to send a row to SQL Server failed with error code 0x80004005.
[DTS.Pipeline] Error: The ProcessInput method on component "SS_DST tlkpDNBGlobal" (41234) failed with error code 0xC02020C7. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
...
[FF_SRC DNBGlobal [6899]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: The PrimeOutput method on component "FF_SRC DNBGlobal" (6899) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

-
After first row/last row (from 1 to 1000000) limit is reached:
[SS_DST tlkpDNBGlobal [41234]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

When trying to do a MaximumCommit = 1000000. Runs up to 1000000 OK then slows down and then error.
[SS_DST tlkpDNBGlobal [41234]] Error: Unable to prepare the SSIS bulk insert for data insertion.

[DTS.Pipeline] Error: The PrimeOutput method on component "FF_SRC DNBGlobal" (6899) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

-
When attempting all in a single batch:
[OLE_DST tlkpDNBGlobal [57133]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.SORT temporary run storage: 156362715561984' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".

The commit size is most likely the problem. SQL Server will be storing those rows in memory until it is time to commit them. I suggest trying different commit sizes until you find one that is optimum.

You tried 1M? Try 500,000 rows - if that works, maybe try more, or if it fails try less. You should find a good value fairly quickly.

if this does not help, let us know more.

Donald Farmer

|||Thanks Donald. I set the MaxInsertCommitSize to 10K and it worked fine.|||

Hi, Donald

I have had the same problem, how / where do i set the commit size ?

Thanks

Agbeko

|||

It wasn't available until SP1. You need to have SP1 installed then you will get MaxInsertCommitSize as a property of SQL Server Destination.

Thanks

Sutha

|||

Thanks for pointing that out Sutha. Remember also that commit size has always been available on the OLEDB Destination with the SQL Server provider.

Donald

|||

Hi, Sutha,

I am new to SQL Server world. Dummy question:

refering to SP1, is it for Windows, SQL Server 2005, or Integration Service?

Thanks

Chuan

|||

They are talking about SP1 for SQL Server 2005

Rafael Salas

|||Thanks Rafael|||

Rafael Salas wrote:

They are talking about SP1 for SQL Server 2005

Rafael Salas

Which includes Integration Services by the way.

John, the only dumb questions are the ones that don't get asked.

-Jamie

Errors loading a text file into a Sql Server Destination

I am trying to load 14+ million rows from a text file into local Sql Server. I tried using Sql Server destination because it seemed to be faster, but after about 5 million rows it would always fail. See various errors below which I received while trying different variations of FirstRow/LastRow, Timeout, Table lock etc. After spending two days trying to get it to work, I switched to OLE DB Destination and it worked fine. I would like to get the Sql Server Destination working because it seems much faster, but the error messages aren't much help. Any ideas on how to fix?

Also, when I wanted to try just loading a small sample by specifying first row/last row, it would get to the upper limit and then picked up speed and looked like it kept on reading rows of the source file until it failed. I expected it to just reach the limit I set and then stop processing.

[SS_DST tlkpDNBGlobal [41234]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

--
[SS_DST tlkpDNBGlobal [41234]] Error: The attempt to send a row to SQL Server failed with error code 0x80004005.
[DTS.Pipeline] Error: The ProcessInput method on component "SS_DST tlkpDNBGlobal" (41234) failed with error code 0xC02020C7. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
...
[FF_SRC DNBGlobal [6899]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: The PrimeOutput method on component "FF_SRC DNBGlobal" (6899) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

-
After first row/last row (from 1 to 1000000) limit is reached:
[SS_DST tlkpDNBGlobal [41234]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

When trying to do a MaximumCommit = 1000000. Runs up to 1000000 OK then slows down and then error.
[SS_DST tlkpDNBGlobal [41234]] Error: Unable to prepare the SSIS bulk insert for data insertion.

[DTS.Pipeline] Error: The PrimeOutput method on component "FF_SRC DNBGlobal" (6899) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

-
When attempting all in a single batch:
[OLE_DST tlkpDNBGlobal [57133]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.SORT temporary run storage: 156362715561984' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".

The commit size is most likely the problem. SQL Server will be storing those rows in memory until it is time to commit them. I suggest trying different commit sizes until you find one that is optimum.

You tried 1M? Try 500,000 rows - if that works, maybe try more, or if it fails try less. You should find a good value fairly quickly.

if this does not help, let us know more.

Donald Farmer

|||Thanks Donald. I set the MaxInsertCommitSize to 10K and it worked fine.|||

Hi, Donald

I have had the same problem, how / where do i set the commit size ?

Thanks

Agbeko

|||

It wasn't available until SP1. You need to have SP1 installed then you will get MaxInsertCommitSize as a property of SQL Server Destination.

Thanks

Sutha

|||

Thanks for pointing that out Sutha. Remember also that commit size has always been available on the OLEDB Destination with the SQL Server provider.

Donald

|||

Hi, Sutha,

I am new to SQL Server world. Dummy question:

refering to SP1, is it for Windows, SQL Server 2005, or Integration Service?

Thanks

Chuan

|||

They are talking about SP1 for SQL Server 2005

Rafael Salas

|||Thanks Rafael|||

Rafael Salas wrote:

They are talking about SP1 for SQL Server 2005

Rafael Salas

Which includes Integration Services by the way.

John, the only dumb questions are the ones that don't get asked.

-Jamie