Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deadlocks after migration from SQL server 2005 to 2008R2 Expand / Collapse
Author
Message
Posted Friday, July 05, 2013 7:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 3:46 AM
Points: 30, Visits: 132
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 TaskProxy0x0000000D5FF36540) Value:0xd297600 Cost0/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 TaskProxy0x0000000F5CE68518) Value:0xd1c3640 Cost0/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 TaskProxy0x0000000D5FF36540) Value:0xd297600 Cost0/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 TaskProxy0x0000000EB2060518) Value:0xcdfc380 Cost0/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 TaskProxy0x0000000CE0EAE540) Value:0xdc44280 Cost0/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 TaskProxy0x0000000EB2060518) Value:0xcdfc380 Cost0/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
Post #1470739
Posted Friday, July 05, 2013 8:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 30, 2013 5:15 AM
Points: 35, Visits: 77
Have you re-built your database under the new SQL Server?
Post #1470749
Posted Friday, July 05, 2013 8:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 3:46 AM
Points: 30, Visits: 132
No. have not rebuilt it.
Post #1470763
Posted Friday, July 05, 2013 8:38 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:01 PM
Points: 643, Visits: 3,695

You may want to update statistics.

See 'next steps' section.

http://msdn.microsoft.com/en-us/library/ms144267.aspx
Post #1470769
Posted Friday, July 05, 2013 9:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 30, 2013 5:15 AM
Points: 35, Visits: 77
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.
Post #1470787
Posted Friday, July 05, 2013 9:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 3:46 AM
Points: 30, Visits: 132
Thank you, I will try out that option and let you know about my findings.
Post #1470790
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse