Post reply

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

  • October 4, 2004 at 8:33 pm

    #86432

    I'm receiving the following error message and require help in determining a resolution. I've already looked at similar posts on this site where the recommended solution is to set the CommandTimeout to zero.

    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

    Background Info:

    • SQL Server 2000 (MSDE) with the latest SP.
    • Inserting 65,000+ rows. Each row is 3kb wide. Indexes have been dropped.
    • This operation is part of a transaction where rows are first bulk inserted from 5 tab delimited files into 5 tables without indexes and constraints. After the bulk insert, rows are transferred to other tables.
    • C#/ADO.NET with CommandTimeout=0
    • 1GB RAM
    • Timeout occurs around 5 minutes mark
    • Query Governer = 0
    • Error also occurs when initiated from ISQL/w.

    TIA,

    srv

  • October 4, 2004 at 10:02 pm

    #525312

    Hi srv

    Is the job failing at the same stage every time - ie is the failure linked to elapsed time, or to a particular T-SQL instruction/batch?

    Do you know that the job completes properly with smaller amounts of data? (Need to rule out any logic/application errors which may be inadvertently causing the error.)

    Cheers

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • October 5, 2004 at 9:03 am

    #525426

    Hi Phil,

    Yes, the job fails at the same point each time.  The point of failure is after the bulk insert when I transfer the inserted data to another table. (Around the 5 minute mark)

    Yes, the job completes with smaller amounts of data without error.

    Thanks,

    Paul

  • October 5, 2004 at 5:59 pm

    #525516

    It's looking pretty strange and I'm running out of ideas. Have you checked Event Viewer to see whether any exceptions are being generated?

    Have you thought about breaking the transfer into several batches - eg 10,000 recs a time? There are several posts elsewhere on this site giving examples of how to do this and perhaps the overhead of performing the entire operation as a transaction is hitting some memory/processing limit.

    Good luck

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply