Deadlock info: How to intepret ?

Trying to do a shrinkfile and it always seems to deadlock. Can someone help
me intepret this and maybe suggest a way on how i can get the shrinkfile to
succeed?
Deadlock encountered ... Printing deadlock information
2003-09-09 16:37:34.10 spid4
2003-09-09 16:37:34.10 spid4 Wait-for graph
2003-09-09 16:37:34.10 spid4
2003-09-09 16:37:34.10 spid4 Node:1
2003-09-09 16:37:34.10 spid4 TAB: 9:1993058136 [] CleanCnt:1
Mode: IX Flags: 0x0
2003-09-09 16:37:34.10 spid4 Grant List::
2003-09-09 16:37:34.10 spid4 Owner:0x56c315e0 Mode: IS Flg:0x0
Ref:1 Life:00000000 SPID:87 ECID:0
2003-09-09 16:37:34.10 spid4 SPID: 87 ECID: 0 Statement Type: EXECUTE
Line #: 1
2003-09-09 16:37:34.10 spid4 Input Buf: RPC Event: sp_cursoropen;1
2003-09-09 16:37:34.10 spid4 Requested By:
2003-09-09 16:37:34.10 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:81 ECID:0 Ec:(0x1a1c1530) Value:0x5bbdf800 Cost:(1/0)
2003-09-09 16:37:34.10 spid4
2003-09-09 16:37:34.10 spid4 Node:2
2003-09-09 16:37:34.10 spid4 PAG: 9:1:2875975 CleanCnt:1
Mode: X Flags: 0x0
2003-09-09 16:37:34.10 spid4 Grant List::
2003-09-09 16:37:34.10 spid4 Owner:0x59e235e0 Mode: X Flg:0x0
Ref:1 Life:00000000 SPID:81 ECID:0
2003-09-09 16:37:34.10 spid4 SPID: 81 ECID: 0 Statement Type: BACKUP
LOG Line #: 1
2003-09-09 16:37:34.10 spid4 Input Buf: Language Event: dbcc
shrinkfile(Northwind_Data ,20000)
2003-09-09 16:37:34.10 spid4 Requested By:
2003-09-09 16:37:34.10 spid4 ResType:LockOwner Stype:'OR' Mode: IS
SPID:87 ECID:0 Ec:(0x4b189558) Value:0x56c31a40 Cost:(0/0)
2003-09-09 16:37:34.10 spid4 Victim Resource Owner:
2003-09-09 16:37:34.10 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:81 ECID:0 Ec:(0x1a1c1530) Value:0x5bbdf800 Cost:(1/0)I shrink file every night, it has three steps;
1 Set databasae into simple recovery mode
2 Shrink log
3 Set database into full recovery mode
alter database proclaim set recovery simple
dbcc shrinkfile (trainingproclaimlog,1000)
alter database proclaim set recovery full
I hope it helps
>--Original Message--
>Trying to do a shrinkfile and it always seems to
deadlock. Can someone help
>me intepret this and maybe suggest a way on how i can get
the shrinkfile to
>succeed?
>Deadlock encountered ... Printing deadlock information
>2003-09-09 16:37:34.10 spid4
>2003-09-09 16:37:34.10 spid4 Wait-for graph
>2003-09-09 16:37:34.10 spid4
>2003-09-09 16:37:34.10 spid4 Node:1
>2003-09-09 16:37:34.10 spid4 TAB: 9:1993058136
[] CleanCnt:1
>Mode: IX Flags: 0x0
>2003-09-09 16:37:34.10 spid4 Grant List::
>2003-09-09 16:37:34.10 spid4 Owner:0x56c315e0
Mode: IS Flg:0x0
>Ref:1 Life:00000000 SPID:87 ECID:0
>2003-09-09 16:37:34.10 spid4 SPID: 87 ECID: 0
Statement Type: EXECUTE
>Line #: 1
>2003-09-09 16:37:34.10 spid4 Input Buf: RPC Event:
sp_cursoropen;1
>2003-09-09 16:37:34.10 spid4 Requested By:
>2003-09-09 16:37:34.10 spid4 ResType:LockOwner
Stype:'OR' Mode: X
>SPID:81 ECID:0 Ec:(0x1a1c1530) Value:0x5bbdf800 Cost:(1/0)
>2003-09-09 16:37:34.10 spid4
>2003-09-09 16:37:34.10 spid4 Node:2
>2003-09-09 16:37:34.10 spid4 PAG:
9:1:2875975 CleanCnt:1
>Mode: X Flags: 0x0
>2003-09-09 16:37:34.10 spid4 Grant List::
>2003-09-09 16:37:34.10 spid4 Owner:0x59e235e0
Mode: X Flg:0x0
>Ref:1 Life:00000000 SPID:81 ECID:0
>2003-09-09 16:37:34.10 spid4 SPID: 81 ECID: 0
Statement Type: BACKUP
>LOG Line #: 1
>2003-09-09 16:37:34.10 spid4 Input Buf: Language
Event: dbcc
>shrinkfile(Northwind_Data ,20000)
>2003-09-09 16:37:34.10 spid4 Requested By:
>2003-09-09 16:37:34.10 spid4 ResType:LockOwner
Stype:'OR' Mode: IS
>SPID:87 ECID:0 Ec:(0x4b189558) Value:0x56c31a40 Cost:(0/0)
>2003-09-09 16:37:34.10 spid4 Victim Resource Owner:
>2003-09-09 16:37:34.10 spid4 ResType:LockOwner
Stype:'OR' Mode: X
>SPID:81 ECID:0 Ec:(0x1a1c1530) Value:0x5bbdf800 Cost:(1/0)
>
>.
>|||Amar,
Why do you shrink the files everynight? If you have to keep shrinking it
then that means it wasn't big enough to begin with. I suggest you leave it
at that size so it doesn't have to keep growing and shrinking.
--
Andrew J. Kelly
SQL Server MVP
"Amar" <amar.pabla@.wanneroo.gov.au> wrote in message
news:036f01c3773f$3ecd1ae0$a601280a@.phx.gbl...
> I shrink file every night, it has three steps;
> 1 Set databasae into simple recovery mode
> 2 Shrink log
> 3 Set database into full recovery mode
> alter database proclaim set recovery simple
> dbcc shrinkfile (trainingproclaimlog,1000)
> alter database proclaim set recovery full
> I hope it helps
> >--Original Message--
> >Trying to do a shrinkfile and it always seems to
> deadlock. Can someone help
> >me intepret this and maybe suggest a way on how i can get
> the shrinkfile to
> >succeed?
> >
> >Deadlock encountered ... Printing deadlock information
> >2003-09-09 16:37:34.10 spid4
> >2003-09-09 16:37:34.10 spid4 Wait-for graph
> >2003-09-09 16:37:34.10 spid4
> >2003-09-09 16:37:34.10 spid4 Node:1
> >2003-09-09 16:37:34.10 spid4 TAB: 9:1993058136
> [] CleanCnt:1
> >Mode: IX Flags: 0x0
> >2003-09-09 16:37:34.10 spid4 Grant List::
> >2003-09-09 16:37:34.10 spid4 Owner:0x56c315e0
> Mode: IS Flg:0x0
> >Ref:1 Life:00000000 SPID:87 ECID:0
> >2003-09-09 16:37:34.10 spid4 SPID: 87 ECID: 0
> Statement Type: EXECUTE
> >Line #: 1
> >2003-09-09 16:37:34.10 spid4 Input Buf: RPC Event:
> sp_cursoropen;1
> >2003-09-09 16:37:34.10 spid4 Requested By:
> >2003-09-09 16:37:34.10 spid4 ResType:LockOwner
> Stype:'OR' Mode: X
> >SPID:81 ECID:0 Ec:(0x1a1c1530) Value:0x5bbdf800 Cost:(1/0)
> >2003-09-09 16:37:34.10 spid4
> >2003-09-09 16:37:34.10 spid4 Node:2
> >2003-09-09 16:37:34.10 spid4 PAG:
> 9:1:2875975 CleanCnt:1
> >Mode: X Flags: 0x0
> >2003-09-09 16:37:34.10 spid4 Grant List::
> >2003-09-09 16:37:34.10 spid4 Owner:0x59e235e0
> Mode: X Flg:0x0
> >Ref:1 Life:00000000 SPID:81 ECID:0
> >2003-09-09 16:37:34.10 spid4 SPID: 81 ECID: 0
> Statement Type: BACKUP
> >LOG Line #: 1
> >2003-09-09 16:37:34.10 spid4 Input Buf: Language
> Event: dbcc
> >shrinkfile(Northwind_Data ,20000)
> >
> >2003-09-09 16:37:34.10 spid4 Requested By:
> >2003-09-09 16:37:34.10 spid4 ResType:LockOwner
> Stype:'OR' Mode: IS
> >SPID:87 ECID:0 Ec:(0x4b189558) Value:0x56c31a40 Cost:(0/0)
> >2003-09-09 16:37:34.10 spid4 Victim Resource Owner:
> >2003-09-09 16:37:34.10 spid4 ResType:LockOwner
> Stype:'OR' Mode: X
> >SPID:81 ECID:0 Ec:(0x1a1c1530) Value:0x5bbdf800 Cost:(1/0)
> >
> >
> >.
> >|||In addition to turning off backup jobs (thanks for the tip, Andrew), you
might want to see what else is going on (any other scheduled jobs?) while
the shrinkfile is trying to execute. I noticed spid87 and spid81 in the log.
which came to life before your statement was actually executed. As Andrew
pointed out, the backup might be running while you're trying to shrink the
file.
HTH,
-Morgan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e7IHeR0dDHA.1752@.TK2MSFTNGP11.phx.gbl...
> Make sure you turn off your backup jobs while this is shrinking.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OyJEy%23ydDHA.3428@.tk2msftngp13.phx.gbl...
> > Trying to do a shrinkfile and it always seems to deadlock. Can someone
> help
> > me intepret this and maybe suggest a way on how i can get the shrinkfile
> to
> > succeed?
> >
> > Deadlock encountered ... Printing deadlock information
> > 2003-09-09 16:37:34.10 spid4
> > 2003-09-09 16:37:34.10 spid4 Wait-for graph
> > 2003-09-09 16:37:34.10 spid4
> > 2003-09-09 16:37:34.10 spid4 Node:1
> > 2003-09-09 16:37:34.10 spid4 TAB: 9:1993058136 []
CleanCnt:1
> > Mode: IX Flags: 0x0
> > 2003-09-09 16:37:34.10 spid4 Grant List::
> > 2003-09-09 16:37:34.10 spid4 Owner:0x56c315e0 Mode: IS
> Flg:0x0
> > Ref:1 Life:00000000 SPID:87 ECID:0
> > 2003-09-09 16:37:34.10 spid4 SPID: 87 ECID: 0 Statement Type:
> EXECUTE
> > Line #: 1
> > 2003-09-09 16:37:34.10 spid4 Input Buf: RPC Event:
sp_cursoropen;1
> > 2003-09-09 16:37:34.10 spid4 Requested By:
> > 2003-09-09 16:37:34.10 spid4 ResType:LockOwner Stype:'OR' Mode: X
> > SPID:81 ECID:0 Ec:(0x1a1c1530) Value:0x5bbdf800 Cost:(1/0)
> > 2003-09-09 16:37:34.10 spid4
> > 2003-09-09 16:37:34.10 spid4 Node:2
> > 2003-09-09 16:37:34.10 spid4 PAG: 9:1:2875975
CleanCnt:1
> > Mode: X Flags: 0x0
> > 2003-09-09 16:37:34.10 spid4 Grant List::
> > 2003-09-09 16:37:34.10 spid4 Owner:0x59e235e0 Mode: X
> Flg:0x0
> > Ref:1 Life:00000000 SPID:81 ECID:0
> > 2003-09-09 16:37:34.10 spid4 SPID: 81 ECID: 0 Statement Type:
> BACKUP
> > LOG Line #: 1
> > 2003-09-09 16:37:34.10 spid4 Input Buf: Language Event: dbcc
> > shrinkfile(Northwind_Data ,20000)
> >
> > 2003-09-09 16:37:34.10 spid4 Requested By:
> > 2003-09-09 16:37:34.10 spid4 ResType:LockOwner Stype:'OR' Mode:
IS
> > SPID:87 ECID:0 Ec:(0x4b189558) Value:0x56c31a40 Cost:(0/0)
> > 2003-09-09 16:37:34.10 spid4 Victim Resource Owner:
> > 2003-09-09 16:37:34.10 spid4 ResType:LockOwner Stype:'OR' Mode: X
> > SPID:81 ECID:0 Ec:(0x1a1c1530) Value:0x5bbdf800 Cost:(1/0)
> >
> >
>