SQL Server Performance

Increasing Fetch Buffer Size for Timeout Error

Discussion in 'SQL Server DTS-Related Questions' started by nareshb, Mar 4, 2007.

  1. nareshb New Member

    Hi,

    I am using a DTS package for transferring data from staging tables to warehouse tables. I am using a data driven query task, in which I am using a query to retrieve data from a table and based on the data, I am updating or inserting records in warehouse tables.

    The package was working fine initially. But now, this task gave me TimeOut Expired error 2-3 times. I increased fetch buffer size to 9000 and it started executing successfully. Now the number of rows in my source table is increasing day-by-day and I will not be able to increase fetch buffer size beyond 9999 if it starts erroring out.

    Please help me providing some alternative.

    Regards,
    Naresh
  2. satya Moderator

    Have you used Perfmon to identify where it is having problem using the available resources on the server, during the executio of this package.

    Also try to insert the rows in smaller batches to avoid the such timeouts.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. nareshb New Member

    The problem seems to occur because of some locking involved for the source table. Suppose there are 11000 records in source table and 9999 of them are fetched and processed. By the time this processing is going on, there might be a lock applied on source table which do not allow queries to be run on the source table.

    Is this possible?

    Regards,
    Naresh

Share This Page