Post reply

Query timeout error on batch job

  • July 14, 2003 at 1:52 pm

    #59338

    Hi Gurus,

    We have clustered SQL server 2000 , and we get the following error when we do our monthly full load to this database which runs for more than 10 hours, ( we get this error within 5 minutes ) .

    General SQL error

    Lock timeout

    Native SQL error 10024

    I have set the Query timeout to infinite n the server properties -> connection page.

    Any ideas / suggestions?.

    TIA

    - Jay

  • July 14, 2003 at 2:52 pm

    #465451

    Check in BOL for

    SET LOCK_TIMEOUT

  • July 14, 2003 at 7:16 pm

    #465475

    Run sp_who2 to check whether this batch job is blocked during its execution. Do you have same error all the time or it just happens occationally when you execute it?

  • July 15, 2003 at 11:46 am

    #465576

    Thanks for the replies.

    This error is intermittent, not regular.

    I checked sp_who2 and saw two sessions were blocke dby other sessions, but I could not find out the exact session that was blocked as all the sessions are using the same login names. Is there a way to find out what is being executed by a particular session to identify the session?,

    Thanks in advance

    Jay

  • July 15, 2003 at 11:57 am

    #465577

    dbcc inputbuffer(spidhere) or run profiler.

  • July 15, 2003 at 3:15 pm

    #465600

    Thanks Allen,

    Also I could see the SQL being executed by the session from the current activity in the Enterprrise manager by clicking on the session .

    Does SQL server rollback one of the transactions if there is a dead lock ? ( Oracle does ) . Anyway I killed the blocking sessions and it runs fine.

    Thanks

    - Jay

  • July 15, 2003 at 6:46 pm

    #465617

    quote:

    Does SQL server rollback one of the transactions if there is a dead lock ? ( Oracle does )

    SQL Server will identify and end a deadlock by choosing the thread automatically (the deadlock victim) that can break the deadlock. SQL Server rolls back the deadlock victim's transaction, notifies the thread's application (by returning error message number 1205), cancels the thread's current request, and then allows the transactions of the nonbreaking threads to continue.

    Typically, SQL Server chooses the thread running the transaction that is least expensive to undo as the deadlock victim.

Viewing 7 posts - 1 through 6 (of 6 total)

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