Dbcc checkdb for highly OLTP system

Is it advisable to run DBCC checkdb for all databases daily on a highly OLTP
system ?
Or is it better to do it once a week or once a month..?
What about dbcc dbreindex ? When does one do that on a highly transactional
system..
Using SQL 2000It won't hurt to run DBCC CHECKDB and DBCC DBREINDEX (or DBCC INDEXDEFRAG)
as often as possible. How often you can run it is usually limited by the
maintenance window you have. If you have time to run them every night, run
them every night. You should run them at least once a week.
DBCC DBREINDEX takes out table locks, so you can't really use that in a 24/7
environment, but you can run DBCC CHECKDB and DBCC INDEXDEFRAG during
periods with low activity.
In both cases (overnight maintenance windows and 24/7) you can choose to run
DBCC INDEXDEFRAG and DBCC CHECKTABLE (instead of DBCC CHECKDB) on a limited
number of different tables only each day, so that you won't exceed your
maintenance window. Be warned though that DBCC CHECKDB can take a lot longer
to complete when it actually encounters problems. I saw the execution time
on one of my servers go from about 25 minutes to about 2.5 hours when an
index got corrupted.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23XG5hlOeDHA.1700@.TK2MSFTNGP10.phx.gbl...
> Is it advisable to run DBCC checkdb for all databases daily on a highly
OLTP
> system ?
> Or is it better to do it once a week or once a month..?
> What about dbcc dbreindex ? When does one do that on a highly
transactional
> system..
> Using SQL 2000
>
>|||IN addition to Jacob's comments you can also restore a copy of the db on
another server and run DBCC CHECKDB there. That way you don't have to
bother the users at all and it will still tell you if you have corruption or
not.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23XG5hlOeDHA.1700@.TK2MSFTNGP10.phx.gbl...
> Is it advisable to run DBCC checkdb for all databases daily on a highly
OLTP
> system ?
> Or is it better to do it once a week or once a month..?
> What about dbcc dbreindex ? When does one do that on a highly
transactional
> system..
> Using SQL 2000
>
>|||Sorry Jacco, darn spell checkers<g>.
Andrew J. Kelly
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OCWiAjSeDHA.1716@.TK2MSFTNGP10.phx.gbl...
> IN addition to Jacob's comments you can also restore a copy of the db on
> another server and run DBCC CHECKDB there. That way you don't have to
> bother the users at all and it will still tell you if you have corruption
or
> not.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23XG5hlOeDHA.1700@.TK2MSFTNGP10.phx.gbl...
> > Is it advisable to run DBCC checkdb for all databases daily on a highly
> OLTP
> > system ?
> > Or is it better to do it once a week or once a month..?
> >
> > What about dbcc dbreindex ? When does one do that on a highly
> transactional
> > system..
> >
> > Using SQL 2000
> >
> >
> >
>|||Re: reindex/indexdefrag - check out the white paper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which not only helps you work out which command will work best for you, it
also tells you how to determine if you even need to bother spending the time
running eitehr command. Just because an index is fragmented, it doesn't
necessarily follow that running either command will have any effect on the
performance of your workload.
Re: checkdb - if you're going to run it nightly, I advise you to use the
WITH PHYSICAL_ONLY option. This will audit every page, perform an allocation
consistency check, and structural checks of B-trees. You should not run with
any of the repair options by default.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#XG5hlOeDHA.1700@.TK2MSFTNGP10.phx.gbl...
> Is it advisable to run DBCC checkdb for all databases daily on a highly
OLTP
> system ?
> Or is it better to do it once a week or once a month..?
> What about dbcc dbreindex ? When does one do that on a highly
transactional
> system..
> Using SQL 2000
>
>