I have an issue that I see alot where applications appear to run alot longer than they should. We are running SQL Server 2000 and the applations are built in Power Builder.
What I see is transactions running for longer than I hour witht the last command issued as "Awaiting Command". There don't appear to be any blocks or deadlocks. Could this be an issue with the application not closing the connection?
Hi, I was looking at the output from a 3rd party tool -SQL Diagnostics Manager. There it is being referred to as a transaction. Looking at it from sp_who2 it's status is sleeping so it probally isn't processing any data.
What version of PowerBuilder are you using? I have to use version 6.5 (for reasons) and I can't make it run smooth with SQL server 2005. When I open the "Select Tables" window, all I get are the system tables.
Selecting or deselecting "Show system tables" makes no difference.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
"Sleeping" can (but does not necessarily) mean that the process is waiting for data to be read from disk. "Running" means at least one CPU is actually working on the task. Is the SPID's DiskIO number going up fairly regularly?
We are using Power Builder 11.
Do you know if there is a feature in PB11 to automatically logout a user after a certain time of inactivity?
Yes, this is easy to do and it will horrendously complicate your problem.
This problem is normally caused when SQLCA.AutoCommit is true and you are relying on the combined wisdom and kindness of PowerBuilder to manage your SQL transactions for you. I've seen many applications and development tools, but I can't remember one of either being particularly wise or kind.
If you want to fix the problem, you need to start to manage SQL transactions explicitly within your PowerBuilder code. If you want your application to start experiencing random data loss, application hangs, and miscellaneous odd behaviors then you probably need to add one line of code to the application object's timeout event.
Thanks for you help. This application is legacy code. What I have been doing with the app team moving forward is to force them to use stored procs that contain the transaction processing rather than let them use embedded sql. In the stored procs I begin and end (commit/rollback) the trasactions.
Just an observation... With new procedures, divide them into two groups: called by application versus called only by other sprocs. Make ROLLBACK TRANSACTION the first statement in procedures called by the application... That's the fastest way I've found to break PowerBuilder developers of bad habits.
Just for the giggle factor, the line that you can add to the timeout event that will cause most most PowerBuilder applications to terminate is something like: