deadlocks after dbcc checkdb

sql2k sp3
Over the weekend I had severe corruption and needed to do DBCC with
repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
different tables. Any ideas as to why? I know I can trace them and figure
them out, but I was curious as to why this would start to happen after my
problems?
TIA, ChrisR.
The only thing I can think of is that the repair dropped some pages. The
data now resides on fewer pages, fewer pages with the same number of locks =
deadlock..
The problem with this thinking is that I do not know if DBCC repair moves
rows or simply drops bad page links..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"ChrisR" <noemail@.bla.com> wrote in message
news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
> sql2k sp3
> Over the weekend I had severe corruption and needed to do DBCC with
> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
> different tables. Any ideas as to why? I know I can trace them and figure
> them out, but I was curious as to why this would start to happen after my
> problems?
> TIA, ChrisR.
>
|||How about if repair removed a whole index in order to repair? That could increase deadlock due to
longer execution time = overlap. Or? I don't know whether repair does drop a whole index, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
> The only thing I can think of is that the repair dropped some pages. The
> data now resides on fewer pages, fewer pages with the same number of locks =
> deadlock..
> The problem with this thinking is that I do not know if DBCC repair moves
> rows or simply drops bad page links..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
>
|||Assuming you're on SQL 2000, repair won't drop whole indexes (only rebuild
them) or do anything that would cause deadlocks. However, it probably had to
delete some pages so whatever application logic was inherent in the database
is now broken (i.e. if your app relied on certain values being present in
multiple tables, that may not be the case any more).
Did you study the output of repair to work out roughly what it dropped? I
take it your backup was broken too which is why you had to run repair rather
than restoring your backup? Did you work out why the corruption happened in
the first place so you can ensure it doesn't happen again?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecDwqnBUFHA.2928@.TK2MSFTNGP09.phx.gbl...
> How about if repair removed a whole index in order to repair? That could
increase deadlock due to
> longer execution time = overlap. Or? I don't know whether repair does drop
a whole index, though...[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
locks =[vbcol=seagreen]
moves[vbcol=seagreen]
figure[vbcol=seagreen]
my
>
|||Hardware issues were the cause. Yes the backups were bad. MS is now
evaluating the output. Thanks to all who helped.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OXOmsFPUFHA.1148@.tk2msftngp13.phx.gbl...
> Assuming you're on SQL 2000, repair won't drop whole indexes (only rebuild
> them) or do anything that would cause deadlocks. However, it probably had
> to
> delete some pages so whatever application logic was inherent in the
> database
> is now broken (i.e. if your app relied on certain values being present in
> multiple tables, that may not be the case any more).
> Did you study the output of repair to work out roughly what it dropped? I
> take it your backup was broken too which is why you had to run repair
> rather
> than restoring your backup? Did you work out why the corruption happened
> in
> the first place so you can ensure it doesn't happen again?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:ecDwqnBUFHA.2928@.TK2MSFTNGP09.phx.gbl...
> increase deadlock due to
> a whole index, though...
> locks =
> moves
> figure
> my
>

deadlocks after dbcc checkdb

sql2k sp3
Over the weekend I had severe corruption and needed to do DBCC with
repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
different tables. Any ideas as to why? I know I can trace them and figure
them out, but I was curious as to why this would start to happen after my
problems?
TIA, ChrisR.The only thing I can think of is that the repair dropped some pages. The
data now resides on fewer pages, fewer pages with the same number of locks =deadlock..
The problem with this thinking is that I do not know if DBCC repair moves
rows or simply drops bad page links..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"ChrisR" <noemail@.bla.com> wrote in message
news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
> sql2k sp3
> Over the weekend I had severe corruption and needed to do DBCC with
> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
> different tables. Any ideas as to why? I know I can trace them and figure
> them out, but I was curious as to why this would start to happen after my
> problems?
> TIA, ChrisR.
>|||How about if repair removed a whole index in order to repair? That could increase deadlock due to
longer execution time = overlap. Or? I don't know whether repair does drop a whole index, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
> The only thing I can think of is that the repair dropped some pages. The
> data now resides on fewer pages, fewer pages with the same number of locks => deadlock..
> The problem with this thinking is that I do not know if DBCC repair moves
> rows or simply drops bad page links..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
>> sql2k sp3
>> Over the weekend I had severe corruption and needed to do DBCC with
>> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
>> different tables. Any ideas as to why? I know I can trace them and figure
>> them out, but I was curious as to why this would start to happen after my
>> problems?
>> TIA, ChrisR.
>>
>|||Assuming you're on SQL 2000, repair won't drop whole indexes (only rebuild
them) or do anything that would cause deadlocks. However, it probably had to
delete some pages so whatever application logic was inherent in the database
is now broken (i.e. if your app relied on certain values being present in
multiple tables, that may not be the case any more).
Did you study the output of repair to work out roughly what it dropped? I
take it your backup was broken too which is why you had to run repair rather
than restoring your backup? Did you work out why the corruption happened in
the first place so you can ensure it doesn't happen again?
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecDwqnBUFHA.2928@.TK2MSFTNGP09.phx.gbl...
> How about if repair removed a whole index in order to repair? That could
increase deadlock due to
> longer execution time = overlap. Or? I don't know whether repair does drop
a whole index, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
> > The only thing I can think of is that the repair dropped some pages. The
> > data now resides on fewer pages, fewer pages with the same number of
locks => > deadlock..
> >
> > The problem with this thinking is that I do not know if DBCC repair
moves
> > rows or simply drops bad page links..
> >
> >
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > (Please respond only to the newsgroup.)
> >
> > I support the Professional Association for SQL Server ( PASS) and it's
> > community of SQL Professionals.
> > "ChrisR" <noemail@.bla.com> wrote in message
> > news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
> >> sql2k sp3
> >>
> >> Over the weekend I had severe corruption and needed to do DBCC with
> >> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
> >> different tables. Any ideas as to why? I know I can trace them and
figure
> >> them out, but I was curious as to why this would start to happen after
my
> >> problems?
> >>
> >> TIA, ChrisR.
> >>
> >>
> >
> >
>|||Hardware issues were the cause. Yes the backups were bad. MS is now
evaluating the output. Thanks to all who helped.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OXOmsFPUFHA.1148@.tk2msftngp13.phx.gbl...
> Assuming you're on SQL 2000, repair won't drop whole indexes (only rebuild
> them) or do anything that would cause deadlocks. However, it probably had
> to
> delete some pages so whatever application logic was inherent in the
> database
> is now broken (i.e. if your app relied on certain values being present in
> multiple tables, that may not be the case any more).
> Did you study the output of repair to work out roughly what it dropped? I
> take it your backup was broken too which is why you had to run repair
> rather
> than restoring your backup? Did you work out why the corruption happened
> in
> the first place so you can ensure it doesn't happen again?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:ecDwqnBUFHA.2928@.TK2MSFTNGP09.phx.gbl...
>> How about if repair removed a whole index in order to repair? That could
> increase deadlock due to
>> longer execution time = overlap. Or? I don't know whether repair does
>> drop
> a whole index, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
>> > The only thing I can think of is that the repair dropped some pages.
>> > The
>> > data now resides on fewer pages, fewer pages with the same number of
> locks =>> > deadlock..
>> >
>> > The problem with this thinking is that I do not know if DBCC repair
> moves
>> > rows or simply drops bad page links..
>> >
>> >
>> > --
>> > Wayne Snyder MCDBA, SQL Server MVP
>> > Mariner, Charlotte, NC
>> > (Please respond only to the newsgroup.)
>> >
>> > I support the Professional Association for SQL Server ( PASS) and it's
>> > community of SQL Professionals.
>> > "ChrisR" <noemail@.bla.com> wrote in message
>> > news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
>> >> sql2k sp3
>> >>
>> >> Over the weekend I had severe corruption and needed to do DBCC with
>> >> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots
>> >> of
>> >> different tables. Any ideas as to why? I know I can trace them and
> figure
>> >> them out, but I was curious as to why this would start to happen after
> my
>> >> problems?
>> >>
>> >> TIA, ChrisR.
>> >>
>> >>
>> >
>> >
>>
>

deadlocks after dbcc checkdb

sql2k sp3
Over the weekend I had severe corruption and needed to do DBCC with
repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
different tables. Any ideas as to why? I know I can trace them and figure
them out, but I was curious as to why this would start to happen after my
problems?
TIA, ChrisR.The only thing I can think of is that the repair dropped some pages. The
data now resides on fewer pages, fewer pages with the same number of locks =
deadlock..
The problem with this thinking is that I do not know if DBCC repair moves
rows or simply drops bad page links..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"ChrisR" <noemail@.bla.com> wrote in message
news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
> sql2k sp3
> Over the weekend I had severe corruption and needed to do DBCC with
> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
> different tables. Any ideas as to why? I know I can trace them and figure
> them out, but I was curious as to why this would start to happen after my
> problems?
> TIA, ChrisR.
>|||How about if repair removed a whole index in order to repair? That could inc
rease deadlock due to
longer execution time = overlap. Or? I don't know whether repair does drop a
whole index, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
> The only thing I can think of is that the repair dropped some pages. The
> data now resides on fewer pages, fewer pages with the same number of locks
=
> deadlock..
> The problem with this thinking is that I do not know if DBCC repair moves
> rows or simply drops bad page links..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
>|||Assuming you're on SQL 2000, repair won't drop whole indexes (only rebuild
them) or do anything that would cause deadlocks. However, it probably had to
delete some pages so whatever application logic was inherent in the database
is now broken (i.e. if your app relied on certain values being present in
multiple tables, that may not be the case any more).
Did you study the output of repair to work out roughly what it dropped? I
take it your backup was broken too which is why you had to run repair rather
than restoring your backup? Did you work out why the corruption happened in
the first place so you can ensure it doesn't happen again?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecDwqnBUFHA.2928@.TK2MSFTNGP09.phx.gbl...
> How about if repair removed a whole index in order to repair? That could
increase deadlock due to
> longer execution time = overlap. Or? I don't know whether repair does drop
a whole index, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
locks =[vbcol=seagreen]
moves[vbcol=seagreen]
figure[vbcol=seagreen]
my[vbcol=seagreen]
>|||Hardware issues were the cause. Yes the backups were bad. MS is now
evaluating the output. Thanks to all who helped.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OXOmsFPUFHA.1148@.tk2msftngp13.phx.gbl...
> Assuming you're on SQL 2000, repair won't drop whole indexes (only rebuild
> them) or do anything that would cause deadlocks. However, it probably had
> to
> delete some pages so whatever application logic was inherent in the
> database
> is now broken (i.e. if your app relied on certain values being present in
> multiple tables, that may not be the case any more).
> Did you study the output of repair to work out roughly what it dropped? I
> take it your backup was broken too which is why you had to run repair
> rather
> than restoring your backup? Did you work out why the corruption happened
> in
> the first place so you can ensure it doesn't happen again?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:ecDwqnBUFHA.2928@.TK2MSFTNGP09.phx.gbl...
> increase deadlock due to
> a whole index, though...
> locks =
> moves
> figure
> my
>sql

Deadlock: Trace flag 1205, 1204

I want to log deadlocks. In query analyzer I ran dbcc
traceon(1205, 1204) on two different SQL Server 2000, SP3a
boxes. Stopped SQL Services and restarted on each box.
Created deadlocks on both boxes via the problem
application. Deadlocks are being written to sql server
logs on one box but not the other.
What is the difference and how can I tell if 1205 and 1204
trace flags are active?dbcc tracestatus(-1)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Mike Mullane" <mike.mullane@.hpinc.com> wrote in message
news:082101c38915$62471510$a301280a@.phx.gbl...
> I want to log deadlocks. In query analyzer I ran dbcc
> traceon(1205, 1204) on two different SQL Server 2000, SP3a
> boxes. Stopped SQL Services and restarted on each box.
> Created deadlocks on both boxes via the problem
> application. Deadlocks are being written to sql server
> logs on one box but not the other.
> What is the difference and how can I tell if 1205 and 1204
> trace flags are active?
>|||Hi Mike,
Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays the status of all
currently enabled trace flags by specifying a value of -1.
Please make sure that you problem application can make deadlock every time
when you execute it. Here is a deadlock example, please to perform the on
both SQL Server using Query Analyzer and check to see if the deadlock is
recorded in both SQL Server's log.
Create a simple deadlock in pubs in two Query Analyzer windows.
Window 1:
dbcc traceon(3605)
dbcc traceon(1204)
begin tran update authors set contract = contract
Window 2: begin tran update titles set ytd_sales = ytd_sales
Window 1: update titles set ytd_sales = ytd_sales
Window 2: update authors set contract = contract
It works on my side and I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hello Michael,
Perfect advice. I am able to recreate locks using your
example and validate the they are being written to the
log. However, I'm was having issues getting DBCC
TRACESTATUS(-1) or DBCC TRACESTATUS(1204) to behave as
described. When I run it I get: "Trace option(s) not
enabled for this connection. Use 'DBCC TRACEON()'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator."
So I ran "DBCC TRACEON" and then aftter running that I
ran "DBCC TRACESTATUS(-1)" and I get "TraceFlag Status
-- --
1204 1" which is what I want. So, it seems that the
order needed is "DBCC TRACEON(1204)" then "DBCC TRACEON"
must be run before "DBCC TRACESTATUS(-1)" will list.
Thanks for your help. I've learned a bit.
Mike
>--Original Message--
>Hi Mike,
>Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays
the status of all
>currently enabled trace flags by specifying a value of -1.
>Please make sure that you problem application can make
deadlock every time
>when you execute it. Here is a deadlock example, please
to perform the on
>both SQL Server using Query Analyzer and check to see if
the deadlock is
>recorded in both SQL Server's log.
>Create a simple deadlock in pubs in two Query Analyzer
windows.
>Window 1:
>dbcc traceon(3605)
>dbcc traceon(1204)
>begin tran update authors set contract = contract
>Window 2: begin tran update titles set ytd_sales =ytd_sales
>Window 1: update titles set ytd_sales = ytd_sales
>Window 2: update authors set contract = contract
>It works on my side and I am standing by for your
response.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>

Deadlock trace interpretation

I have the following from a DBCC Trace:
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:1
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:216 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: Range-
Insert-Null SPID:215 ECID:0 Ec0x9017B5B0) Value:0x1dec9180 Cost0/1E4)
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:2
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:215 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec0x9014D5B0) Value:0x1e31b200 Cost0/1D4)
06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec0x9014D5B0) Value:0x1e31b200 Cost0/1D4)
06/08/2006 16:29:02,spid4,Unknown,
Since there are range locks, it looks as if the transaction isolation level
SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
insert into a user table based on a select statement against a temporary
table. Prior to this insert that is being deadlocked, the temporary table is
populated and then updated by joining on several user tables, including the
table being used in the deadlock insert. Should I be focusing on the update
to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
insert upon which the deadlock is occurring and putting a HOLDLOCK on the
select statement of the temporary table that populates the user table?
Message posted via http://www.droptable.comHi cbrichards
Without the code (and probably the DDL) it's impossible to say.
You are right that there must be SERIALIZABLE isolation in order to get the
range locks. And if you are already in SERIALIZABLE isolation, adding
HOLDLOCK would be redundant.
Sometimes you can avoid deadlock by requesting an X lock on data in a SELECT
statement, so there is no chance of another process also reading it and
holding onto the locks, until the first process is done. But again, without
any more details from you, there's little else to say.
--
HTH
Kalen Delaney, SQL Server MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:617d199ca3401@.uwe...
>I have the following from a DBCC Trace:
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:1
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:216 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode:
> Range-
> Insert-Null SPID:215 ECID:0 Ec0x9017B5B0) Value:0x1dec9180 Cost0/1E4)
>
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:2
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:215 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec0x9014D5B0) Value:0x1e31b200 Cost0/1D4)
>
> 06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec0x9014D5B0) Value:0x1e31b200 Cost0/1D4)
> 06/08/2006 16:29:02,spid4,Unknown,
>
> Since there are range locks, it looks as if the transaction isolation
> level
> SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
> insert into a user table based on a select statement against a temporary
> table. Prior to this insert that is being deadlocked, the temporary table
> is
> populated and then updated by joining on several user tables, including
> the
> table being used in the deadlock insert. Should I be focusing on the
> update
> to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
> insert upon which the deadlock is occurring and putting a HOLDLOCK on the
> select statement of the temporary table that populates the user table?
> --
> Message posted via http://www.droptable.com|||Hi Karen,
The stored proc code and DDL is included. Thanks for your help.
ALTER PROCEDURE [dbo].[MyStoredProc]
@.E_UID INT OUTPUT,
@.LKey Int,
@.RFID Int,
@.CID Int = Null,
@.CName varchar(255),
@.CCmID VARCHAR(30),
@.PID varchar(255),
@.CmS varchar(3),
@.VID varchar(38),
@.CmPtAm money
AS
DECLARE @.sVIDScrub varchar(38)
-- Determine the value to stuff in the VID field. First get rid of any non-
numerics
SET @.sVIDScrub = dbo.udf_Alpha(@.VID, 1)
CREATE TABLE #TRes (
LKey int,
RFID int,
CID int,
CName varchar(255),
CCmID varchar(30),
PID varchar(255),
CmS varchar(3),
VID varchar(38),
CmPtAm money,
CFID int,
CCode char(8),
PRIMARY KEY (LKey))
INSERT #TRes
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
CmPtAm,
CFID,
CCode )
SELECT @.LKey,
@.RFID,
@.CID,
@.CName,
@.CCmID,
@.PID,
@.CmS,
@.VID,
@.CmPtAm,
null,
null
/* Determine what value to stick into CFID
In order to match up car:
1) try for an exact CPID match; if none, then
2) see if the E CPID is contained in any car CPID or vice versa.
*/
-- Get car information based on CID
UPDATE TR
SET TR.CFID = Cast(Car.CUID AS varchar(10)),
TR.CCode = RTrim(Car.CCode),
TR.CName = Car.CName
FROM #TRes TR
JOIN dbo.RCm RCm
ON RCm.RFID = @.RFID
AND RCm.LKey = @.LKey
JOIN dbo.CarHist CBH
ON CBH.CmID = RCm.CmID
AND CBH.LKey = RCm.LKey
JOIN dbo.Cars Car
ON Car.CUID = CBH.CFID
AND Car.LKey = CBH.LKey
WHERE RCm.LKey = @.LKey
AND CBH.LKey = @.LKey
AND Car.LKey = @.LKey
--Insert the record
INSERT dbo.RCm
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
CmID,
VID,
CmPtAm,
CFID,
CCode )
SELECT LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
-- Only fill the VID column if the size fits the column data type
CASE
WHEN Len(@.sVIDScrub) < 10 THEN Cast(@.sVIDScrub AS int)
ELSE 0
END,
CmPtAm,
CFID,
CCode
FROM #TRes
-- Return the new id
SET @.@.E_UID = SCOPE_IDENTITY()
-- ****************************************
**********************************
**
CREATE TABLE [dbo].[Cars](
[LKey] [int] NOT NULL,
[CUID] [int] IDENTITY(1,1) NOT NULL,
[CCode] [varchar](8) NOT NULL,
[CName] [varchar](35) NOT NULL,
[CUser] [char](12) NOT NULL,
[CDate] [datetime] NOT NULL
CONSTRAINT [PK_Cars] PRIMARY KEY NONCLUSTERED
(
[CUID] ASC,
[LKey] ASC
) ON [PRIMARY],
CONSTRAINT [Unique_CCode] UNIQUE NONCLUSTERED
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_CUID] ON [dbo].[Cars]
(
[LKey] ASC,
[CUID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CCode] ON [dbo].[Cars]
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
-- ****************************************
*******************************
CREATE TABLE [dbo].[RCm](
[E_UID] [int] IDENTITY(1,1) NOT NULL,
[LKey] [int] NOT NULL,
[RFID] [int] NOT NULL,
[CID] [int] NULL,
[CName] [varchar](100) NULL,
[CCmID] [varchar](30) NULL,
[PID] [varchar](255) NULL,
[CmS] [varchar](3) NULL,
[CmID] [varchar](38) NOT NULL,
[VID] [int] NOT NULL DEFAULT ((-1)),
[CmPtAm] [money] NULL,
[CFID] [int] NULL,
[CCode] [char](8) NULL,
CONSTRAINT [PK_RCm] PRIMARY KEY NONCLUSTERED
(
[E_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_E_UID] ON [dbo].[RCm]
(
[Key] ASC,
[E_UID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_RCm_RFID] ON [dbo].[RCm]
(
[RFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CarHist](
[LKey] [int] NOT NULL,
[CarHist_UID] [int] IDENTITY(1,1) NOT NULL,
[CDetFID] [int] NOT NULL CONSTRAINT [DF__CarCharge] DEFAULT (1)
,
[CFID] [int] NOT NULL,
[CCode] [varchar](8) NOT NULL,
[VFID] [int] NULL,
[CmID] AS (convert(varchar(10),[VisitFID]) + ltrim([ClaimIDSuff
ix])),
CONSTRAINT [PK_CarHist] PRIMARY KEY NONCLUSTERED
(
[CarHist_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE CLUSTERED INDEX [IDX1_CBH_CDetFID] ON [dbo].[CarHist]
(
[CDetFID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CBH_VFID] ON [dbo].[CarHist]
(
[VFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
Kalen Delaney wrote:[vbcol=seagreen]
>Hi cbrichards
>Without the code (and probably the DDL) it's impossible to say.
>You are right that there must be SERIALIZABLE isolation in order to get the
>range locks. And if you are already in SERIALIZABLE isolation, adding
>HOLDLOCK would be redundant.
>Sometimes you can avoid deadlock by requesting an X lock on data in a SELEC
T
>statement, so there is no chance of another process also reading it and
>holding onto the locks, until the first process is done. But again, without
>any more details from you, there's little else to say.
>[quoted text clipped - 51 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1

Deadlock trace interpretation

I have the following from a DBCC Trace:
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:1
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:216 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: Range-
Insert-Null SPID:215 ECID:0 Ec:(0x9017B5B0) Value:0x1dec9180 Cost:(0/1E4)
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:2
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:215 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
06/08/2006 16:29:02,spid4,Unknown,
Since there are range locks, it looks as if the transaction isolation level
SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
insert into a user table based on a select statement against a temporary
table. Prior to this insert that is being deadlocked, the temporary table is
populated and then updated by joining on several user tables, including the
table being used in the deadlock insert. Should I be focusing on the update
to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
insert upon which the deadlock is occurring and putting a HOLDLOCK on the
select statement of the temporary table that populates the user table?
--
Message posted via http://www.sqlmonster.comHi cbrichards
Without the code (and probably the DDL) it's impossible to say.
You are right that there must be SERIALIZABLE isolation in order to get the
range locks. And if you are already in SERIALIZABLE isolation, adding
HOLDLOCK would be redundant.
Sometimes you can avoid deadlock by requesting an X lock on data in a SELECT
statement, so there is no chance of another process also reading it and
holding onto the locks, until the first process is done. But again, without
any more details from you, there's little else to say.
--
HTH
Kalen Delaney, SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:617d199ca3401@.uwe...
>I have the following from a DBCC Trace:
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:1
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:216 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode:
> Range-
> Insert-Null SPID:215 ECID:0 Ec:(0x9017B5B0) Value:0x1dec9180 Cost:(0/1E4)
>
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:2
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:215 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
>
> 06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
> 06/08/2006 16:29:02,spid4,Unknown,
>
> Since there are range locks, it looks as if the transaction isolation
> level
> SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
> insert into a user table based on a select statement against a temporary
> table. Prior to this insert that is being deadlocked, the temporary table
> is
> populated and then updated by joining on several user tables, including
> the
> table being used in the deadlock insert. Should I be focusing on the
> update
> to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
> insert upon which the deadlock is occurring and putting a HOLDLOCK on the
> select statement of the temporary table that populates the user table?
> --
> Message posted via http://www.sqlmonster.com|||Hi Karen,
The stored proc code and DDL is included. Thanks for your help.
ALTER PROCEDURE [dbo].[MyStoredProc]
@.E_UID INT OUTPUT,
@.LKey Int,
@.RFID Int,
@.CID Int = Null,
@.CName varchar(255),
@.CCmID VARCHAR(30),
@.PID varchar(255),
@.CmS varchar(3),
@.VID varchar(38),
@.CmPtAm money
AS
DECLARE @.sVIDScrub varchar(38)
-- Determine the value to stuff in the VID field. First get rid of any non-
numerics
SET @.sVIDScrub = dbo.udf_Alpha(@.VID, 1)
CREATE TABLE #TRes (
LKey int,
RFID int,
CID int,
CName varchar(255),
CCmID varchar(30),
PID varchar(255),
CmS varchar(3),
VID varchar(38),
CmPtAm money,
CFID int,
CCode char(8),
PRIMARY KEY (LKey))
INSERT #TRes
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
CmPtAm,
CFID,
CCode )
SELECT @.LKey,
@.RFID,
@.CID,
@.CName,
@.CCmID,
@.PID,
@.CmS,
@.VID,
@.CmPtAm,
null,
null
/* Determine what value to stick into CFID
In order to match up car:
1) try for an exact CPID match; if none, then
2) see if the E CPID is contained in any car CPID or vice versa.
*/
-- Get car information based on CID
UPDATE TR
SET TR.CFID = Cast(Car.CUID AS varchar(10)),
TR.CCode = RTrim(Car.CCode),
TR.CName = Car.CName
FROM #TRes TR
JOIN dbo.RCm RCm
ON RCm.RFID = @.RFID
AND RCm.LKey = @.LKey
JOIN dbo.CarHist CBH
ON CBH.CmID = RCm.CmID
AND CBH.LKey = RCm.LKey
JOIN dbo.Cars Car
ON Car.CUID = CBH.CFID
AND Car.LKey = CBH.LKey
WHERE RCm.LKey = @.LKey
AND CBH.LKey = @.LKey
AND Car.LKey = @.LKey
--Insert the record
INSERT dbo.RCm
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
CmID,
VID,
CmPtAm,
CFID,
CCode )
SELECT LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
-- Only fill the VID column if the size fits the column data type
CASE
WHEN Len(@.sVIDScrub) < 10 THEN Cast(@.sVIDScrub AS int)
ELSE 0
END,
CmPtAm,
CFID,
CCode
FROM #TRes
-- Return the new id
SET @.@.E_UID = SCOPE_IDENTITY()
--****************************************************************************
CREATE TABLE [dbo].[Cars](
[LKey] [int] NOT NULL,
[CUID] [int] IDENTITY(1,1) NOT NULL,
[CCode] [varchar](8) NOT NULL,
[CName] [varchar](35) NOT NULL,
[CUser] [char](12) NOT NULL,
[CDate] [datetime] NOT NULL
CONSTRAINT [PK_Cars] PRIMARY KEY NONCLUSTERED
(
[CUID] ASC,
[LKey] ASC
) ON [PRIMARY],
CONSTRAINT [Unique_CCode] UNIQUE NONCLUSTERED
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_CUID] ON [dbo].[Cars]
(
[LKey] ASC,
[CUID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CCode] ON [dbo].[Cars]
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
--***********************************************************************
CREATE TABLE [dbo].[RCm](
[E_UID] [int] IDENTITY(1,1) NOT NULL,
[LKey] [int] NOT NULL,
[RFID] [int] NOT NULL,
[CID] [int] NULL,
[CName] [varchar](100) NULL,
[CCmID] [varchar](30) NULL,
[PID] [varchar](255) NULL,
[CmS] [varchar](3) NULL,
[CmID] [varchar](38) NOT NULL,
[VID] [int] NOT NULL DEFAULT ((-1)),
[CmPtAm] [money] NULL,
[CFID] [int] NULL,
[CCode] [char](8) NULL,
CONSTRAINT [PK_RCm] PRIMARY KEY NONCLUSTERED
(
[E_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_E_UID] ON [dbo].[RCm]
(
[Key] ASC,
[E_UID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_RCm_RFID] ON [dbo].[RCm]
(
[RFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CarHist](
[LKey] [int] NOT NULL,
[CarHist_UID] [int] IDENTITY(1,1) NOT NULL,
[CDetFID] [int] NOT NULL CONSTRAINT [DF__CarCharge] DEFAULT (1),
[CFID] [int] NOT NULL,
[CCode] [varchar](8) NOT NULL,
[VFID] [int] NULL,
[CmID] AS (convert(varchar(10),[VisitFID]) + ltrim([ClaimIDSuffix])),
CONSTRAINT [PK_CarHist] PRIMARY KEY NONCLUSTERED
(
[CarHist_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE CLUSTERED INDEX [IDX1_CBH_CDetFID] ON [dbo].[CarHist]
(
[CDetFID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CBH_VFID] ON [dbo].[CarHist]
(
[VFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
Kalen Delaney wrote:
>Hi cbrichards
>Without the code (and probably the DDL) it's impossible to say.
>You are right that there must be SERIALIZABLE isolation in order to get the
>range locks. And if you are already in SERIALIZABLE isolation, adding
>HOLDLOCK would be redundant.
>Sometimes you can avoid deadlock by requesting an X lock on data in a SELECT
>statement, so there is no chance of another process also reading it and
>holding onto the locks, until the first process is done. But again, without
>any more details from you, there's little else to say.
>>I have the following from a DBCC Trace:
>[quoted text clipped - 51 lines]
>> insert upon which the deadlock is occurring and putting a HOLDLOCK on the
>> select statement of the temporary table that populates the user table?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1

DeadLock Trace

We set our Deadlock Trace on with the following command:

DBCC TRACEOn (1204, -1)
DBCC TRACEOFF (3605, -1);
DBCC TRACESTATUS (-1)
GO

In 2000 this worked fine but in 2005 we get the following error:

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:

What do I need to do to fix this so we can see the cause of the deadlock in the SQL Server logs?

The statements above do work on SQL2005 and will output deadlock graphs to the errorlog.

There is a new trace flag in SQL2005 that enhances the information output on deadlock: trace flag 1222.

What log viewer are you using to view the errorlogs? Can you open the errorlog files with notepad instead?

|||Thanks for the information. I use the SQL Server Log viewer in Microsoft SQL Server Management Studio to view the logs.|||

Hi Guys,

Unlike SQL 2000, Trace flag 3605 does not work in SQL Server 2005.

regards

Jag

DeadLock Trace

We set our Deadlock Trace on with the following command:

DBCC TRACEOn (1204, -1)
DBCC TRACEOFF (3605, -1);
DBCC TRACESTATUS (-1)
GO

In 2000 this worked fine but in 2005 we get the following error:

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:

What do I need to do to fix this so we can see the cause of the deadlock in the SQL Server logs?

The statements above do work on SQL2005 and will output deadlock graphs to the errorlog.

There is a new trace flag in SQL2005 that enhances the information output on deadlock: trace flag 1222.

What log viewer are you using to view the errorlogs? Can you open the errorlog files with notepad instead?

|||Thanks for the information. I use the SQL Server Log viewer in Microsoft SQL Server Management Studio to view the logs.|||

Hi Guys,

Unlike SQL 2000, Trace flag 3605 does not work in SQL Server 2005.

regards

Jag

Deadlock searches every 5 seconds

Rumor has it that a great way to wait for potential deadlock is to:
DBCC TRACEOn (3605, 1205, -1)
Ive done this for a while and have been please with the resaults. However I
just noticed in the Error Log that a Deadlock search is being done about
every 5 seconds. This is fine, but Im wondering if theres a way that the
search can write to the Error Log only if theres a Deadlock, not every time
it does a search? Also, if I ever needed to "Truncate" the Error Log , how
would I go about it?The behavior you're seeing is because you've turned on trace flag 1205. If
you turn on trace flag 1204, you'll only get error log output when we find a
deadlock. The overhead of -T1205 is pretty high, and the TF is
undocumented, so I would recommend turning it off and relying on the
documented -T1204 instead.
FYI, the 5 second deadlock search is by design. If we find a deadlock,
though, we will increase the frequency of searches temporarily.
Thanks,
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"ChrisR" <noemail@.bla.com> wrote in message
news:egkCnvUNFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Rumor has it that a great way to wait for potential deadlock is to:
> DBCC TRACEOn (3605, 1205, -1)
> Ive done this for a while and have been please with the resaults. However
> I just noticed in the Error Log that a Deadlock search is being done about
> every 5 seconds. This is fine, but Im wondering if theres a way that the
> search can write to the Error Log only if theres a Deadlock, not every
> time it does a search? Also, if I ever needed to "Truncate" the Error Log
> , how would I go about it?
>|||Perfect! Thanks.
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:uFJpQCVNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> The behavior you're seeing is because you've turned on trace flag 1205.
> If you turn on trace flag 1204, you'll only get error log output when we
> find a deadlock. The overhead of -T1205 is pretty high, and the TF is
> undocumented, so I would recommend turning it off and relying on the
> documented -T1204 instead.
> FYI, the 5 second deadlock search is by design. If we find a deadlock,
> though, we will increase the frequency of searches temporarily.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:egkCnvUNFHA.2748@.TK2MSFTNGP09.phx.gbl...
>> Rumor has it that a great way to wait for potential deadlock is to:
>> DBCC TRACEOn (3605, 1205, -1)
>> Ive done this for a while and have been please with the resaults. However
>> I just noticed in the Error Log that a Deadlock search is being done
>> about every 5 seconds. This is fine, but Im wondering if theres a way
>> that the search can write to the Error Log only if theres a Deadlock, not
>> every time it does a search? Also, if I ever needed to "Truncate" the
>> Error Log , how would I go about it?
>|||To clarify, Traceon is a Server level setting, right?
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:uFJpQCVNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> The behavior you're seeing is because you've turned on trace flag 1205.
> If you turn on trace flag 1204, you'll only get error log output when we
> find a deadlock. The overhead of -T1205 is pretty high, and the TF is
> undocumented, so I would recommend turning it off and relying on the
> documented -T1204 instead.
> FYI, the 5 second deadlock search is by design. If we find a deadlock,
> though, we will increase the frequency of searches temporarily.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:egkCnvUNFHA.2748@.TK2MSFTNGP09.phx.gbl...
>> Rumor has it that a great way to wait for potential deadlock is to:
>> DBCC TRACEOn (3605, 1205, -1)
>> Ive done this for a while and have been please with the resaults. However
>> I just noticed in the Error Log that a Deadlock search is being done
>> about every 5 seconds. This is fine, but Im wondering if theres a way
>> that the search can write to the Error Log only if theres a Deadlock, not
>> every time it does a search? Also, if I ever needed to "Truncate" the
>> Error Log , how would I go about it?
>|||Hi Chris
DBCC TRACEON is normally session level, but adding the -1 makes it take
effect for all sessions.
To start a fresh errorlog, you can run sp_cycle_errorlog.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ChrisR" <noemail@.bla.com> wrote in message
news:uEkDybVNFHA.1392@.TK2MSFTNGP10.phx.gbl...
> To clarify, Traceon is a Server level setting, right?
>
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:uFJpQCVNFHA.1040@.TK2MSFTNGP12.phx.gbl...
>> The behavior you're seeing is because you've turned on trace flag 1205.
>> If you turn on trace flag 1204, you'll only get error log output when we
>> find a deadlock. The overhead of -T1205 is pretty high, and the TF is
>> undocumented, so I would recommend turning it off and relying on the
>> documented -T1204 instead.
>> FYI, the 5 second deadlock search is by design. If we find a deadlock,
>> though, we will increase the frequency of searches temporarily.
>> Thanks,
>> --
>> Ryan Stonecipher
>> Microsoft Sql Server Storage Engine, DBCC
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:egkCnvUNFHA.2748@.TK2MSFTNGP09.phx.gbl...
>> Rumor has it that a great way to wait for potential deadlock is to:
>> DBCC TRACEOn (3605, 1205, -1)
>> Ive done this for a while and have been please with the resaults.
>> However I just noticed in the Error Log that a Deadlock search is being
>> done about every 5 seconds. This is fine, but Im wondering if theres a
>> way that the search can write to the Error Log only if theres a
>> Deadlock, not every time it does a search? Also, if I ever needed to
>> "Truncate" the Error Log , how would I go about it?
>>
>|||All sessions in the DB, or the Server?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23PcnYpZNFHA.3668@.TK2MSFTNGP14.phx.gbl...
> Hi Chris
> DBCC TRACEON is normally session level, but adding the -1 makes it take
> effect for all sessions.
> To start a fresh errorlog, you can run sp_cycle_errorlog.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uEkDybVNFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> To clarify, Traceon is a Server level setting, right?
>>
>> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
>> news:uFJpQCVNFHA.1040@.TK2MSFTNGP12.phx.gbl...
>> The behavior you're seeing is because you've turned on trace flag 1205.
>> If you turn on trace flag 1204, you'll only get error log output when we
>> find a deadlock. The overhead of -T1205 is pretty high, and the TF is
>> undocumented, so I would recommend turning it off and relying on the
>> documented -T1204 instead.
>> FYI, the 5 second deadlock search is by design. If we find a deadlock,
>> though, we will increase the frequency of searches temporarily.
>> Thanks,
>> --
>> Ryan Stonecipher
>> Microsoft Sql Server Storage Engine, DBCC
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:egkCnvUNFHA.2748@.TK2MSFTNGP09.phx.gbl...
>> Rumor has it that a great way to wait for potential deadlock is to:
>> DBCC TRACEOn (3605, 1205, -1)
>> Ive done this for a while and have been please with the resaults.
>> However I just noticed in the Error Log that a Deadlock search is being
>> done about every 5 seconds. This is fine, but Im wondering if theres a
>> way that the search can write to the Error Log only if theres a
>> Deadlock, not every time it does a search? Also, if I ever needed to
>> "Truncate" the Error Log , how would I go about it?
>>
>>
>|||All sessions means all connections to the server.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ChrisR" <noemail@.bla.com> wrote in message
news:uVGZi2hNFHA.2880@.TK2MSFTNGP10.phx.gbl...
> All sessions in the DB, or the Server?
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23PcnYpZNFHA.3668@.TK2MSFTNGP14.phx.gbl...
>> Hi Chris
>> DBCC TRACEON is normally session level, but adding the -1 makes it take
>> effect for all sessions.
>> To start a fresh errorlog, you can run sp_cycle_errorlog.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:uEkDybVNFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> To clarify, Traceon is a Server level setting, right?
>>
>> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
>> news:uFJpQCVNFHA.1040@.TK2MSFTNGP12.phx.gbl...
>> The behavior you're seeing is because you've turned on trace flag 1205.
>> If you turn on trace flag 1204, you'll only get error log output when
>> we find a deadlock. The overhead of -T1205 is pretty high, and the TF
>> is undocumented, so I would recommend turning it off and relying on the
>> documented -T1204 instead.
>> FYI, the 5 second deadlock search is by design. If we find a deadlock,
>> though, we will increase the frequency of searches temporarily.
>> Thanks,
>> --
>> Ryan Stonecipher
>> Microsoft Sql Server Storage Engine, DBCC
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:egkCnvUNFHA.2748@.TK2MSFTNGP09.phx.gbl...
>> Rumor has it that a great way to wait for potential deadlock is to:
>> DBCC TRACEOn (3605, 1205, -1)
>> Ive done this for a while and have been please with the resaults.
>> However I just noticed in the Error Log that a Deadlock search is
>> being done about every 5 seconds. This is fine, but Im wondering if
>> theres a way that the search can write to the Error Log only if theres
>> a Deadlock, not every time it does a search? Also, if I ever needed to
>> "Truncate" the Error Log , how would I go about it?
>>
>>
>>
>

deadlock problem

I have an application that calls many stored procedures. They are
occassionally getting into a deadlock situation.
I have used DBCC TraceOn (-1,1204) to trace the problem and have had
moderate success. However, I have hit a particular deadlock that I just
don't understand and was hoping someone here may be able to help.
The two sps in question are Item_I and Item_DNPK.
The output from DBCC Trace (see below) tells me that SPID 60 (see red text)
has an exclusive (X) lock on the Item table index (KEY: 10:613577224:1 is a
clustered index XPKItem). SPID 60 is currently deadlocked at line 29 of
Item_I. Line 29 is the very first transaction in this sp.
The other client, SPID 56 (see green text) has a Range-Shared-Update lock on
the same resource as SPID 60 and is currently deadlocked at line 60 of
Item_DNPK. Line 60 is the very first meaningful transaction in this sp (not
counting declares and create operations on #temp tables).
The deadlock is, I think, occurring because SPID 60, which has an X lock on
the resource, is waiting for a Range-Insert-Null lock (see maroon text),
while SPID 56 is waiting for a Range-S-U lock on the same resource.
What I don't understand is why there is a deadlock in this case; with one
resource? Also, how can I prevent it, given that the lines in question are
right at the beginning of the transactions in the respective sps?
Any help would be very much appreciated
Adrian
Output of DBCC TraceOn (1204) for the deadlock described above::
2006-02-21 14:25:06.21 spid4
2006-02-21 14:25:06.21 spid4 Node:1
2006-02-21 14:25:06.21 spid4 KEY: 10:613577224:1 (100095e758e0)
CleanCnt:1 Mode: X Flags: 0x0
2006-02-21 14:25:06.21 spid4 Grant List 0::
2006-02-21 14:25:06.21 spid4 Owner:0x42be3fe0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:60 ECID:0
2006-02-21 14:25:06.21 spid4 SPID: 60 ECID: 0 Statement Type: INSERT
Line #: 29
2006-02-21 14:25:06.21 spid4 Input Buf: RPC Event: dbo.Item_I;1
2006-02-21 14:25:06.21 spid4 Requested By:
2006-02-21 14:25:06.21 spid4 ResType:LockOwner Stype:'OR' Mode:
Range-S-U SPID:56 ECID:0 Ec:(0x44111368) Value:0x42bd7920 Cost:(0/0)
2006-02-21 14:25:06.21 spid4
2006-02-21 14:25:06.21 spid4 Node:2
2006-02-21 14:25:06.21 spid4 KEY: 10:613577224:1 (ffffffffffff)
CleanCnt:1 Mode: Range-S-U Flags: 0x0
2006-02-21 14:25:06.21 spid4 Grant List 0::
2006-02-21 14:25:06.21 spid4 Owner:0x42bd85a0 Mode: Range-S-U Flg:0x0
Ref:0 Life:02000000 SPID:56 ECID:0
2006-02-21 14:25:06.21 spid4 SPID: 56 ECID: 0 Statement Type: INSERT
Line #: 60
2006-02-21 14:25:06.21 spid4 Input Buf: RPC Event: dbo.Item_DNPK;1
2006-02-21 14:25:06.21 spid4 Requested By:
2006-02-21 14:25:06.21 spid4 ResType:LockOwner Stype:'OR' Mode:
Range-Insert-Null SPID:60 ECID:0 Ec:(0x4428D368) Value:0x42bd29e0
Cost:(0/214)
2006-02-21 14:25:06.21 spid4 Victim Resource Owner:
2006-02-21 14:25:06.21 spid4 ResType:LockOwner Stype:'OR' Mode:
Range-S-U SPID:56 ECID:0 Ec:(0x44111368)See if this helps
http://sql-server-performance.com/deadlocks.asp
Madhivanan|||Please post the queries.
ML
http://milambda.blogspot.com/|||Here are the two queries involved in the deadlck I am referring to:
Query 1 - sp Item_DNPK (I have left some of this stored proc out just
because it is very long. My deadlocking is happening on the Insert so I
don't think the rest of the sp (after this Insert) is relevant. If you think
it will help, I can post it as well):
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --SERIALIZABLE--
BEGIN TRAN
CREATE TABLE #Temp (MimeElementKey int)
-- primary key has to exist in order to prevent the subsequent deletes
from hanging
CREATE TABLE #Temp2 (ItemKey int not null , ParentKey int,
RecursionDepth int not null,
PRIMARY KEY CLUSTERED
(
[ItemKey],
[RecursionDepth]
))
-- get the dependent items of the initial item being deleted
INSERT INTO #Temp2
SELECT ItemKey, ParentKey, @.RecursionDepth
FROM [dbo].[Item] WITH (UPDLOCK,HOLDLOCK)
WHERE ParentKey = @.aItemKey
END
Query 2: Item_I:
CREATE PROC [dbo].[Item_I]
----
--
-- Schema Version : 2.2 Build 198 Revision 0
-- Date Generated : Mon Feb 20 09:56:52 2006
-- Author : Generated from schema stored procedure template 'SP_I'
-- Description : Generic identity based insert stored procedure for
table 'Item'
-- Returns : New Identity Key if successful or negative error
number
----
--
@.aItemTypeKey int ,
@.aParentKey int ,
@.aItemPartitionKey int ,
@.aOwnerKey int ,
@.aExternalKey sql_variant ,
@.aName nvarchar(256) ,
@.aDescription nvarchar(256) ,
@.aInternal varbinary(100) ,
@.aIsLeaf bit = 0,
@.aIsDeleted bit = 0,
@.aCreatedOn datetime ,
@.aModifiedOn datetime
AS
BEGIN
DECLARE @.Result int
SET NoCount ON
INSERT INTO [dbo].[Item]
(
ItemTypeKey,
ParentKey,
ItemPartitionKey,
OwnerKey,
ExternalKey,
Name,
Description,
Internal,
IsLeaf,
IsDeleted,
CreatedOn,
ModifiedOn
)
VALUES
(
@.aItemTypeKey,
@.aParentKey,
@.aItemPartitionKey,
@.aOwnerKey,
@.aExternalKey,
@.aName,
@.aDescription,
@.aInternal,
@.aIsLeaf,
@.aIsDeleted,
@.aCreatedOn,
@.aModifiedOn
)
IF (@.@.Error = 0)
BEGIN
SELECT @.Result = @.@.Identity
END
ELSE
BEGIN
SELECT @.Result = -@.@.Error
END
RETURN @.Result
END
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D0BAC95E-35B8-47A2-9F51-2BF174E5EF0F@.microsoft.com...
> Please post the queries.
>
> ML
> --
> http://milambda.blogspot.com/|||Comments inline:

> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --SERIALIZABLE--
Repeatable read? Is there another reason for this isolation level? As far as
I see it you could just use defaults here (READ COMMITED).

> BEGIN TRAN
> CREATE TABLE #Temp (MimeElementKey int)
> -- primary key has to exist in order to prevent the subsequent deletes
> from hanging
> CREATE TABLE #Temp2 (ItemKey int not null , ParentKey int,
> RecursionDepth int not null,
> PRIMARY KEY CLUSTERED
> (
> [ItemKey],
> [RecursionDepth]
> ))
> -- get the dependent items of the initial item being deleted
> INSERT INTO #Temp2
> SELECT ItemKey, ParentKey, @.RecursionDepth
> FROM [dbo].[Item] WITH (UPDLOCK,HOLDLOCK)
The HOLDLOCK hint instructs the procedure not to release the lock until it
ends. As I see it you select a set of values here and store them in a local
temporary table, I guess you make a few changes, then update the values
appropriately or what? Could you do these in a single update statement? It
would really help if we could see the rest of the procedure here - especiall
y
the part where the explicit lock is acquired.
Also try adding the ROWLOCK hint.

> WHERE ParentKey = @.aItemKey
> END
The other procedure looks OK to me. It's a simple insert procedure, and has
little or no room for improvement. I, personally, prefer the INSERT...SELECT
syntax to INSERT...VALUES, but I've never heard that choosing either one
would affect locking.
ML
http://milambda.blogspot.com/|||Thanks for the reply.
I tried these two isolation levels (REPEATABLE READ and SERIALIZABLE), but
they don't seem to make a dramatic difference. I'm a little in the dark
ragarding this deadlock, so I must admit that I don't really know whether
READ REPEATABLE is better than READ COMMITTED or not.
The rest of this stored procuder involves several (7 or 8) different
delete/update queries which themselves were involved in earlier deadlocks.
It is for that reason I had started introducing these non-default isolation
levels. Again, though, I am largely driving by the seat of my pants, here;
no explicit reason for having done that.
The reason for the temp table is actually this: my schema is represented as
meta data within several tables and I need to delete a hierarchy of items
defined within this meta schema. To do this, I recurse the sp looking for
children of items at each level. I place the identifiers of all affected
items in the #temp table and then, as I come out of the recursion, I delete
and update various aspects of my schema to delete the children in a clean
and orderly fashion. The semantics of the sp may not make much sense to you
since the rest of the schema is not known to you. IF you have any questions,
though, I will be happy to try and work through them with you.
Thanks for your help in looking into this.
Here is the rest of the sp:
CREATE PROC [dbo].[Item_DNPK]
----
--
-- Schema Version : 2.2 Build 198 Revision 0
-- Date Generated : Mon Feb 20 09:56:52 2006
-- Author : APD
-- Description : Cascade primary key based delete stored procedure for
table 'Item'
-- Recursively calls into itself deleting all children of
the
-- item passed in as the parameter
-- Returns : Zero if successful or negative error number
-- Returns : and List of dependent items that were deleted during
the cascade
----
--
@.aItemKey int,
@.RecursionDepth int = 0
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --SERIALIZABLE--
DECLARE @.IsLocalTran bit
DECLARE @.Result int
-- This procedure is recursive; keep track of the depth of recursion
SELECT @.RecursionDepth = @.RecursionDepth + 1
IF (@.@.TranCount = 0)
BEGIN
SET @.IsLocalTran = 1
BEGIN TRAN
END
ELSE
BEGIN
SET @.IsLocalTran = 0
END
SET @.Result = IsNull(Object_Id('tempdb..#Temp'), 0)
IF ((@.Result > 0) AND (@.RecursionDepth = 1))
BEGIN
DROP TABLE #Temp
END
-- A temporary table to hold the cascaded item Ids for later deletion
SET @.Result = IsNull(Object_Id('tempdb..#Temp2'), 0)
IF ((@.Result > 0) AND (@.RecursionDepth = 1))
BEGIN
DROP TABLE #Temp2
END
-- for the first entry, create holding tables
IF (@.RecursionDepth = 1)
BEGIN
CREATE TABLE #Temp (MimeElementKey int)
-- primary key has to exist in order to prevent the subsequent deletes
from hanging
CREATE TABLE #Temp2 (ItemKey int not null , ParentKey int,
RecursionDepth int not null,
PRIMARY KEY CLUSTERED
(
[ItemKey],
[RecursionDepth]
))
-- get the dependent items of the initial item being deleted
INSERT INTO #Temp2
SELECT ItemKey, ParentKey, @.RecursionDepth
FROM [dbo].[Item] WITH (UPDLOCK,HOLDLOCK)
WHERE ParentKey = @.aItemKey
END
ELSE
BEGIN
-- get the dependent items of the items at the previous recursion level
INSERT INTO #Temp2
SELECT i.ItemKey, i.ParentKey, @.RecursionDepth
FROM [dbo].[Item] i WITH (UPDLOCK,HOLDLOCK)
JOIN #Temp2 t WITH (NOLOCK)
ON i.ParentKey = t.ItemKey
WHERE t.RecursionDepth = @.RecursionDepth-1
END
-- if the "get" of dependent children returned a non-zero number of items,
recurse to get their children
IF (SELECT @.@.ROWCOUNT) > 0
BEGIN
-- get these children to delete as well
EXEC Item_DNPK null, @.RecursionDepth
END
-- delete all items and clean up links as a result of these deletions
INSERT INTO #Temp
SELECT ME.[MimeElementKey]
FROM [dbo].[MimeElement] ME
JOIN [dbo].[Property] P
ON ME.[MimeElementKey] = P.[MimeElementKey]
WHERE P.[ItemKey] in (SELECT ItemKey FROM #Temp2 WITH (NOLOCK) WHERE
RecursionDepth = @.RecursionDepth)
IF (@.@.Error = 0)
BEGIN
DELETE [dbo].[Property]
WHERE [ItemKey] in (SELECT ItemKey FROM #Temp2 WITH (NOLOCK) WHERE
RecursionDepth = @.RecursionDepth)
END
IF (@.@.Error = 0)
BEGIN
DELETE [dbo].[MimeElement]
FROM [dbo].[MimeElement] ME
JOIN #Temp WITH (NOLOCK)
ON ME.[MimeElementKey] = #Temp.[MimeElementKey]
DELETE FROM #Temp
END
IF (@.@.Error = 0)
BEGIN
/* DELETE [dbo].[Link]
FROM [dbo].[Link] l
JOIN #Temp2 t WITH (NOLOCK) ON l.[SourceItemKey] = t.ItemKey
WHERE RecursionDepth = @.RecursionDepth
END
IF (@.@.Error = 0)
BEGIN
DELETE [dbo].[Link]
FROM [dbo].[Link] l
JOIN #Temp2 t WITH (NOLOCK) ON l.[TargetItemKey] = t.ItemKey
WHERE RecursionDepth = @.RecursionDepth */
DELETE [dbo].[Link]
WHERE [SourceItemKey] in (SELECT ItemKey FROM #Temp2 WITH (NOLOCK) WHERE
RecursionDepth = @.RecursionDepth)
OR [TargetItemKey] in (SELECT ItemKey FROM #Temp2 WITH (NOLOCK) WHERE
RecursionDepth = @.RecursionDepth)
END
IF (@.@.Error = 0)
BEGIN
UPDATE [dbo].[Property]
SET [ReferenceTargetItem] = NULL
FROM [dbo].[Property] p
JOIN #Temp2 t WITH (NOLOCK) ON p.[ReferenceTargetItem] = t.ItemKey
WHERE RecursionDepth = @.RecursionDepth
END
IF (@.@.Error = 0)
BEGIN
UPDATE [dbo].[Item]
SET [ParentKey] = NULL
FROM [dbo].[Item] i
JOIN #Temp2 t WITH (NOLOCK) ON i.[ParentKey] = t.ItemKey
WHERE RecursionDepth = @.RecursionDepth
END
IF (@.@.Error = 0)
BEGIN
DELETE [dbo].[Item]
FROM [dbo].[Item] i
JOIN #Temp2 t WITH (NOLOCK)
ON i.ItemKey = t.ItemKey
WHERE t.RecursionDepth = @.RecursionDepth
END
IF (SELECT @.RecursionDepth) = 1
BEGIN
-- delete the primary item passed in as a parameter
IF (@.@.Error = 0)
BEGIN
-- put it in #Temp2 so we can return it in the "cascadedDeletedItems"
resultset
INSERT INTO #Temp2
SELECT i.ItemKey, i.ParentKey, @.RecursionDepth
FROM [dbo].[Item] i -- WITH (NOLOCK)
WHERE i.ItemKey = @.aItemKey
exec Item_D1PK @.aItemKey
END
if (@.IsLocalTran = 1)
BEGIN
IF (@.@.Error = 0)
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END
END
SELECT @.Result = -@.@.Error
-- return the items affected by this query
SELECT distinct ItemKey FROM #Temp2
RETURN @.Result
END
END
GO
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1D228D99-FB06-4FB4-AEC2-24653942A8B4@.microsoft.com...
> Comments inline:
>
> Repeatable read? Is there another reason for this isolation level? As far
> as
> I see it you could just use defaults here (READ COMMITED).
>
> The HOLDLOCK hint instructs the procedure not to release the lock until it
> ends. As I see it you select a set of values here and store them in a
> local
> temporary table, I guess you make a few changes, then update the values
> appropriately or what? Could you do these in a single update statement? It
> would really help if we could see the rest of the procedure here -
> especially
> the part where the explicit lock is acquired.
> Also try adding the ROWLOCK hint.
>
> The other procedure looks OK to me. It's a simple insert procedure, and
> has
> little or no room for improvement. I, personally, prefer the
> INSERT...SELECT
> syntax to INSERT...VALUES, but I've never heard that choosing either one
> would affect locking.
>
> ML
> --
> http://milambda.blogspot.com/|||Before I read through the entire procedure - is there a tree or a hierarchy
involved in these deletes?
This could be optimized, take a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
Deletes require exclusive locks - you should make the delete procedure as
concise as possible - maybe even by building the list outside of a
transaction, and only beginning an explicit transaction just before the
actual delete statement. The delete will block other users anyway, so make i
t
as short as possible.
ML
http://milambda.blogspot.com/|||Yes. Typically an item in the item table has reference to it's parent which
is another item in the item table. The relationship from parent to children
is one to many. A row representing an item in the item table has references
to other tables that define things like properties and property types, etc.
Essentially, then, the hierarchy is to determine all the children of the
item whose id is passed in as a parameter to the sp, and recursively do that
for all those children until the end of the line is reached
I hope this answers your question.
Adrian
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1DECC75A-38CD-40AB-BC63-50FD758231C9@.microsoft.com...
> Before I read through the entire procedure - is there a tree or a
> hierarchy
> involved in these deletes?
> This could be optimized, take a look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
> Deletes require exclusive locks - you should make the delete procedure as
> concise as possible - maybe even by building the list outside of a
> transaction, and only beginning an explicit transaction just before the
> actual delete statement. The delete will block other users anyway, so make
> it
> as short as possible.
>
> ML
> --
> http://milambda.blogspot.com/|||That certainly is one of the function's purposes - to get a list of all
descendants in a hierarchy.
ML
http://milambda.blogspot.com/|||Thanks for the link and suggestion. I'll try putting the select outside of
the transaction and get back to you on my results
Adrian
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1DECC75A-38CD-40AB-BC63-50FD758231C9@.microsoft.com...
> Before I read through the entire procedure - is there a tree or a
> hierarchy
> involved in these deletes?
> This could be optimized, take a look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
> Deletes require exclusive locks - you should make the delete procedure as
> concise as possible - maybe even by building the list outside of a
> transaction, and only beginning an explicit transaction just before the
> actual delete statement. The delete will block other users anyway, so make
> it
> as short as possible.
>
> ML
> --
> http://milambda.blogspot.com/

Deadlock

Hi All,
I need help urgently.
I got problem.
Whenever I issue DBCC SHRINKDATABASE the below message appear.
Server: Msg 1205, Level 13, State 57, Line 1
Transaction (Process ID 65) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
I've tried to find out log transation by issue some command
sp_who,sp_who2,sp_lock,
SELECT spid, blocked, waittime
FROM sysprocesses
WHERE blocked <> 0
AND waittime > 10000
ORDER by waittime DESC
...
Yet It doesn't help me.
I found no deadlock process.
Could help me please?
Thanks, in advace, for helping me..
Regards,
Lux
*** Sent via Developersdex http://www.codecomments.com ***
It's known to happen sometimes. How to address it depends on
a few things. First, are you sure you actually need to
execute a shrink operation? Sometimes people start running
those and they really aren't necessary. Make sure you are
backing up your log as needed. You should check the
following article for more information:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Also, if you really do need to do a shrink, try using dbcc
shrinkfile instead of shrinkdatabase so you have more
control over what files are being shrunk to what size. And
then if you are trying to shrink the data file...you may
want to try executing a DBCC INDEXDEFRAG which could help
when you then run a shrinkfile.
-Sue
On Mon, 22 Aug 2005 02:36:43 -0700, lux sok
<lux.lyny@.gmail.com> wrote:

>Hi All,
>I need help urgently.
>I got problem.
>Whenever I issue DBCC SHRINKDATABASE the below message appear.
>Server: Msg 1205, Level 13, State 57, Line 1
>Transaction (Process ID 65) was deadlocked on lock resources with
>another process and has been chosen as the deadlock victim. Rerun the
>transaction.
>I've tried to find out log transation by issue some command
>sp_who,sp_who2,sp_lock,
>SELECT spid, blocked, waittime
>FROM sysprocesses
>WHERE blocked <> 0
>AND waittime > 10000
>ORDER by waittime DESC
>...
>Yet It doesn't help me.
>I found no deadlock process.
>Could help me please?
>Thanks, in advace, for helping me..
>Regards,
>Lux
>
>*** Sent via Developersdex http://www.codecomments.com ***
|||Thanks, in advance, for helping me.
By the way, If i don't issue command DBCC SHRINKDATABASE/FILE do we have
any way to solve a logical file size grow ?
I mean when I droped/truncated a table the space remain the same
(logical file size).
For example, a table eat up 72GB when i dropped it.
I still can get this space back.
So my hard drive will full.
Any idea or comment please.
Maybe i'm not sure about the behind structure of MS SQLSERVER.
Thanks,
*** Sent via Developersdex http://www.codecomments.com ***
|||That should be a one time thing then right? You would use a
dbcc shrinkfile for that specifying the data file - that's
the file you are concerned about correct? The MDF?
Did you try dbcc shrinkfile?
If you get deadlocks, try the indexdefrag and then the
shrinkfile.
-Sue
On Tue, 23 Aug 2005 01:19:26 -0700, lux sok
<lux.lyny@.gmail.com> wrote:

>Thanks, in advance, for helping me.
>By the way, If i don't issue command DBCC SHRINKDATABASE/FILE do we have
>any way to solve a logical file size grow ?
>I mean when I droped/truncated a table the space remain the same
>(logical file size).
>For example, a table eat up 72GB when i dropped it.
>I still can get this space back.
>So my hard drive will full.
>Any idea or comment please.
>Maybe i'm not sure about the behind structure of MS SQLSERVER.
>Thanks,
>
>*** Sent via Developersdex http://www.codecomments.com ***

Deadlock

Hi All,
I need help urgently.
I got problem.
Whenever I issue DBCC SHRINKDATABASE the below message appear.
Server: Msg 1205, Level 13, State 57, Line 1
Transaction (Process ID 65) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
I've tried to find out log transation by issue some command
sp_who,sp_who2,sp_lock,
SELECT spid, blocked, waittime
FROM sysprocesses
WHERE blocked <> 0
AND waittime > 10000
ORDER by waittime DESC
...
Yet It doesn't help me.
I found no deadlock process.
Could help me please?
Thanks, in advace, for helping me..
Regards,
Lux
*** Sent via Developersdex http://www.codecomments.com ***It's known to happen sometimes. How to address it depends on
a few things. First, are you sure you actually need to
execute a shrink operation? Sometimes people start running
those and they really aren't necessary. Make sure you are
backing up your log as needed. You should check the
following article for more information:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Also, if you really do need to do a shrink, try using dbcc
shrinkfile instead of shrinkdatabase so you have more
control over what files are being shrunk to what size. And
then if you are trying to shrink the data file...you may
want to try executing a DBCC INDEXDEFRAG which could help
when you then run a shrinkfile.
-Sue
On Mon, 22 Aug 2005 02:36:43 -0700, lux sok
<lux.lyny@.gmail.com> wrote:

>Hi All,
>I need help urgently.
>I got problem.
>Whenever I issue DBCC SHRINKDATABASE the below message appear.
>Server: Msg 1205, Level 13, State 57, Line 1
>Transaction (Process ID 65) was deadlocked on lock resources with
>another process and has been chosen as the deadlock victim. Rerun the
>transaction.
>I've tried to find out log transation by issue some command
>sp_who,sp_who2,sp_lock,
>SELECT spid, blocked, waittime
>FROM sysprocesses
>WHERE blocked <> 0
>AND waittime > 10000
>ORDER by waittime DESC
>...
>Yet It doesn't help me.
>I found no deadlock process.
>Could help me please?
>Thanks, in advace, for helping me..
>Regards,
>Lux
>
>*** Sent via Developersdex http://www.codecomments.com ***|||Thanks, in advance, for helping me.
By the way, If i don't issue command DBCC SHRINKDATABASE/FILE do we have
any way to solve a logical file size grow ?
I mean when I droped/truncated a table the space remain the same
(logical file size).
For example, a table eat up 72GB when i dropped it.
I still can get this space back.
So my hard drive will full.
Any idea or comment please.
Maybe i'm not sure about the behind structure of MS SQLSERVER.
Thanks,
*** Sent via Developersdex http://www.codecomments.com ***|||That should be a one time thing then right? You would use a
dbcc shrinkfile for that specifying the data file - that's
the file you are concerned about correct? The MDF?
Did you try dbcc shrinkfile?
If you get deadlocks, try the indexdefrag and then the
shrinkfile.
-Sue
On Tue, 23 Aug 2005 01:19:26 -0700, lux sok
<lux.lyny@.gmail.com> wrote:

>Thanks, in advance, for helping me.
>By the way, If i don't issue command DBCC SHRINKDATABASE/FILE do we have
>any way to solve a logical file size grow ?
>I mean when I droped/truncated a table the space remain the same
>(logical file size).
>For example, a table eat up 72GB when i dropped it.
>I still can get this space back.
>So my hard drive will full.
>Any idea or comment please.
>Maybe i'm not sure about the behind structure of MS SQLSERVER.
>Thanks,
>
>*** Sent via Developersdex http://www.codecomments.com ***

DBREINDEX/INDEXDEFRAG a few questions

I pretty much understand the differences between DBCC DBREINDEX and DBCC INDEXDEFRAG. However, I need the forums help to understand a few specific issues relating to clustered/non-clustered indexes and the advantages/disadvantages of running the DBCC DBREINDEX/INDEXDEFRAG against the table or against each specific index...

"If a table has a clustered index, it's only necessary to re-index the clustered index because any non-clustered indexes on that table will be automatically re-indexed as well."

I think the above statement is true for DBCC DBREINDEX but is the following statement true for DBCC INDEXDEFRAG:-

"If a table has a clustered index, it's only necessary to Index Defrag the clustered index because any non-clustered indexes on that table will be automatically defragged as well."

Following on from the above, is there any advantage with an index maintenance strategy to individually running DBCC DBREINDEX against each specific index as opposed to running it against the table and letting SQL sort out the underlying indexes? Does the same apply to DBCC INDEXDEFRAG?

Regards,

Clive"If a table has a clustered index, it's only necessary to Index Defrag the clustered index because any non-clustered indexes on that table will be automatically defragged as well."

I just did a test of this, and the answer is no, the non-clustered indexes will not be defragged as well. This works out as a slight benefit to indexdefrag, as it saves you the time of rebuilding the non-clustered indexes at the same time as the clustered index.

If you have all non-clustered indexes, then there would be a slight benefit in space-savings by running dbcc dbreindex on each index separately. If there is a clustered index, then running dbreindex on the clustered index rebuilds all of the indexes. This would be a waste of any time spent on each index being rebuilt individually. Does this help?|||Yes, that helps. Thank you. In fact, I just put together a test myself and found the same result. Not unsurprisingly, the DBREINDEX produced 100% scan density on the clustered index and the same or close to it on the non-clustered indexes. However, I was surprised that INDEXDEFRAG on the clustered index actually lowered scan-density by a few percent!

Regards,

Clive|||What was the scan density when you started?

The main problem with indexdefrag is that it is only moving pages from and to page locations that are already allocated to the index being defragmented. It may coalesce some unused space among these allocations, but it does not touch anything that is already allocated to another index page or data page of the table. Since it has to work around all of these prior allocations, you almost never get a nice 100% result from indexdefrag, unless all you had to start with was the clustered index.

DBREINDEX takes database offline?

HI All,
Environment: SQL server2000, SP4, on win2003.
I have few quetions regarding DBCC DBREINDEX..
1. Reindex is offline operation means..takes the db offline?
we have optimzation job through maintenace plan. When it started, will
it takes the database offline by issuing the below command? I heard one DBA
saying this.
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
What is my understanding till now is, it will take the table offline by
issuing xcluve lock.. is that correct?
2. Database is avaiable for use when reondex is happening?
3. When users are already connected, this optimation jobs starts, it will
kill the existing users?If no, if the user is having a lock on table, reindex
is also for the same table, reindex will wait for that user to release the
lock on the same object or it will kill the user?
4. It will allow new users while it is running?
Any one can throw some light on above points.
Thanks,
Suchi
Thanks a lot Tibor..I am clear on REINDEX now..
"Tibor Karaszi" wrote:

> First, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
> (Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
> add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
> DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
> See inline below for comments on your questions:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Suchi" <Suchi@.discussions.microsoft.com> wrote in message
> news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> No, it isn't offline in that sense.
>
>
> Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
> DATABASE would be executed, then there's no way you can access the database and execute the DBCC
> DBREINDEx commands.
>
> Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
> lock (from memory).
>
> Yes, except for the table currently being reindexes and having the lock.
>
> No
>
> Wait. Just as any type lf locking/blocking.
>
> Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
> connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
> same resource.
>
>

DBREINDEX takes database offline?

HI All,
Environment: SQL server2000, SP4, on win2003.
I have few quetions regarding DBCC DBREINDEX..
1. Reindex is offline operation means..takes the db offline?
we have optimzation job through maintenace plan. When it started, will
it takes the database offline by issuing the below command? I heard one DBA
saying this.
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
What is my understanding till now is, it will take the table offline by
issuing xcluve lock.. is that correct?
2. Database is avaiable for use when reondex is happening?
3. When users are already connected, this optimation jobs starts, it will
kill the existing users?If no, if the user is having a lock on table, reindex
is also for the same table, reindex will wait for that user to release the
lock on the same object or it will kill the user?
4. It will allow new users while it is running?
Any one can throw some light on above points.
Thanks,
SuchiFirst, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
(Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
See inline below for comments on your questions:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> HI All,
>
> Environment: SQL server2000, SP4, on win2003.
> I have few quetions regarding DBCC DBREINDEX..
> 1. Reindex is offline operation means..takes the db offline?
No, it isn't offline in that sense.
> we have optimzation job through maintenace plan. When it started, will
> it takes the database offline by issuing the below command? I heard one DBA
> saying this.
> ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
DATABASE would be executed, then there's no way you can access the database and execute the DBCC
DBREINDEx commands.
> What is my understanding till now is, it will take the table offline by
> issuing xcluve lock.. is that correct?
Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
lock (from memory).
> 2. Database is avaiable for use when reondex is happening?
Yes, except for the table currently being reindexes and having the lock.
>
> 3. When users are already connected, this optimation jobs starts, it will
> kill the existing users?
No
> If no, if the user is having a lock on table, reindex
> is also for the same table, reindex will wait for that user to release the
> lock on the same object or it will kill the user?
Wait. Just as any type lf locking/blocking.
>
> 4. It will allow new users while it is running?
Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
same resource.
>
> Any one can throw some light on above points.
>
> Thanks,
> Suchi|||Thanks a lot Tibor..I am clear on REINDEX now..
"Tibor Karaszi" wrote:
> First, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
> (Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
> add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
> DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
> See inline below for comments on your questions:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Suchi" <Suchi@.discussions.microsoft.com> wrote in message
> news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> > HI All,
> >
> >
> > Environment: SQL server2000, SP4, on win2003.
> >
> > I have few quetions regarding DBCC DBREINDEX..
> >
> > 1. Reindex is offline operation means..takes the db offline?
> No, it isn't offline in that sense.
>
> > we have optimzation job through maintenace plan. When it started, will
> > it takes the database offline by issuing the below command? I heard one DBA
> > saying this.
> >
> > ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
> Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
> DATABASE would be executed, then there's no way you can access the database and execute the DBCC
> DBREINDEx commands.
>
> >
> > What is my understanding till now is, it will take the table offline by
> > issuing xcluve lock.. is that correct?
> Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
> lock (from memory).
>
> >
> > 2. Database is avaiable for use when reondex is happening?
> Yes, except for the table currently being reindexes and having the lock.
>
> >
> >
> > 3. When users are already connected, this optimation jobs starts, it will
> > kill the existing users?
> No
>
> > If no, if the user is having a lock on table, reindex
> > is also for the same table, reindex will wait for that user to release the
> > lock on the same object or it will kill the user?
> Wait. Just as any type lf locking/blocking.
>
> >
> >
> > 4. It will allow new users while it is running?
> Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
> connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
> same resource.
>
> >
> >
> > Any one can throw some light on above points.
> >
> >
> > Thanks,
> > Suchi
>