Deadlocks and trace 1204

We had a deadlock the other day and would like to identify the offending
process. Is it too late if there were no traces?
If we set trace 1204 to capture future deadlocks, will it write to the sql
error log? Against which database do you run the DBCC trace (master or the
user database)?
Thanks.
RonHi Ron
run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadlock
graph reports in the sql error log when deadlocks occur.
If you have any trouble interpreting them, post the output here & I'm sure
you'll get some help..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
> We had a deadlock the other day and would like to identify the offending
> process. Is it too late if there were no traces?
> If we set trace 1204 to capture future deadlocks, will it write to the sql
> error log? Against which database do you run the DBCC trace (master or
> the
> user database)?
> Thanks.
> Ron|||Hi Greg, thanks for the info. Here's what's getting written to the error
log. I don't find it all that helpful. Do I need something else turned on?
Is there a way to convert the SPIDs to users or logins?
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec0x79A63A30)
Value:0x701
2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
ECID:0 Ec0x0E589528) Value:0x781
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
usp_JC_PrintCycle_UpdatePrintedClaims;1
2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
#: 14
2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
Life:00000001 SPID:57 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 3::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Mode: U Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:2
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
ECID:0 Ec0x79A63A30) Value:0x701
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
#: 1
2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
Life:02000000 SPID:63 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 1::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1
Mode: X Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:1
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 Wait-for graph
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ...
Thanks
Ron
"Greg Linwood" wrote:

> Hi Ron
> run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadloc
k
> graph reports in the sql error log when deadlocks occur.
> If you have any trouble interpreting them, post the output here & I'm sure
> you'll get some help..
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
>
>|||Hi Ron
Firstly, it's better to read these by opening up the error log with a text
editor in the file system than via the Enterprise Manager as the Enterprise
Manager reverses the order of display. Here's what the output should look
like:
Node:1
KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1 Mode: X Flags: 0x0
Grant List 1::
Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
SPID: 63 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec0x79A63A30)
Value:0x701
Node:2
Mode: U Flags: 0x0
KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Grant List 3::
Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:57 ECID:0
SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 14
Input Buf: RPC Event: usp_JC_PrintCycle_UpdatePrintedClaims;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec0x0E589528)
Value:0x781
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec0x79A63A30)
Value:0x701
Note that there are 2 "nodes". Each node represents a resource being locked
& includes information about which connection was "granted" a lock & which
connection has "requested" a lock on the same resource. In this case, Node 1
is an index key lock (ie, an index b-tree page) from database 11, objectid
251199995 & index 27. Node 2 is also a lock on an index key from the same
index. To work out what these numbers represent, you can use the following
queries:
select name from master..sysdatabases where dbid = 11 --gives the database
name
--from within that database
select name from sysobjects where id = 251199995 --gives the table name
select name from sysindexes where id = 251199995 and indid = 27 --gives the
index name
Now you should have the index which is being locked. You can also see the
commands which have acquired the locks from each Node's 'Input Buf' section.
From this, you will see the commands which are taking the respective Node
locks & from here, you probably need to look at each update statement &
determine whether good indexes exist for the filter predicates of the
queries as it often happens with deadlock resolution that the updates are
locking more rows than they need to complete their work etc..
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E8F6BF4-8664-4C82-9907-D9A54CEF5B0B@.microsoft.com...[vbcol=seagreen]
> Hi Greg, thanks for the info. Here's what's getting written to the error
> log. I don't find it all that helpful. Do I need something else turned
> on?
> Is there a way to convert the SPIDs to users or logins?
> ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec0x79A63A30)
> Value:0x701
> 2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
> ECID:0 Ec0x0E589528) Value:0x781
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
> usp_JC_PrintCycle_UpdatePrintedClaims;1
> 2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
> #: 14
> 2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
> Life:00000001 SPID:57 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 3::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3)
> CleanCnt:1
> Mode: U Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:2
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
> ECID:0 Ec0x79A63A30) Value:0x701
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
> VooDoo.dbo.tblClaims_Processing_Professional
> 2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
> #: 1
> 2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
> Life:02000000 SPID:63 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 1::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c)
> CleanCnt:1
> Mode: X Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:1
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 Wait-for graph
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ...
> Thanks
> Ron
> "Greg Linwood" wrote:
>

Deadlocks and trace 1204

