Integrity job fails error -1073548784

Question

Post reply

Integrity job fails error -1073548784

Krasavita

SSC-Insane

Points: 23138
More actions
September 12, 2011 at 6:44 am

#390383

Hello, my integrity job failed:
Executing the query "ALTER INDEX [IX_UserProfileValue_RecordID] ON [dbo].[UserProfileValue] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
" failed with the following error: "Transaction (Process ID 86) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
How can I fix it?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Plz post the deadlock graph.

Traceflag 1222 if you don't have anything in the sql logs.

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

From sql logs:

Sourcespid89

Message

A read operation on a large object failed while sending data to the client. A common cause for this is if the application is running in READ UNCOMMITTED isolation level. This connection will be terminated.

Message

Deadlock encountered .... Printing deadlock information

Message

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:

Message

OBJECT: 13:13243102:2 CleanCnt:3 Mode:Sch-M Flags: 0x0

message

Grant List 3:

Message

Owner:0x000000011A06C700 Mode: Sch-M Flg:0x0 Ref:0 Life:20000000 SPID:86 ECID:0 XactLockInfo: 0x000000023D23AA88

Message

SPID: 86 ECID: 0 Statement Type: ALTER INDEX Line #: 1

Message

Input Buf: Language Event: ALTER INDEX [IX_UserProfileValue_RecordID] ON [dbo].[UserProfileValue] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )

Message

ResType:LockOwner Stype:'OR'Xdes:0x0000000080B68F18 Mode: Sch-S SPID:147 BatchID:0 ECID:0 TaskProxy:(0x00000004A72F2570) Value:0xe13ccb40 Cost:(0/0)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That is not a deadlock graph. Please enable the traceflag and post the deadlock graph if the problem occurs again.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

GilaMonster (9/12/2011)
That is not a deadlock graph. Please enable the traceflag and post the deadlock graph if the problem occurs again.

Exactly. It should look something like this :

<deadlock-list>

<process-list>

SELECT DISTINCT IMSQD.client_id

FROM dbo.IMS_Quote_Details IMSQD

INNER JOIN dbo.coverages CVGS WITH(NOLOCK) ON CVGS.coverage_id = IMSQD.coverage_id WHERE

client_id IN

(SELECT DISTINCT PCLNT.client_id FROM dbo.policy_clients PCLNT INNER JOIN

dbo.policies PLCY WITH(NOLOCK) ON PCLNT.policy_id= PLCY.policy_Id

INNER JOIN dbo.party_types PTYP

WITH(NOLOCK) ON PTYP.type_id = PCLNT.type_id

WHERE PLCY.policy_id = @policyID AND PTYP.type_id = @CLNTTYPID)

GROUP BY IMSQD.client_id, IMSQD.option_id, IMSQD.revi_lumpsum, IMSQD.coverage_id, CVGS.description

ORDER BY IMSQD.client_id

--Query to return benefit-type FOR member </frame>

exec dbo.procGenerateIMSResult @policyID </frame>

</executionStack>

Proc [Database Id = 6 Object Id = 1234207547] </inputbuf>

</process>

SELECT DISTINCT IMSQD.client_id

FROM dbo.IMS_Quote_Details IMSQD

INNER JOIN dbo.coverages CVGS WITH(NOLOCK) ON CVGS.coverage_id = IMSQD.coverage_id WHERE

client_id IN

(SELECT DISTINCT PCLNT.client_id FROM dbo.policy_clients PCLNT INNER JOIN

dbo.policies PLCY WITH(NOLOCK) ON PCLNT.policy_id= PLCY.policy_Id

INNER JOIN dbo.party_types PTYP

WITH(NOLOCK) ON PTYP.type_id = PCLNT.type_id

WHERE PLCY.policy_id = @policyID AND PTYP.type_id = @CLNTTYPID)

GROUP BY IMSQD.client_id, IMSQD.option_id, IMSQD.revi_lumpsum, IMSQD.coverage_id, CVGS.description

ORDER BY IMSQD.client_id

--Query to return benefit-type FOR member </frame>

exec dbo.procGenerateIMSResult @policyID </frame>

EXEC @return_value = [dbo].[procGenerateIMS_Msg]

@policyID = 6312,

@userType = N'B',

@loginID = N'B',

@isSampleIMS = True,

@ReturnCode = @ReturnCode OUTPUT,

@BusErrorMsg = @BusErrorMsg OUTPUT,

@TecErrorMsg = @TecErrorMsg OUTPUT </frame>

</executionStack>

DECLARE @return_value int,

@ReturnCode varchar(20),

@BusErrorMsg varchar(500),

@TecErrorMsg varchar(4000)

EXEC @return_value = [dbo].[procGenerateIMS_Msg]

@policyID = 6312,

@userType = N'B',

@loginID = N'B',

@isSampleIMS = True,

@ReturnCode = @ReturnCode OUTPUT,

@BusErrorMsg = @BusErrorMsg OUTPUT,

@TecErrorMsg = @TecErrorMsg OUTPUT

SELECT @ReturnCode as N'@ReturnCode',

@BusErrorMsg as N'@BusErrorMsg',

@TecErrorMsg as N'@TecErrorMsg'

