Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Posts
    56

    Unanswered: Blocking issue with timeouts

    Hi,
    We have an application job (power builder) that runs on the app server every 5 minutes to load data into a table. Occassionally the job times out without completing but this can lead to an inactive transaction that will cause blocking. I have been manually issuing the kill command to stop the blocking.

    Any ideas on how to clear up the issue from a sql server side? Any code we can put in the powerbuilder app to clear the inactive transaction?



    Thanks

    Rebecca

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you loading directly into a production table?
    A better practice is to load your data into a staging table first, and then execute a sproc to process the staging data. This separates the data transfer from the data transform, allowing better auditing and debugging.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2008
    Posts
    56
    Thanks BM
    We do load to a staging table but the staging table is in the same db/schema. Would you advise perhaps a separate db for staging?

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Rebecca Johnston View Post
    Thanks BM
    We do load to a staging table but the staging table is in the same db/schema. Would you advise perhaps a separate db for staging?
    No!!!! Change the timeout to 0 which means there is no time out on the job. Just watch to make sure it doesn't run forever...might be a problem down stream so check the sprocs which transform the data and load into destination tables.

Posting Permissions

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