We had a deadlock the other day and would like to identify the offending
process. Is it too late if there were no traces?
If we set trace 1204 to capture future deadlocks, will it write to the sql
error log? Against which database do you run the DBCC trace (master or the
user database)?
Thanks.
RonHi Ron
run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadlock
graph reports in the sql error log when deadlocks occur.
If you have any trouble interpreting them, post the output here & I'm sure
you'll get some help..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
> We had a deadlock the other day and would like to identify the offending
> process. Is it too late if there were no traces?
> If we set trace 1204 to capture future deadlocks, will it write to the sql
> error log? Against which database do you run the DBCC trace (master or
> the
> user database)?
> Thanks.
> Ron|||Hi Greg, thanks for the info. Here's what's getting written to the error
log. I don't find it all that helpful. Do I need something else turned on?
Is there a way to convert the SPIDs to users or logins?
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
Value:0x701
2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
ECID:0 Ec:(0x0E589528) Value:0x781
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
usp_JC_PrintCycle_UpdatePrintedClaims;1
2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
#: 14
2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
Life:00000001 SPID:57 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 3::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Mode: U Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:2
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
ECID:0 Ec:(0x79A63A30) Value:0x701
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
#: 1
2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
Life:02000000 SPID:63 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 1::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1
Mode: X Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:1
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 Wait-for graph
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ...
Thanks
Ron
"Greg Linwood" wrote:
> Hi Ron
> run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadlock
> graph reports in the sql error log when deadlocks occur.
> If you have any trouble interpreting them, post the output here & I'm sure
> you'll get some help..
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
> > We had a deadlock the other day and would like to identify the offending
> > process. Is it too late if there were no traces?
> >
> > If we set trace 1204 to capture future deadlocks, will it write to the sql
> > error log? Against which database do you run the DBCC trace (master or
> > the
> > user database)?
> >
> > Thanks.
> >
> > Ron
>
>|||Hi Ron
Firstly, it's better to read these by opening up the error log with a text
editor in the file system than via the Enterprise Manager as the Enterprise
Manager reverses the order of display. Here's what the output should look
like:
Node:1
KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1 Mode: X Flags: 0x0
Grant List 1::
Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
SPID: 63 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
Value:0x701
Node:2
Mode: U Flags: 0x0
KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Grant List 3::
Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:57 ECID:0
SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 14
Input Buf: RPC Event: usp_JC_PrintCycle_UpdatePrintedClaims;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec:(0x0E589528)
Value:0x781
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
Value:0x701
Note that there are 2 "nodes". Each node represents a resource being locked
& includes information about which connection was "granted" a lock & which
connection has "requested" a lock on the same resource. In this case, Node 1
is an index key lock (ie, an index b-tree page) from database 11, objectid
251199995 & index 27. Node 2 is also a lock on an index key from the same
index. To work out what these numbers represent, you can use the following
queries:
select name from master..sysdatabases where dbid = 11 --gives the database
name
--from within that database
select name from sysobjects where id = 251199995 --gives the table name
select name from sysindexes where id = 251199995 and indid = 27 --gives the
index name
Now you should have the index which is being locked. You can also see the
commands which have acquired the locks from each Node's 'Input Buf' section.
From this, you will see the commands which are taking the respective Node
locks & from here, you probably need to look at each update statement &
determine whether good indexes exist for the filter predicates of the
queries as it often happens with deadlock resolution that the updates are
locking more rows than they need to complete their work etc..
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E8F6BF4-8664-4C82-9907-D9A54CEF5B0B@.microsoft.com...
> Hi Greg, thanks for the info. Here's what's getting written to the error
> log. I don't find it all that helpful. Do I need something else turned
> on?
> Is there a way to convert the SPIDs to users or logins?
> ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
> Value:0x701
> 2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
> ECID:0 Ec:(0x0E589528) Value:0x781
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
> usp_JC_PrintCycle_UpdatePrintedClaims;1
> 2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
> #: 14
> 2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
> Life:00000001 SPID:57 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 3::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3)
> CleanCnt:1
> Mode: U Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:2
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
> ECID:0 Ec:(0x79A63A30) Value:0x701
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
> VooDoo.dbo.tblClaims_Processing_Professional
> 2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
> #: 1
> 2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
> Life:02000000 SPID:63 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 1::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c)
> CleanCnt:1
> Mode: X Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:1
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 Wait-for graph
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ...
> Thanks
> Ron
> "Greg Linwood" wrote:
>> Hi Ron
>> run dbcc traceon (1204, 3605, -1) in any database. You'll then get
>> deadlock
>> graph reports in the sql error log when deadlocks occur.
>> If you have any trouble interpreting them, post the output here & I'm
>> sure
>> you'll get some help..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
>> > We had a deadlock the other day and would like to identify the
>> > offending
>> > process. Is it too late if there were no traces?
>> >
>> > If we set trace 1204 to capture future deadlocks, will it write to the
>> > sql
>> > error log? Against which database do you run the DBCC trace (master or
>> > the
>> > user database)?
>> >
>> > Thanks.
>> >
>> > Ron
>>

dbreindex causes fragmentation in other indexes

We have a report that uses dbcc showcontig to identify indexes with
fragmentation. It shows indexes that have a scan density under 85% or extent
fragmentation over 15%. I run dbcc dbreindex against the indexes identified
in the report to rebuild the indexes. When I run the report again a
completely different index shows up. During this time no other users or
processes are running against the database. Any ideas what may be causing
this?
--
OdellHi
You may want to build all indexes for the given table rather than specific
index, especially if the index is a clustered.
John
"Odell Edwards" wrote:
> We have a report that uses dbcc showcontig to identify indexes with
> fragmentation. It shows indexes that have a scan density under 85% or extent
> fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> in the report to rebuild the indexes. When I run the report again a
> completely different index shows up. During this time no other users or
> processes are running against the database. Any ideas what may be causing
> this?
> --
> Odell|||Thanks for the post. We tried rebuilding all the indexes. It took several
hours but it didn't clean up the fragmentation.
--
Odell
"John Bell" wrote:
> Hi
> You may want to build all indexes for the given table rather than specific
> index, especially if the index is a clustered.
> John
> "Odell Edwards" wrote:
> > We have a report that uses dbcc showcontig to identify indexes with
> > fragmentation. It shows indexes that have a scan density under 85% or extent
> > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > in the report to rebuild the indexes. When I run the report again a
> > completely different index shows up. During this time no other users or
> > processes are running against the database. Any ideas what may be causing
> > this?
> > --
> > Odell|||Hi
Did you specify the indexes individually or just the table?
John
"Odell Edwards" wrote:
> Thanks for the post. We tried rebuilding all the indexes. It took several
> hours but it didn't clean up the fragmentation.
> --
> Odell
>
> "John Bell" wrote:
> > Hi
> >
> > You may want to build all indexes for the given table rather than specific
> > index, especially if the index is a clustered.
> >
> > John
> >
> > "Odell Edwards" wrote:
> >
> > > We have a report that uses dbcc showcontig to identify indexes with
> > > fragmentation. It shows indexes that have a scan density under 85% or extent
> > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > > in the report to rebuild the indexes. When I run the report again a
> > > completely different index shows up. During this time no other users or
> > > processes are running against the database. Any ideas what may be causing
> > > this?
> > > --
> > > Odell|||We sepcified the table, not the individual indexes.
--
Odell
"John Bell" wrote:
> Hi
> Did you specify the indexes individually or just the table?
> John
> "Odell Edwards" wrote:
> > Thanks for the post. We tried rebuilding all the indexes. It took several
> > hours but it didn't clean up the fragmentation.
> > --
> > Odell
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > You may want to build all indexes for the given table rather than specific
> > > index, especially if the index is a clustered.
> > >
> > > John
> > >
> > > "Odell Edwards" wrote:
> > >
> > > > We have a report that uses dbcc showcontig to identify indexes with
> > > > fragmentation. It shows indexes that have a scan density under 85% or extent
> > > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > > > in the report to rebuild the indexes. When I run the report again a
> > > > completely different index shows up. During this time no other users or
> > > > processes are running against the database. Any ideas what may be causing
> > > > this?
> > > > --
> > > > Odell|||This is the format we used
dbcc dbreindex (<tablename>, '',0)
Thanks,
--
Odell
"Odell Edwards" wrote:
> We sepcified the table, not the individual indexes.
> --
> Odell
>
> "John Bell" wrote:
> > Hi
> >
> > Did you specify the indexes individually or just the table?
> >
> > John
> >
> > "Odell Edwards" wrote:
> >
> > > Thanks for the post. We tried rebuilding all the indexes. It took several
> > > hours but it didn't clean up the fragmentation.
> > > --
> > > Odell
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > You may want to build all indexes for the given table rather than specific
> > > > index, especially if the index is a clustered.
> > > >
> > > > John
> > > >
> > > > "Odell Edwards" wrote:
> > > >
> > > > > We have a report that uses dbcc showcontig to identify indexes with
> > > > > fragmentation. It shows indexes that have a scan density under 85% or extent
> > > > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > > > > in the report to rebuild the indexes. When I run the report again a
> > > > > completely different index shows up. During this time no other users or
> > > > > processes are running against the database. Any ideas what may be causing
> > > > > this?
> > > > > --
> > > > > Odell|||Scan density is meaningless if you have several database files (search the archives). And there's
little you can do about extent scan fragmentation (I tend to ignore it). Look at Logical
fragmentation...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>> We sepcified the table, not the individual indexes.
>> --
>> Odell
>>
>> "John Bell" wrote:
>> > Hi
>> >
>> > Did you specify the indexes individually or just the table?
>> >
>> > John
>> >
>> > "Odell Edwards" wrote:
>> >
>> > > Thanks for the post. We tried rebuilding all the indexes. It took several
>> > > hours but it didn't clean up the fragmentation.
>> > > --
>> > > Odell
>> > >
>> > >
>> > > "John Bell" wrote:
>> > >
>> > > > Hi
>> > > >
>> > > > You may want to build all indexes for the given table rather than specific
>> > > > index, especially if the index is a clustered.
>> > > >
>> > > > John
>> > > >
>> > > > "Odell Edwards" wrote:
>> > > >
>> > > > > We have a report that uses dbcc showcontig to identify indexes with
>> > > > > fragmentation. It shows indexes that have a scan density under 85% or extent
>> > > > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
>> > > > > in the report to rebuild the indexes. When I run the report again a
>> > > > > completely different index shows up. During this time no other users or
>> > > > > processes are running against the database. Any ideas what may be causing
>> > > > > this?
>> > > > > --
>> > > > > Odell|||Is this a clustered index or a HEAP? If it is a HEAP then you can reindex
all you want and nothing will happen to reduce fragmentation. Can you post
the results of DBCC SHOWCONTIG?
--
Andrew J. Kelly SQL MVP
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>> We sepcified the table, not the individual indexes.
>> --
>> Odell
>>
>> "John Bell" wrote:
>> > Hi
>> >
>> > Did you specify the indexes individually or just the table?
>> >
>> > John
>> >
>> > "Odell Edwards" wrote:
>> >
>> > > Thanks for the post. We tried rebuilding all the indexes. It took
>> > > several
>> > > hours but it didn't clean up the fragmentation.
>> > > --
>> > > Odell
>> > >
>> > >
>> > > "John Bell" wrote:
>> > >
>> > > > Hi
>> > > >
>> > > > You may want to build all indexes for the given table rather than
>> > > > specific
>> > > > index, especially if the index is a clustered.
>> > > >
>> > > > John
>> > > >
>> > > > "Odell Edwards" wrote:
>> > > >
>> > > > > We have a report that uses dbcc showcontig to identify indexes
>> > > > > with
>> > > > > fragmentation. It shows indexes that have a scan density under
>> > > > > 85% or extent
>> > > > > fragmentation over 15%. I run dbcc dbreindex against the indexes
>> > > > > identified
>> > > > > in the report to rebuild the indexes. When I run the report
>> > > > > again a
>> > > > > completely different index shows up. During this time no other
>> > > > > users or
>> > > > > processes are running against the database. Any ideas what may
>> > > > > be causing
>> > > > > this?
>> > > > > --
>> > > > > Odell

dbreindex causes fragmentation in other indexes

We have a report that uses dbcc showcontig to identify indexes with
fragmentation. It shows indexes that have a scan density under 85% or exten
t
fragmentation over 15%. I run dbcc dbreindex against the indexes identified
in the report to rebuild the indexes. When I run the report again a
completely different index shows up. During this time no other users or
processes are running against the database. Any ideas what may be causing
this?
--
OdellHi
You may want to build all indexes for the given table rather than specific
index, especially if the index is a clustered.
John
"Odell Edwards" wrote:

> We have a report that uses dbcc showcontig to identify indexes with
> fragmentation. It shows indexes that have a scan density under 85% or ext
ent
> fragmentation over 15%. I run dbcc dbreindex against the indexes identifi
ed
> in the report to rebuild the indexes. When I run the report again a
> completely different index shows up. During this time no other users or
> processes are running against the database. Any ideas what may be causing
> this?
> --
> Odell|||Thanks for the post. We tried rebuilding all the indexes. It took several
hours but it didn't clean up the fragmentation.
--
Odell
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may want to build all indexes for the given table rather than specific
> index, especially if the index is a clustered.
> John
> "Odell Edwards" wrote:
>|||Hi
Did you specify the indexes individually or just the table?
John
"Odell Edwards" wrote:
[vbcol=seagreen]
> Thanks for the post. We tried rebuilding all the indexes. It took several
> hours but it didn't clean up the fragmentation.
> --
> Odell
>
> "John Bell" wrote:
>|||We sepcified the table, not the individual indexes.
--
Odell
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Did you specify the indexes individually or just the table?
> John
> "Odell Edwards" wrote:
>|||This is the format we used
dbcc dbreindex (<tablename>, '',0)
Thanks,
--
Odell
"Odell Edwards" wrote:
[vbcol=seagreen]
> We sepcified the table, not the individual indexes.
> --
> Odell
>
> "John Bell" wrote:
>|||Scan density is meaningless if you have several database files (search the a
rchives). And there's
little you can do about extent scan fragmentation (I tend to ignore it). Loo
k at Logical
fragmentation...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...[vbcol=seagreen]
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>|||Is this a clustered index or a HEAP? If it is a HEAP then you can reindex
all you want and nothing will happen to reduce fragmentation. Can you post
the results of DBCC SHOWCONTIG?
Andrew J. Kelly SQL MVP
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...[vbcol=seagreen]
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>