Increasing Fetch Buffer Size for Timeout Error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Increasing Fetch Buffer Size for Timeout Error

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