transaction without a request
in [Server Configuration]
|
From: Dan Holmes on 18 Mar 2010 12:54 On 3/18/2010 12:45 PM, Kalen Delaney wrote: > You still haven't told me what version you are using, or what query you > are running to get the numbers you are showing. Although the columns > names are a big clue, it's more polite to not make the people helping > you try to guess. It still looks like the big number in > resource_associated_entity_id is the partition_id. I think you didn't > show all the rows. There should be some for OBJECT locks, and then the > resource_associated_entity_id would be the object_id. > i didn't show all the rows there are 1770 of them. > There is no way to hijack an open transaction, but you can kill it, and > that will rollback the transaction and release the locks. > I am sorry. This is happening live and i am trying to gather how to fix this as well as manage the problem., this produced the output Select * FROM sys.dm_tran_locks WHERE request_Session_ID = 187 your guess was right about the value Select object_name(p.object_id), * FROM sys.dm_tran_locks tl LEFT JOIN sys.partitions p ON tl.resource_associated_entity_id = p.Partition_id WHERE request_Session_ID = 187 and resource_database_id = 42 showed me what tables were affected. i just tested my second theory BEGIN TRAN; UPDATE tblAgency SET RMqsserverportnumber = 12345 select * from sys.dm_tran_session_transactions --stop here --now i modified the spAddressSelect proc below --and stuck in an extra commit. Declare @ContextStr varchar(255) set @ContextStr = dbo.syscontextstr() exec [spAddressSelect] @ContextStr , 3, 1 select * from sys.dm_tran_session_transactions --after running the transaction was gone and committed. SELECT * FROM tblAgency
From: Uri Dimant on 21 Mar 2010 05:33
Dan Adam Machanic has written a great stored procedure to track down such problems, please visit on www.sqlblog.com and look for who_is_active_v8_82.sql "Dan Holmes" <dan.holmes(a)routematch.com> wrote in message news:uumZ3urxKHA.1548(a)TK2MSFTNGP02.phx.gbl... > On 3/18/2010 12:45 PM, Kalen Delaney wrote: >> You still haven't told me what version you are using, or what query you >> are running to get the numbers you are showing. Although the columns >> names are a big clue, it's more polite to not make the people helping >> you try to guess. It still looks like the big number in >> resource_associated_entity_id is the partition_id. I think you didn't >> show all the rows. There should be some for OBJECT locks, and then the >> resource_associated_entity_id would be the object_id. >> > i didn't show all the rows there are 1770 of them. > >> There is no way to hijack an open transaction, but you can kill it, and >> that will rollback the transaction and release the locks. >> > > I am sorry. This is happening live and i am trying to gather how to fix > this as well as manage the problem., > > this produced the output > Select * > FROM sys.dm_tran_locks > WHERE request_Session_ID = 187 > > > your guess was right about the value > > Select object_name(p.object_id), * > FROM sys.dm_tran_locks tl > LEFT JOIN sys.partitions p ON tl.resource_associated_entity_id = > p.Partition_id > WHERE request_Session_ID = 187 > and resource_database_id = 42 > > > showed me what tables were affected. > > i just tested my second theory > > BEGIN TRAN; > UPDATE tblAgency SET RMqsserverportnumber = 12345 > select * from sys.dm_tran_session_transactions > > --stop here > --now i modified the spAddressSelect proc below > --and stuck in an extra commit. > Declare @ContextStr varchar(255) > set @ContextStr = dbo.syscontextstr() > exec [spAddressSelect] > @ContextStr , > 3, > 1 > select * from sys.dm_tran_session_transactions > --after running the transaction was gone and committed. > > SELECT * FROM tblAgency |