SELECT 'Return Value' = @return_value

</inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

</resource-list>

</deadlock>

</deadlock-list>

Krasavita SSC-Insane Points: 23138 More actions · Answer 5

I don't know how to run deadlock graph,but from red gate here is my results:

spid:101 dbid: 7, object id: 72057606206259200, index id: (4d17e82b2d6c) UPDATE Line #: 727

Language Event: (@P1 image,@P2 varbinary(579),@P3 datetime OUTPUT,@P4 int OUTPUT,@P5 int OUTPUT,@P6 int,@P7 varbinary(16) OUTPUT,@P8 varbinary(19),@P9 nvarchar(15),@P10 nvarchar(15),@P11 nvarchar(4),@P12 nvarchar(41),@P13 nvarchar(41),@P14 nvarchar(7),@P15 nvarchar(1),@

2)169

Language Event: (@L0 uniqueidentifier,@L2 uniqueidentifier,@IU int,@L3 uniqueidentifier,@L4 uniqueidentifier,@L5 uniqueidentifier,@II int) SELECT t1.[Type] AS c0,t3.[tp_ID] AS c10c5,UserData.[ntext2],UserData.[nvarchar10],UserData.[datetime1],UserData.[nvarchar37],t1.

Victim process dbid: 7, object id: 72057606206324736, index id: (1d0367d1d522) SELECT Line #: 1

SQL query fragment:

Language Event: (@L0 uniqueidentifier,@L2 uniqueidentifier,@IU int,@L3 uniqueidentifier,@L4 uniqueidentifier,@L5 uniqueidentifier,@II int) SELECT t1.[Type] AS c0,t3.[tp_ID] AS c10c5,UserData.[ntext2],UserData.[nvarchar10],UserData.[datetime1],UserData.[nvarchar37],t1.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

If you are talking about sql monitor you should be able to see more than that.

Also with the traceflag on you will have all the info we (as in Gail) need. Right now it's incomplete.

Krasavita SSC-Insane Points: 23138 More actions · Answer 7

sql monitor:

Deadlock encountered .... Printing deadlock information

Wait-for graph

Node:1

KEY: 7:72057606206259200 (4d17e82b2d6c) CleanCnt:3 Mode:X Flags: 0x0

Grant List 3:

Owner:0x00000002654A4A80 Mode: X Flg:0x0 Ref:0 Life:02000001 SPID:101 ECID:0 XactLockInfo: 0x0000000350E923A8

SPID: 101 ECID: 0 Statement Type: UPDATE Line #: 727

Input Buf: Language Event: (@P1 image,@P2 varbinary(579),@P3 datetime OUTPUT,@P4 int OUTPUT,@P5 int OUTPUT,@P6 int,@P7 varbinary(16) OUTPUT,@P8 varbinary(19),@P9 nvarchar(15),@P10 nvarchar(15),@P11 nvarchar(4),@P12 nvarchar(41),@P13 nvarchar(41),@P14 nvarchar(7),@P15 nvarchar(1),@P

Requested By:

ResType:LockOwner Stype:'OR'Xdes:0x0000000081270558 Mode: S SPID:169 BatchID:0 ECID:0 TaskProxy:(0x0000000162314598) Value:0x78608680 Cost:(0/0)

Node:2

KEY: 7:72057606206324736 (1d0367d1d522) CleanCnt:2 Mode:U Flags: 0x0

Grant List 3:

Owner:0x00000001178A8280 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:169 ECID:0 XactLockInfo: 0x0000000081270590

Deadlock encountered .... Printing deadlock information

Wait-for graph

Node:1

KEY: 7:72057606206259200 (4d17e82b2d6c) CleanCnt:3 Mode:X Flags: 0x0

Grant List 3:

Owner:0x00000002654A4A80 Mode: X Flg:0x0 Ref:0 Life:02000001 SPID:101 ECID:0 XactLockInfo: 0x0000000350E923A8

SPID: 101 ECID: 0 Statement Type: UPDATE Line #: 727

Input Buf: Language Event: (@P1 image,@P2 varbinary(579),@P3 datetime OUTPUT,@P4 int OUTPUT,@P5 int OUTPUT,@P6 int,@P7 varbinary(16) OUTPUT,@P8 varbinary(19),@P9 nvarchar(15),@P10 nvarchar(15),@P11 nvarchar(4),@P12 nvarchar(41),@P13 nvarchar(41),@P14 nvarchar(7),@P15 nvarchar(1),@P

Requested By:

ResType:LockOwner Stype:'OR'Xdes:0x0000000081270558 Mode: S SPID:169 BatchID:0 ECID:0 TaskProxy:(0x0000000162314598) Value:0x78608680 Cost:(0/0)

Node:2

KEY: 7:72057606206324736 (1d0367d1d522) CleanCnt:2 Mode:U Flags: 0x0

Grant List 3:

Owner:0x00000001178A8280 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:169 ECID:0 XactLockInfo: 0x0000000081270590

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

The command gets cut, we (still as in Gail), need to see the whole command.

Enable the 1222 traceflag and then post from the sql logs once you get it again.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

That's the output from traceflag 1204. old style, limited info, hard to read.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass