Results 1 to 5 of 5

Thread: SQL2K: Deadlock on lock

  1. #1
    Join Date
    Feb 2007
    Posts
    45

    SQL2K: Deadlock on lock

    Hello,

    I have scheduled a job using Job Agent in SQL2K (Management). Every once in a while i get an error when i view job history:

    Executed as user: xxx. Transaction (Process ID 53) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.

    The process ID changes for every error. Sometimes its 51, 56, or 54 as well.

    I have checked several times, but there is no other process that runs at that time. I even rescheduled it for a different time, but even then it fails about once every week or two.

    How can i check what is the other procedure it is deadlocking with, if it is? OR what exactly is the problem, if it is not with any other process?

    Any help and/or guidance is much appreciated.

    Thanks,

    J

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    What does the job do? What's sql2k sp level?

  3. #3
    Join Date
    Feb 2007
    Posts
    45
    I am running sql 2k sp 3.

    The job runs a stored procedure (sp_final) that first drops a table (tbl_final) then creates it again, and then inserts data from a view (vw_final) to this table. (I have now changed this to "truncate table" then insert data from vw_final to tbl_final).

    Vw_final is a "union" of several other views. The data from vw_final is then inserted into tbl_final in sp_final, which is scheduled to run everyday.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Possible to apply sp4? Soimilar error fixed in sp4 per Microsoft.

  5. #5
    Join Date
    Feb 2007
    Posts
    45
    I can try that over a weekend since its a server.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •