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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy