DBCC TRACEON did not capture info on deadlocking
in [Using MS SQL Server]
|
Prev: 100% without investment online part time jobs..(adsense,datawork,neobux..more jobs)
Next: SQL Server 2008 R2 Native Client backward-compatible to SQL 2005/2008??
From: xo on 5 Aug 2010 12:31 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.
From: Erland Sommarskog on 5 Aug 2010 14:45 xo (xo5555ox(a)gmail.com) writes: > 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 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(a)sommarskog.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/prodinfo/previousversions/books.mspx
From: xo on 5 Aug 2010 16:37 On Aug 5, 11:45 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > xo (xo555...(a)gmail.com) writes: > > 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 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, esq...(a)sommarskog.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/prodinfo/previousversions/books.mspx 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
From: Erland Sommarskog on 5 Aug 2010 17:35
xo (xo5555ox(a)gmail.com) writes: > 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. 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(a)sommarskog.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/prodinfo/previousversions/books.mspx |