July 5, 2013 at 7:46 am
Have migrated the SQL server 2005 to 2008R2 and we are now facing lots of deadlocks issues. Have enabled the trace flag 1222 and can see numerous deadlocks loaded in error logs. One of them is below. Can somebody please help what may be the issue?
------------------------------------------------------------------------------
07/05/2013 15:38:08,spid12s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000005B5989950 Mode: Sch-M SPID:80 BatchID:0 ECID:0 TaskProxy:(0x0000000D5FF36540) Value:0xd297600 Cost:(0/0)
07/05/2013 15:38:08,spid12s,Unknown,Victim Resource Owner:
07/05/2013 15:38:08,spid12s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
07/05/2013 15:38:08,spid12s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000800845F0 Mode: Sch-S SPID:139 BatchID:0 ECID:0 TaskProxy:(0x0000000F5CE68518) Value:0xd1c3640 Cost:(0/0)
07/05/2013 15:38:08,spid12s,Unknown,Requested by:
07/05/2013 15:38:08,spid12s,Unknown,Input Buf: RPC Event: Proc [Database Id = 12 Object Id = 1508968502]
07/05/2013 15:38:08,spid12s,Unknown,SPID: 80 ECID: 0 Statement Type: ALTER TABLE Line #: 25
07/05/2013 15:38:08,spid12s,Unknown,Owner:0x000000000CFCDC80 Mode: Sch-M Flg:0x40 Ref:0 Life:20000000 SPID:80 ECID:0 XactLockInfo: 0x00000005B5989990
07/05/2013 15:38:08,spid12s,Unknown,Grant List 0:
07/05/2013 15:38:08,spid12s,Unknown,OBJECT: 2:1250113027:4 CleanCnt:2 Mode:Sch-M Flags: 0x1
07/05/2013 15:38:08,spid12s,Unknown,Node:2
07/05/2013 15:38:08,spid12s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
07/05/2013 15:38:08,spid12s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000005B5989950 Mode: Sch-M SPID:80 BatchID:0 ECID:0 TaskProxy:(0x0000000D5FF36540) Value:0xd297600 Cost:(0/0)
07/05/2013 15:38:08,spid12s,Unknown,Requested by:
07/05/2013 15:38:08,spid12s,Unknown,Input Buf: RPC Event: Proc [Database Id = 12 Object Id = 1508968502]
07/05/2013 15:38:08,spid12s,Unknown,SPID: 139 ECID: 0 Statement Type: UPDATE Line #: 371
07/05/2013 15:38:08,spid12s,Unknown,Owner:0x000000000C5DC5C0 Mode: Sch-S Flg:0x40 Ref:1 Life:00000000 SPID:139 ECID:0 XactLockInfo: 0x0000000E570E1BA0
07/05/2013 15:38:08,spid12s,Unknown,Grant List 0:
07/05/2013 15:38:08,spid12s,Unknown,OBJECT: 2:1250113027:6 CleanCnt:2 Mode:Sch-S Flags: 0x1
07/05/2013 15:38:08,spid12s,Unknown,Node:1
07/05/2013 15:38:08,spid12s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
07/05/2013 15:38:08,spid12s,Unknown,Wait-for graph
07/05/2013 15:38:08,spid12s,Unknown,Deadlock encountered .... Printing deadlock information
07/05/2013 15:37:51,spid12s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000D38AE59D0 Mode: Sch-S SPID:179 BatchID:0 ECID:0 TaskProxy:(0x0000000EB2060518) Value:0xcdfc380 Cost:(0/0)
07/05/2013 15:37:51,spid12s,Unknown,Victim Resource Owner:
07/05/2013 15:37:51,spid12s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
07/05/2013 15:37:51,spid12s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000B5490E80 Mode: Sch-M SPID:96 BatchID:0 ECID:0 TaskProxy:(0x0000000CE0EAE540) Value:0xdc44280 Cost:(0/0)
07/05/2013 15:37:51,spid12s,Unknown,Requested by:
07/05/2013 15:37:51,spid12s,Unknown,Input Buf: RPC Event: Proc [Database Id = 12 Object Id = 1508968502]
07/05/2013 15:37:51,spid12s,Unknown,SPID: 179 ECID: 0 Statement Type: UPDATE Line #: 116
07/05/2013 15:37:51,spid12s,Unknown,Owner:0x000000000C6B0C40 Mode: Sch-S Flg:0x40 Ref:1 Life:00000000 SPID:179 ECID:0 XactLockInfo: 0x000000011F019BA0
07/05/2013 15:37:51,spid12s,Unknown,Grant List 0:
07/05/2013 15:37:51,spid12s,Unknown,OBJECT: 2:1401065560:10 CleanCnt:2 Mode:Sch-S Flags: 0x1
07/05/2013 15:37:51,spid12s,Unknown,Node:2
07/05/2013 15:37:51,spid12s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
07/05/2013 15:37:51,spid12s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000D38AE59D0 Mode: Sch-S SPID:179 BatchID:0 ECID:0 TaskProxy:(0x0000000EB2060518) Value:0xcdfc380 Cost:(0/0)
07/05/2013 15:37:51,spid12s,Unknown,Requested by:
07/05/2013 15:37:51,spid12s,Unknown,Input Buf: RPC Event: Proc [Database Id = 12 Object Id = 1508968502]
07/05/2013 15:37:51,spid12s,Unknown,SPID: 96 ECID: 0 Statement Type: ALTER TABLE Line #: 24
07/05/2013 15:37:51,spid12s,Unknown,Owner:0x000000000EAE1D00 Mode: Sch-M Flg:0x40 Ref:0 Life:20000000 SPID:96 ECID:0 XactLockInfo: 0x00000000B5490EC0
07/05/2013 15:37:51,spid12s,Unknown,Grant List 1:
07/05/2013 15:37:51,spid12s,Unknown,OBJECT: 2:1401065560:6 CleanCnt:2 Mode:Sch-M Flags: 0x1
07/05/2013 15:37:51,spid12s,Unknown,Node:1
07/05/2013 15:37:51,spid12s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
07/05/2013 15:37:51,spid12s,Unknown,Wait-for graph
07/05/2013 15:37:51,spid12s,Unknown,Deadlock encountered .... Printing deadlock information
July 5, 2013 at 8:11 am
Have you re-built your database under the new SQL Server?
July 5, 2013 at 8:29 am
No. have not rebuilt it.
July 5, 2013 at 8:38 am
You may want to update statistics.
See 'next steps' section.
July 5, 2013 at 9:13 am
FYI
The only reason I recommend to rebuild the database is that each revision and release of SQL Server sometimes uses a revised method of creating physical database objects depending on what was updated. The methods of accessing these objects are also sometimes revised. Using some of the backward compatibility methods to access older database structures can cause numerous issues. It is always recommended to rebuild the databases under the upgraded server.
Depending on the size and complexity of the database, this may be a simple way to eliminate potential problems and possibly improve performance of your database.
July 5, 2013 at 9:16 am
Thank you, I will try out that option and let you know about my findings.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply