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?
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.
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.