Hello,

I tried to troubleshoot a deadlocking issue with the ETL jobs. And I
enabled tracing through DBCC TRACEON (1222, 1204, -1). I can see that
it is on when I do DBCC TRACESTATUS.

But it captured NO deadlock information. Why it did not trap any info?
And is there any other alternatives I have in capturing the deadlock
incidents?

I am currently running SQL server 2005 SP2.

Thanks in advance.

xo (xo5555ox (AT) gmail (DOT) com) writes:
I know I tested that the other day, but it was on SQL 2005 SP3 or SQL 2008
SP1.

But maybe you added one too many. 1222 is suffcient. 1204 is the old
deadlock trace which is more difficult to understand.

You can also enable the deadlock trace by adding ;-T1222 to the startup
paramerters for you instance in SQL Server Configuration Manager. This
forces a server restart obviously.

And since some people misunderstand what a deadlock is: you did really
have a deadlock with a process getting an error about being a deadlock
victim?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Aug 5, 11:45*am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thanks Erland!

I suspect the processes encountered blocking instead of deadlocking
since SQL server error log did not capture any info. But the
application folks keep insisting there was deadlocking and provided
the following log info from the application side.


Error message from the SQL server scheduled job -

Executed as user: Server\User. ...ress Progress: 2010-07-15
00:10:02.81 Source: LOAD_FLODS_ACTIVITY_D00_INC_CF00
Validating: 80% complete End Progress Progress: 2010-07-15
00:10:03.34 Source: GET_LOADTYPE_CF00 Executing query "SELECT
LOAD_TYPE FROM FLODS_UTL_LOADTYPE_LOOKUP W".: 100% complete End
Progress Progress: 2010-07-15 00:10:03.34 Source:
GET_NEWRUN_DATE_INC_CF00 Executing query "SELECT
FLODS_NEW_ETL_RUNDATE NEW_MODIFIED_DT FROM ".: 100% complete End
Progress Progress: 2010-07-15 00:10:03.35 Source:
GET_LAST_DATE_INC_CF00 Executing query "SELECT
FLODS_LAST_ETL_RUNDATE LAST_MODIFIED_DT FRO".: 100% complete End
Progress Progress: 2010-07-15 00:10:03.35 Source:
TRUNCATE_STAGING_TABLE_INC_CF00 Executing query "TRUNCATE TABLE
FLODS_AUDIT_OBJECT_SD00".: 100% complete End Progress Progress:
2010-07-15 00:10:03.35 Source:
LOAD_FLODS_AUDIT_OBJECT_D00_INC_CF00 Validating: 0% complete End
Pr... The package execution fa... The step failed.


Error message from application log -

2010-07-15 00:14:46.000 OnError
LOAD_FLODS_ENQUIRY_INT_RELATION_D00_INC_CF00 SSIS Error Code
DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:
0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB
Provider for SQL Server" Hresult: 0x80004005 Description:
"Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.".

2010-07-15 00:14:46.000 OnError
LOAD_FLODS_ENQUIRY_INT_RELATION_D00_INC_CF00 component
"Source_Provider_DF00" (1491) failed the pre-execute phase and
returned error code 0xC0202009.


Is there any other methods to diagnose the problems? Thanks

xo (xo5555ox (AT) gmail (DOT) com) writes:
The error message sure looks like a deadlock to me.

I don't know why it was captured. Maybe they are running against several
instances, the deadlock was another instance?

You can easily verify that your configuration is working. Create this
table:

CREATE TABLE test(a int NOT NULL PRIMARY KEY)

Then run in two windows:

BEGIN TRANSACTION
SELECT MAX(a) FROM test WITH (HOLDLOCK)
WAITFOR DELAY '00:00:10'
INSERT test(a) VALUES(19)
ROLLBACK TRANSACTION

Yet an alternative to capture deadlock information is through traces
or event notifications.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx