-
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
-
What does the job do? What's sql2k sp level?
-
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.
-
Possible to apply sp4? Soimilar error fixed in sp4 per Microsoft.
-
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
-
Forum Rules
|
|