DBCC Logical Scan Bytes/Sec

Hi,
I have a process data acquisition system running with SQL 2000
Enterprise Manager. I have a large hard drive Read activity. By using the
performance monitor, I found that this reading activity is clearly associate
with DBCC logical scan bytes/sec counter and is mostly twin with Physical
Hard drive read/sec.
The point is that I didn't find what trigger this scan. I observed that more
my database is growing more this scan take time. Actually the hard drive is
busy at more that 99% of the time to read and data request take more and
more time.
Is those DBCC scans are essential and how can I identify what is triggering
them?
Regards
MarcDo you have any maintenance tasks scheduled that run a DBCC CHECKDB? Use
profiler to see what commands are being run at that time.
Andrew J. Kelly SQL MVP
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:I4pFe.52350$mv2.811211@.weber.videotron.net...
> Hi,
> I have a process data acquisition system running with SQL 2000
> Enterprise Manager. I have a large hard drive Read activity. By using the
> performance monitor, I found that this reading activity is clearly
> associate with DBCC logical scan bytes/sec counter and is mostly twin with
> Physical Hard drive read/sec.
> The point is that I didn't find what trigger this scan. I observed that
> more my database is growing more this scan take time. Actually the hard
> drive is busy at more that 99% of the time to read and data request take
> more and more time.
> Is those DBCC scans are essential and how can I identify what is
> triggering them?
>
> Regards
> Marc
>|||Hi Mr. Kelly, thanks for the reply.
That was a thing I was suspecting but I didn't know how to trace it. I used
SQL Profiler, like suggested, and I found many querys who use the DBCC
UPDATEUSAGE command. I think this is another disk intensive command any way
I found some with a duration over 250000 ( milliseconds I guess ).
Is there any reason to use this command frequently?
Thanks again for the help. I'll try to reach to conceptors of the DB to see
why they're using this command intensively.
Regards
Marc
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
news: ubKtOedkFHA.2044@.TK2MSFTNGP10.phx.gbl...
> Do you have any maintenance tasks scheduled that run a DBCC CHECKDB? Use
> profiler to see what commands are being run at that time.
> --
> Andrew J. Kelly SQL MVP
>
> "marc quirion" <mquirion@.videotron.ca> wrote in message
> news:I4pFe.52350$mv2.811211@.weber.videotron.net...
>|||That command certainly would do it. There is no real reason to update it so
often. If they need that kind of accurate data that often they should
rethink what they are doing and why.
Andrew J. Kelly SQL MVP
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:8vnGe.8806$nx3.312875@.wagner.videotron.net...
> Hi Mr. Kelly, thanks for the reply.
> That was a thing I was suspecting but I didn't know how to trace it. I
> used
> SQL Profiler, like suggested, and I found many querys who use the DBCC
> UPDATEUSAGE command. I think this is another disk intensive command any
> way
> I found some with a duration over 250000 ( milliseconds I guess ).
> Is there any reason to use this command frequently?
> Thanks again for the help. I'll try to reach to conceptors of the DB to
> see
> why they're using this command intensively.
> Regards
> Marc
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
> news: ubKtOedkFHA.2044@.TK2MSFTNGP10.phx.gbl...
>

DBCC CHECKDB error resolved with dbreindex - safe?

We had a hard drive failure in one of our RAID arrays. We've resolved the
hardware issue; however, DBCC CHECKDB is showing errors in at least some of
our databases. The way we structure our databases is with two data files,
the second containing all of our table indexes. The RAID array that failed
contained these "index-only" data files.
Since DBCC CHECKDB reported that the minimum repair option would be
'REPAIR_ALLOW_DATA_LOSS', I thought I would be restoring each database from
our last full plus differential backups. It's approximately 45 databases,
and I was hoping to avoid this.
Since CHECKDB seems to be reporting an error on only a single table in each
database AND the hardware failure was on the array that contains only
indexes, I thought simply reindexing the table reporting an error might be
an option. So I took one of the databases showing an error and reindexed
the offending table with DBCC DBREINDEX. It completed successfully, so I
reran the DBCC CHECKDB and it reported no errors.
I realize restoring from backups might be the safest route to go, but if I
only reindex a table and the dbcc checkdb comes up clean, am I at risk for
any data loss?
I appreciate your insight on this situation.
Thanks
-joeIf the error was in the index then rebuilding the index will in most cases
fix the problem. If you do a DBCC DBREINDEX you are rebuilding the index
from scratch. So as long as there are no hardware issues to prevent a clean
rebuild you should be OK. The problem comes into play more when it is data
corruption than indexes.
Andrew J. Kelly SQL MVP
"Joe Ross" <joeross7@.hotmail.com> wrote in message
news:O25cjrE7FHA.744@.TK2MSFTNGP10.phx.gbl...
> We had a hard drive failure in one of our RAID arrays. We've resolved the
> hardware issue; however, DBCC CHECKDB is showing errors in at least some
> of our databases. The way we structure our databases is with two data
> files, the second containing all of our table indexes. The RAID array
> that failed contained these "index-only" data files.
> Since DBCC CHECKDB reported that the minimum repair option would be
> 'REPAIR_ALLOW_DATA_LOSS', I thought I would be restoring each database
> from our last full plus differential backups. It's approximately 45
> databases, and I was hoping to avoid this.
> Since CHECKDB seems to be reporting an error on only a single table in
> each database AND the hardware failure was on the array that contains only
> indexes, I thought simply reindexing the table reporting an error might be
> an option. So I took one of the databases showing an error and reindexed
> the offending table with DBCC DBREINDEX. It completed successfully, so I
> reran the DBCC CHECKDB and it reported no errors.
> I realize restoring from backups might be the safest route to go, but if I
> only reindex a table and the dbcc checkdb comes up clean, am I at risk for
> any data loss?
> I appreciate your insight on this situation.
> Thanks
> -joe
>|||Thank you. This is how I understood it, but it's reassuring to get
confirmation from an expert.
Much appreciated,
-joe
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ej%23s64F7FHA.2484@.TK2MSFTNGP10.phx.gbl...
> If the error was in the index then rebuilding the index will in most cases
> fix the problem. If you do a DBCC DBREINDEX you are rebuilding the index
> from scratch. So as long as there are no hardware issues to prevent a
> clean rebuild you should be OK. The problem comes into play more when it
> is data corruption than indexes.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe Ross" <joeross7@.hotmail.com> wrote in message
> news:O25cjrE7FHA.744@.TK2MSFTNGP10.phx.gbl...
>|||Andrew, There's another SQL 2005 launch event December 19th in Waltham if
you'd care to post it on NESQL.
Please email me if you'd like more details.
--
burt_king@.yahoo.com
"Joe Ross" wrote:

> Thank you. This is how I understood it, but it's reassuring to get
> confirmation from an expert.
> Much appreciated,
> -joe
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ej%23s64F7FHA.2484@.TK2MSFTNGP10.phx.gbl...
>
>

DBCC CHECKDB error resolved with dbreindex - safe?

We had a hard drive failure in one of our RAID arrays. We've resolved the
hardware issue; however, DBCC CHECKDB is showing errors in at least some of
our databases. The way we structure our databases is with two data files,
the second containing all of our table indexes. The RAID array that failed
contained these "index-only" data files.
Since DBCC CHECKDB reported that the minimum repair option would be
'REPAIR_ALLOW_DATA_LOSS', I thought I would be restoring each database from
our last full plus differential backups. It's approximately 45 databases,
and I was hoping to avoid this.
Since CHECKDB seems to be reporting an error on only a single table in each
database AND the hardware failure was on the array that contains only
indexes, I thought simply reindexing the table reporting an error might be
an option. So I took one of the databases showing an error and reindexed
the offending table with DBCC DBREINDEX. It completed successfully, so I
reran the DBCC CHECKDB and it reported no errors.
I realize restoring from backups might be the safest route to go, but if I
only reindex a table and the dbcc checkdb comes up clean, am I at risk for
any data loss?
I appreciate your insight on this situation.
Thanks
-joeIf the error was in the index then rebuilding the index will in most cases
fix the problem. If you do a DBCC DBREINDEX you are rebuilding the index
from scratch. So as long as there are no hardware issues to prevent a clean
rebuild you should be OK. The problem comes into play more when it is data
corruption than indexes.
--
Andrew J. Kelly SQL MVP
"Joe Ross" <joeross7@.hotmail.com> wrote in message
news:O25cjrE7FHA.744@.TK2MSFTNGP10.phx.gbl...
> We had a hard drive failure in one of our RAID arrays. We've resolved the
> hardware issue; however, DBCC CHECKDB is showing errors in at least some
> of our databases. The way we structure our databases is with two data
> files, the second containing all of our table indexes. The RAID array
> that failed contained these "index-only" data files.
> Since DBCC CHECKDB reported that the minimum repair option would be
> 'REPAIR_ALLOW_DATA_LOSS', I thought I would be restoring each database
> from our last full plus differential backups. It's approximately 45
> databases, and I was hoping to avoid this.
> Since CHECKDB seems to be reporting an error on only a single table in
> each database AND the hardware failure was on the array that contains only
> indexes, I thought simply reindexing the table reporting an error might be
> an option. So I took one of the databases showing an error and reindexed
> the offending table with DBCC DBREINDEX. It completed successfully, so I
> reran the DBCC CHECKDB and it reported no errors.
> I realize restoring from backups might be the safest route to go, but if I
> only reindex a table and the dbcc checkdb comes up clean, am I at risk for
> any data loss?
> I appreciate your insight on this situation.
> Thanks
> -joe
>|||Thank you. This is how I understood it, but it's reassuring to get
confirmation from an expert.
Much appreciated,
-joe
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ej%23s64F7FHA.2484@.TK2MSFTNGP10.phx.gbl...
> If the error was in the index then rebuilding the index will in most cases
> fix the problem. If you do a DBCC DBREINDEX you are rebuilding the index
> from scratch. So as long as there are no hardware issues to prevent a
> clean rebuild you should be OK. The problem comes into play more when it
> is data corruption than indexes.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe Ross" <joeross7@.hotmail.com> wrote in message
> news:O25cjrE7FHA.744@.TK2MSFTNGP10.phx.gbl...
>> We had a hard drive failure in one of our RAID arrays. We've resolved
>> the hardware issue; however, DBCC CHECKDB is showing errors in at least
>> some of our databases. The way we structure our databases is with two
>> data files, the second containing all of our table indexes. The RAID
>> array that failed contained these "index-only" data files.
>> Since DBCC CHECKDB reported that the minimum repair option would be
>> 'REPAIR_ALLOW_DATA_LOSS', I thought I would be restoring each database
>> from our last full plus differential backups. It's approximately 45
>> databases, and I was hoping to avoid this.
>> Since CHECKDB seems to be reporting an error on only a single table in
>> each database AND the hardware failure was on the array that contains
>> only indexes, I thought simply reindexing the table reporting an error
>> might be an option. So I took one of the databases showing an error and
>> reindexed the offending table with DBCC DBREINDEX. It completed
>> successfully, so I reran the DBCC CHECKDB and it reported no errors.
>> I realize restoring from backups might be the safest route to go, but if
>> I only reindex a table and the dbcc checkdb comes up clean, am I at risk
>> for any data loss?
>> I appreciate your insight on this situation.
>> Thanks
>> -joe
>|||Andrew, There's another SQL 2005 launch event December 19th in Waltham if
you'd care to post it on NESQL.
Please email me if you'd like more details.
--
burt_king@.yahoo.com
"Joe Ross" wrote:
> Thank you. This is how I understood it, but it's reassuring to get
> confirmation from an expert.
> Much appreciated,
> -joe
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ej%23s64F7FHA.2484@.TK2MSFTNGP10.phx.gbl...
> > If the error was in the index then rebuilding the index will in most cases
> > fix the problem. If you do a DBCC DBREINDEX you are rebuilding the index
> > from scratch. So as long as there are no hardware issues to prevent a
> > clean rebuild you should be OK. The problem comes into play more when it
> > is data corruption than indexes.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Joe Ross" <joeross7@.hotmail.com> wrote in message
> > news:O25cjrE7FHA.744@.TK2MSFTNGP10.phx.gbl...
> >> We had a hard drive failure in one of our RAID arrays. We've resolved
> >> the hardware issue; however, DBCC CHECKDB is showing errors in at least
> >> some of our databases. The way we structure our databases is with two
> >> data files, the second containing all of our table indexes. The RAID
> >> array that failed contained these "index-only" data files.
> >>
> >> Since DBCC CHECKDB reported that the minimum repair option would be
> >> 'REPAIR_ALLOW_DATA_LOSS', I thought I would be restoring each database
> >> from our last full plus differential backups. It's approximately 45
> >> databases, and I was hoping to avoid this.
> >>
> >> Since CHECKDB seems to be reporting an error on only a single table in
> >> each database AND the hardware failure was on the array that contains
> >> only indexes, I thought simply reindexing the table reporting an error
> >> might be an option. So I took one of the databases showing an error and
> >> reindexed the offending table with DBCC DBREINDEX. It completed
> >> successfully, so I reran the DBCC CHECKDB and it reported no errors.
> >>
> >> I realize restoring from backups might be the safest route to go, but if
> >> I only reindex a table and the dbcc checkdb comes up clean, am I at risk
> >> for any data loss?
> >>
> >> I appreciate your insight on this situation.
> >>
> >> Thanks
> >> -joe
> >>
> >
> >
>
>