locked
sql job has a step getting dead lock RRS feed

  • Question

  • i've got several sql jobs which calls a ssis package.  There are components in that SSIS package dead lock from the perspective applications.

    is there a way to detect the sql job is having a deadlock and would stop the deadlock and rerun the job itself later?

    thanks.


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Wednesday, November 14, 2012 6:58 AM

Answers

  • First of all, you are looking for a work around for the deadlock. However, I would suggest you to look at the CAUSE of the deadlock and chances toa void the same by tuning the queries or indexes. Please share the deadlock graph/information perhaps you captured. We would be able to help you better.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Kalman Toth Tuesday, November 20, 2012 7:36 AM
    Wednesday, November 14, 2012 7:09 AM
  • Following article is on deadlocks and prevention:

    http://www.sqlusa.com/bestpractices/deadlock/

    Here is how you can find out at least one of the jobs causing the deadlock:

    take the jobs (reschedule) out one by one until no deadlock


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Marked as answer by Kalman Toth Tuesday, November 20, 2012 7:37 AM
    • Edited by Kalman Toth Tuesday, November 20, 2012 7:40 AM
    Tuesday, November 20, 2012 7:37 AM

All replies

  • First of all, you are looking for a work around for the deadlock. However, I would suggest you to look at the CAUSE of the deadlock and chances toa void the same by tuning the queries or indexes. Please share the deadlock graph/information perhaps you captured. We would be able to help you better.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Kalman Toth Tuesday, November 20, 2012 7:36 AM
    Wednesday, November 14, 2012 7:09 AM
  • Check this

    http://msdn.microsoft.com/en-us/library/ms178104%28v=sql.105%29.aspx


    Many Thanks & Best Regards, Hua Min

    Wednesday, November 14, 2012 7:09 AM
  • hi,

    these appl, the pkg, db, etc, are  under soa (contact) that no modifications can be made by us. therefore the way to work around it is how the SQL job handles it.

    so, i do need what i asked.

    thanks.


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Wednesday, November 14, 2012 8:23 AM
  • thanks, i know what deadlock is and just haven't have much luck doing what i post.


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Wednesday, November 14, 2012 8:24 AM
  • Identify which query blocks or makes the dead lock and then fine tune the query by adding index or change the isolation level to read uncommitted.

    Next time when you are running the job.Use the below URL to identify the Locking or blocking Query or run the profiler to identify that.

    http://gallery.technet.microsoft.com/Blocking-And-Locking-881e06c7


    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, November 14, 2012 9:44 AM
  • Ok, As long as you will not be able to touch code, you may suggest the product people about the issue. That should be the ideal case. You may be able to retry the operation using try catch with GOTO statement, however, you need to change the code again which is not applicable as you do not own the code.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, November 14, 2012 9:55 AM
  • Following article is on deadlocks and prevention:

    http://www.sqlusa.com/bestpractices/deadlock/

    Here is how you can find out at least one of the jobs causing the deadlock:

    take the jobs (reschedule) out one by one until no deadlock


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Marked as answer by Kalman Toth Tuesday, November 20, 2012 7:37 AM
    • Edited by Kalman Toth Tuesday, November 20, 2012 7:40 AM
    Tuesday, November 20, 2012 7:37 AM