Transaction_Status2
in [Server Configuration]
|
From: michaelg via SQLMonster.com on 3 Nov 2009 08:54 Does anyone know what the transaction_status2 field in the SYS. DM_TRAN_ACTIVE_TRANSACTIONS DMV is used for? I am tracking down an issue with a sleeping connection holding page and object locks. I've been able to track down the rest of the fields, but this one has a value of 323 which looks nothing like any other rows in the view. According to the MS website, it is "Identified for informational purposes only. Not supported. Future compatibility is not guaranteed." which is not helpful at all. Thanks! -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200911/1
From: Russell Fields on 3 Nov 2009 09:43 Because it is not defined, I would suggest ignoring it. Especially with the warnings that are offered. However, I do note that certain transaction names tend to use the same transaction_status2. I tracked for a while and got this. transaction_status2 name 451 DTCXact 259 user_transaction 67 INSERT EXEC 3 CREATE STATISTICS 3 CREATE TABLE 3 DELETE 3 DROPOBJ 3 INSERT 3 INSERT EXEC 3 SELECT INTO 3 TRUNCATE TABLE 3 UPDATE 2 AutoCreateQPStats 2 Cache Coherency 2 DBM_INIT 2 droptemp 2 FCheckAndCleanupCachedTempTable 2 sort_init 2 TVQuery So, if your step has a particular transaction name, that might be a clue. For now. But the transaction name is probably a bigger clue. (And I have no idea what 323 is.) RLF "michaelg via SQLMonster.com" <u13012(a)uwe> wrote in message news:9e90769d6af57(a)uwe... > Does anyone know what the transaction_status2 field in the SYS. > DM_TRAN_ACTIVE_TRANSACTIONS DMV is used for? I am tracking down an issue > with > a sleeping connection holding page and object locks. I've been able to > track > down the rest of the fields, but this one has a value of 323 which looks > nothing like any other rows in the view. > > According to the MS website, it is "Identified for informational purposes > only. Not supported. Future compatibility is not guaranteed." which is not > helpful at all. > > Thanks! > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200911/1 >
From: michaelg via SQLMonster.com on 3 Nov 2009 10:07 Thanks for the quick feedback! It's looking more and more like the app somehow stranded the database connection/process. When I try to map the SPID to an OS ID, it doesn't show up. Also, there is no record for the SPID in sys.dm_exec_requests even though it is holding a lock on a table. I even checked to see if it was in a cursor loop, it would show up in the sys.dm_exec_cursors.. no luck their either. Thanks again! Russell Fields wrote: >Because it is not defined, I would suggest ignoring it. Especially with the >warnings that are offered. > >However, I do note that certain transaction names tend to use the same >transaction_status2. I tracked for a while and got this. > >transaction_status2 name >451 DTCXact >259 user_transaction >67 INSERT EXEC >3 CREATE STATISTICS >3 CREATE TABLE >3 DELETE >3 DROPOBJ >3 INSERT >3 INSERT EXEC >3 SELECT INTO >3 TRUNCATE TABLE >3 UPDATE >2 AutoCreateQPStats >2 Cache Coherency >2 DBM_INIT >2 droptemp >2 FCheckAndCleanupCachedTempTable >2 sort_init >2 TVQuery > >So, if your step has a particular transaction name, that might be a clue. >For now. But the transaction name is probably a bigger clue. (And I have no >idea what 323 is.) > >RLF > >> Does anyone know what the transaction_status2 field in the SYS. >> DM_TRAN_ACTIVE_TRANSACTIONS DMV is used for? I am tracking down an issue >[quoted text clipped - 9 lines] >> >> Thanks! -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200911/1
From: Russell Fields on 4 Nov 2009 08:34 You might check this document on orphaned connections. http://support.microsoft.com/kb/137983 RLF "michaelg via SQLMonster.com" <u13012(a)uwe> wrote in message news:9e9119e2b288c(a)uwe... > Thanks for the quick feedback! > > It's looking more and more like the app somehow stranded the database > connection/process. When I try to map the SPID to an OS ID, it doesn't > show > up. Also, there is no record for the SPID in sys.dm_exec_requests even > though > it is holding a lock on a table. I even checked to see if it was in a > cursor > loop, it would show up in the sys.dm_exec_cursors.. no luck their either. > > Thanks again! > > > Russell Fields wrote: >>Because it is not defined, I would suggest ignoring it. Especially with >>the >>warnings that are offered. >> >>However, I do note that certain transaction names tend to use the same >>transaction_status2. I tracked for a while and got this. >> >>transaction_status2 name >>451 DTCXact >>259 user_transaction >>67 INSERT EXEC >>3 CREATE STATISTICS >>3 CREATE TABLE >>3 DELETE >>3 DROPOBJ >>3 INSERT >>3 INSERT EXEC >>3 SELECT INTO >>3 TRUNCATE TABLE >>3 UPDATE >>2 AutoCreateQPStats >>2 Cache Coherency >>2 DBM_INIT >>2 droptemp >>2 FCheckAndCleanupCachedTempTable >>2 sort_init >>2 TVQuery >> >>So, if your step has a particular transaction name, that might be a clue. >>For now. But the transaction name is probably a bigger clue. (And I have >>no >>idea what 323 is.) >> >>RLF >> >>> Does anyone know what the transaction_status2 field in the SYS. >>> DM_TRAN_ACTIVE_TRANSACTIONS DMV is used for? I am tracking down an issue >>[quoted text clipped - 9 lines] >>> >>> Thanks! > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200911/1 >
|
Pages: 1 Prev: Start cube processing using TSQL - RobRom Next: SOS_Scheduler_Yield |