July 23, 2008 at 4:28 am
Hello All,
I am facing with few deadlocks in our servers. I used WMI alerts to capture those queries. It will be great if someone can advise me how to minimize these deadlocks..
process id="process92fe28" taskpriority="0" logused="0" waitresource="OBJECT: 5:644197345:0" waittime="6593" ownerId="210631213" transactionname="SELECT" lasttranstarted="2008-07-21T06:05:13.347" XDES="0x1cafd8d8" lockMode="S" schedulerid="3" kpid="2004" status="suspended" spid="92" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2008-07-21T06:05:13.347" lastbatchcompleted="2008-07-21T06:05:13.347" clientapp=".Net SqlClient Data Provider" hostname="N1APP1QA2" hostpid="2872" loginname="sql" isolationlevel="serializable (4)" xactid="210631213" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"
SELECT [orgMemberRoleView].[OrgRoleMemberId] AS 'OrgRoleMemberId', [orgMemberRoleView].[OrgRoleId] AS 'OrgRoleId', [orgMemberRoleView].[OrgRoleMemberActiveFlag] AS 'OrgRoleMemberActiveFlag', [orgMemberRoleView].[OrgRoleActiveFlag] AS 'OrgRoleActiveFlag', [orgMemberRoleView].[OrgRoleName] AS 'OrgRoleName', [orgMemberRoleView].[OrgRoleDescription] AS 'OrgRoleDescription', [orgMemberRoleView].[Dependent] AS 'Dependent', [orgMemberRoleView].[OrgMemberId] AS 'OrgMemberId', [orgMemberRoleView].[OrgId] AS 'OrgId', [orgMemberRoleView].[MemberId] AS 'MemberId', [orgMemberRoleView].[StartDate] AS 'StartDate', [orgMemberRoleView].[EndDate] AS 'EndDate', [orgMemberRoleView].[OrgMemberStatusId] AS 'OrgMemberStatusId', [orgMemberRoleView].[CalendarId] AS 'CalendarId', [orgMemberRoleView].[OrgMemberTypeId] AS 'OrgMemberTypeId', [orgMemberRoleView].[PrefixTitle] AS 'PrefixTitle', [orgMemberRoleView].[FirstName] AS 'FirstName', [orgMemberRoleView].[MiddleName] AS 'MiddleName', [orgMemberRoleView].[LastName]
and
process id="process30468b68" taskpriority="0" logused="12320" waitresource="OBJECT: 5:651149365:0" waittime="4484" ownerId="210630779" transactionname="user_transaction" lasttranstarted="2008-07-21T06:05:13.003" XDES="0x8a5bb68" lockMode="IX" schedulerid="2" kpid="1748" status="suspended" spid="83" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-07-21T06:05:15.457" lastbatchcompleted="2008-07-21T06:05:15.457" clientapp=".Net SqlClient Data Provider" hostname="N1APP1QA2" hostpid="2872" loginname="sql" isolationlevel="serializable (4)" xactid="210630779" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
INSERT INTO [dbo].[OrgMember] ([CalendarId], [CreatedBy], [CreatedDate], [EndDate], [MemberId], [OrgId], [OrgMemberStatusId], [OrgMemberTypeId], [ReferenceCode], [StartDate], [UpdatedBy], [UpdatedDate]) VALUES (@calendarId, @createdby, @createdDate, @endDate, @memberId, @orgId, @orgMemberStatusId, @orgMemberTypeId, @referenceCode, @startDate, @updatedBy, @updatedDate)
[font="Verdana"]- Deepak[/font]
July 23, 2008 at 8:08 am
run a search in the scripts, there are a bunch that will catch deadlocks
we run a few of them every 2 minutes, they write to a table and send us an alert. to see what happened, we look in the table to see who blocked who
July 23, 2008 at 8:11 am
Yes. I have captured the deadlocks into a table which displays the data as a XML file..but i was wondering how to minimize the query..if you need any other info i can provide you..
[font="Verdana"]- Deepak[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply