Transaction Promotion/Hang?

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.

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" wrote in message

>
> 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.
>
>