January 3, 2012 at 2:41 pm
I think I already know my answer, but thought I would throw it out there in case I'm just not finding it.
I just started a new job, and quickly realized they have a significant issue with blocking caused by SPIDs that are sleeping/ AWAITING COMMAND. 4-5 times a day we have to call users to have them log out of the application and log back in to free up the session's blocking.
It appears to me to be a case of a TRAN not being committed properly either through error, or nested stored procedures etc.
My question is, if that is the case, how can I find all of the Stmts that are contained in the open transactions to find the offending code? This is a high transaction DB 9,000 tran/sec at times, so it is difficult to keep a trace running that would capture enough data to be relevant for 1 spid that runs off, but from what I've researched that is the only way to get what I am looking for.
Please tell me I'm just missing the dmvs I'm looking for, or if not, any advice on the best way to setup the trace.
Thanks!!!
January 3, 2012 at 2:48 pm
sys.dm_tran_active_transactions joined to dm_tran_session_transactions joined to sys.dm_exec_sessions for the details of who and from where. May need exec_connections with the last sql handle column (not exact, it's something like that)
sys.dm_tran_locks (complex) for the locks held
You won't be able to get all the statements that the connections ran without having a trace, all the DMVs will show you is the current/previous. The DMVs may give you an indication where to start, especially if you are checking frequently
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2012 at 3:06 pm
Thanks for the reply Gail. That is what I was afraid of, I've been using Paul Randal's long running transaction script and Mechanic's sp_whoisactive to get the last stmt, but hadn't found a way to see earlier statements (that most likely caused the issue). At least I know I'm not missing something. I was hoping for some lower lying fruit 🙂
I'll definitely be doing some reading on the specific dmvs you listed to add to my understanding, and hopefully logging some of the last stmts when they hang up will give me enough info to help Dev narrow it down.
January 3, 2012 at 3:27 pm
Have them look for connection objects that open transactions auto-magically for them as well. I ran into this a while back where my devs swore up and down they never actually opened a transaction. It proved out that the connection object they were using once you dug through the inheritance was called something like 'ODBC_TransactionalCall' or something equally obvious if you thought about it and saw it.
You might also trace a single user for a bit and see if anything obvious pops up.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 5, 2012 at 9:39 am
Thanks Craig, that is also good to know.
January 5, 2012 at 12:07 pm
We recently found that some of our code was not closing transactions when errors were encountered. The only way that we found that was because we have a trace running constantly on the busy OLTP system. We load trace data when we need to do analysis. We found that the session was executing "set implicit_transactions on" at several points and then erroring out for whatever reason. The session was then sleeping, but the transaction was still open and caused blocking.
January 5, 2012 at 1:04 pm
Chuck Hottle (1/5/2012)
We recently found that some of our code was not closing transactions when errors were encountered. The only way that we found that was because we have a trace running constantly on the busy OLTP system. We load trace data when we need to do analysis. We found that the session was executing "set implicit_transactions on" at several points and then erroring out for whatever reason. The session was then sleeping, but the transaction was still open and caused blocking.
Dear gods. Talk about a recipe for disaster.
Quotes of interest on this: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/a300ac43-e4c0-4329-8b79-a1a05e63370a.htm in your BOL for SQL 2K8.
When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:
ALTER TABLE
FETCH
REVOKE
CREATE
GRANT
SELECT
DELETE
INSERT
TRUNCATE TABLE
DROP
OPEN
UPDATE
Not so bad by itself, until you hit this little detail:
Transactions that are automatically opened as the result of this setting being ON must be explicitly committed or rolled back by the user at the end of the transaction. Otherwise, the transaction and all of the data changes it contains are rolled back when the user disconnects. After a transaction is committed, executing one of the statements above starts a new transaction.
Implicit transaction mode remains in effect until the connection executes a SET IMPLICIT_TRANSACTIONS OFF statement, which returns the connection to autocommit mode. In autocommit mode, all individual statements are committed if they complete successfully.
Dear lord. I'd never even seen this option. It needs to be shot in the back of the head. Thanks for that call-out.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 5, 2012 at 1:22 pm
Chuck Hottle (1/5/2012)
We found that the session was executing "set implicit_transactions on" at several points and then erroring out for whatever reason. The session was then sleeping, but the transaction was still open and caused blocking.
Yes that is VERY intersting, I'm going to trace against our Dev environment for a few days to see if I see that pop up anywhere. Will definitely report if I do 🙂 Thx for info.
January 10, 2012 at 1:16 pm
So, you da man Chuck Hottle. We are apparently setting IMPLICIT_TRANSACTION ON all throughout our code. I brought it up to a dev and they said it is probably part of the powerbuilder they used to write the code. I think it is actually coming from the app side as part of the connection and isn't actually a part of any stored procedures, so it makes it very difficult to change. 🙁
Again, once I leave the SQL realm and enter in the client\program realm, I start to get lost so, looks like I'm pretty much stuck at this point.
I have seen some article online suggesting setting XACT_ABORT on anyone have any experience/suggestions about using this stored procedures to alleviate the connection opening in IMPLICIT_TRANSACTION mode and then receiving errors from the client and just hanging a transaction out there until the client app is closed?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply