SQL deadlocks

[ccdesktop]

Hi,



We have noticed that we are getting a large number of sql deadlocks and timeouts on our LanSweeper database. It is on a Windows 2003 Cluster running SQL 2005.



We are witnessing around 30 an hour. Below is the information we have gathered. Has anyone else experienced similar problems.



Thanks in advance



Steve



23/11/2009 10:10:28: A11429 SERVICES Transaction (Process ID 193) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at LansweeperService.MdlWmi.GetWMI(String Querystr, String Table, LS_Computer& mycomputer, String section, SqlConnection& sqlcon)



23/11/2009 10:11:30: A14977 SERVICES Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at LansweeperService.MdlWmi.GetWMI(String Querystr, String Table, LS_Computer& mycomputer, String section, SqlConnection& sqlcon)





Deadlock encountered .... Printing deadlock information
Wait-for graph



Node:1
PAGE: 34:1:851780 CleanCnt:3 Mode:IX Flags: 0x2
Grant List 2:
Owner:0x59E2C240 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:269 ECID:0 XactLockInfo: 0x0D624B74
SPID: 269 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: (@P1 money,@P2 datetime,@P3 datetime,@P4 datetime,@P5 datetime,@P6 varchar(10))UPDATE Call
SET CallStatus = @P1, CurrentEvent = 21, ActDatTim = NULL
, SLT1FixDateTime = @P2, SLT1WarningFixDateTime = @P3
,SLT1RespondDateTime = @P4, SLT1WarningRespondD
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x52FF86D8 Mode: S SPID:255 BatchID:0 ECID:0 TaskProxy:(0x1D474378) Value:0x35091aa0 Cost:(0/0)



Node:2
PAGE: 34:1:851782 CleanCnt:2 Mode:S Flags: 0x2
Grant List 1:
Owner:0x12EA0460 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:255 ECID:0 XactLockInfo: 0x52FF86FC
SPID: 255 ECID: 0 Statement Type: SELECT Line #: 74
Input Buf: RPC Event: Proc [Database Id = 34 Object Id = 859254216]
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x0D624B50 Mode: IX SPID:269 BatchID:0 ECID:0 TaskProxy:(0x21B62378) Value:0x56c12d20 Cost:(0/764)



Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x52FF86D8 Mode: S SPID:255 BatchID:0 ECID:0 TaskProxy:(0x1D474378) Value:0x35091aa0 Cost:(0/0)

[Hemoco]

If possible mail us your complete errorlog.txt file.

I believe the sql connection pooling is set to 100 by default in windows.

[Hemoco]

In the errorlog do you get this error always for "SERVICES"

[ccdesktop]

We are running 75 concurrent threads, what would you recommend that we lower this too?

[Hemoco]

How many concurrent threads are you running?
have you tried lowering this.

[ccdesktop]

Just to add more information.

We are seeing approx 133 separate sql process connected to the database.

Database locks run mostly around the 133 mark but spikes to 700/800 are seen regularly and the highest seen so far is 12741.

Any ideas Lansweeper?

[ccdesktop]

nope, just lansweeper

[Hemoco]

Is there any other software accessing the lansweeper database?

[ccdesktop]

No, this seems to have been happening for a while, it's just that we have some new monitoring software that brought it to our attention. For example we have noticed 1828 deadlocks over the last three weeks.

[Hemoco]

Did this problem happened recently (after an upgrade or change?)