Transaction Promotion/Hang?
in [Server Configuration]
|
Prev: Sql Server 2008 installation problem
Next: SQL Server 2000: Tempdb growth is causing application failure.
From: SnapDive on 29 Mar 2010 16:52 SQL Server 2008, a SSIS package runs fine. I attempted to make it transactional, and run that within a larger SSIS transaction. The execution now hangs somewhere. I suspect it is when the internal transaction gets promoted to run within the containing transaction, but I really have no idea what is happening. Can anyone throw me any clues or what-to-trace info so I can understand this better? Thanks.
From: Uri Dimant on 30 Mar 2010 09:21
Hi See blocking info -- Basic blocking information ---select * from Sys.dm_db_index_operational_stats(DB_ID(),NULL, NULL, NULL) SELECT owt.session_id AS waiting_session_id, owt.blocking_session_id, DB_NAME(tls.resource_database_id) as database_name, owt.wait_duration_ms, owt.waiting_task_address, tls.request_mode, tls.request_type, tls.resource_associated_entity_id, tls.resource_description AS local_resource_description, owt.wait_type, owt.resource_description AS blocking_resource_description FROM sys.dm_os_waiting_tasks AS owt INNER JOIN sys.dm_tran_locks AS tls ON owt.resource_address = tls.lock_owner_address WHERE owt.wait_duration_ms > 5000 AND owt.session_id > 50 -- Detailed blocking information with query information SELECT owt.session_id AS waiting_session_id, owt.blocking_session_id, DB_NAME(tls.resource_database_id) AS database_name, (SELECT SUBSTRING(est.[text], ers.statement_start_offset/2 + 1, (CASE WHEN ers.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2 ELSE ers.statement_end_offset END - ers.statement_start_offset ) / 2) FROM sys.dm_exec_sql_text(ers.[sql_handle]) AS est) AS waiting_query_text, CASE WHEN owt.blocking_session_id > 0 THEN ( SELECT est.[text] FROM sys.sysprocesses AS sp CROSS APPLY sys.dm_exec_sql_text(sp.[sql_handle]) as est WHERE sp.spid = owt.blocking_session_id) ELSE NULL END AS blocking_query_text, (CASE tls.resource_type WHEN 'OBJECT' THEN OBJECT_NAME(tls.resource_associated_entity_id, tls.resource_database_id) WHEN 'DATABASE' THEN DB_NAME(tls.resource_database_id) ELSE (SELECT OBJECT_NAME(pat.[object_id], tls.resource_database_id) FROM sys.partitions pat WHERE pat.hobt_id = tls.resource_associated_entity_id) END ) AS object_name, owt.wait_duration_ms, owt.waiting_task_address, owt.wait_type, tls.resource_associated_entity_id, tls.resource_description AS local_resource_description, tls.resource_type, tls.request_mode, tls.request_type, tls.request_session_id, owt.resource_description AS blocking_resource_description, qp.query_plan AS waiting_query_plan FROM sys.dm_tran_locks AS tls INNER JOIN sys.dm_os_waiting_tasks owt ON tls.lock_owner_address = owt.resource_address INNER JOIN sys.dm_exec_requests ers ON tls.request_request_id = ers.request_id AND owt.session_id = ers.session_id OUTER APPLY sys.dm_exec_query_plan(ers.[plan_handle]) AS qp GO "SnapDive" <SnapDive(a)community.nospam> wrote in message news:lk42r5969ffbmms97dk6i3dnncf6j7meao(a)4ax.com... > > SQL Server 2008, a SSIS package runs fine. I attempted to make it > transactional, and run that within a larger SSIS transaction. The > execution now hangs somewhere. I suspect it is when the internal > transaction gets promoted to run within the containing transaction, > but I really have no idea what is happening. > > Can anyone throw me any clues or what-to-trace info so I can > understand this better? > > > Thanks. > > |