DBCC Checkdb WITH PHYSICAL_ONLY verses DBCC CHECKALLOC

Can someone explain what the differences are between the DBCC
CHECKDB(XXXX) WITH PHYSICAL_ONLY verses the DBCC CHECKALLOC()?
If you could run one of these, which one and why?
Our production SAP database is too large and the impact of the DBCC is
too great on a daily basis. So, we are going to run a full CHECKDB
only weekly. However, for "comfort zone", we would at least like to
verify the database structure daily during night hours.
They both appear to be similar by reading the books.
thanks
SteveHi Steve,
DBCC CHECKALLOC checks allocation and page usage in a database, including
indexed views. The NOINDEX option, used only for backward compatibility,
also applies to indexed views.
DBCC CHECKDB performs a physical consistency check on indexed views. The
NOINDEX option, used only for backward compatibility, also applies to any
secondary indexes on indexed views.
CheckdB PHYSICAL_ONLY limits the checking to the integrity of the physical
structure of the page and record headers, and to the consistency between
the pages' object ID and index ID and the allocation structures. Designed
to provide a low overhead check of the physical consistency of the
database, this check also detects torn pages and common hardware failures
that can compromise a user's data.
It is not necessary to execute DBCC CHECKALLOC if DBCC CHECKDB has already
been executed. DBCC CHECKDB is a superset of DBCC CHECKALLOC and includes
allocation checks in addition to checks of index structure and data
integrity.
DBCC CHECKDB is the safest repair statement because it identifies and
repairs the widest possible range of errors. If only allocation errors are
reported for a database, execute DBCC CHECKALLOC with a repair option to
correct them. However, to ensure that all errors (including allocation
errors) are repaired properly, execute DBCC CHECKDB with a repair option.
DBCC CHECKALLOC messages are sorted by object ID, except for those messages
generated from tempdb. DBCC CHECKALLOC validates the allocation of all data
pages in the database while DBCC CHECKDB validates the page information
used in the storage of data in addition to validating the allocation
information.
DBCC CHECKDB validates the integrity of everything in a database. There is
no need to run DBCC CHECKALLOC or DBCC CHECKTABLE if DBCC CHECKDB either is
currently or has been recently executed.
DBCC CHECKDB performs the same checking as if both a DBCC CHECKALLOC
statement and a DBCC CHECKTABLE statement were executed for each table in
the database.
For more information regarding this issue please refer to the following
article on SQL Server Books Online.
Topic: "DBCC CHECKALLOC"
Topic: "DBCC CHECKDB"
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
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.|||Hi Steve,
To greatly simplify what Michael posted, you should run DBCC CHECKDB WITH
PHYSICAL_ONLY. It performs a DBCC CHECKALLOC (which is very fast) and then
page audits of every allocated page in the database. This will catch gross
page corruptions and torn pages (as long as the page has been written since
torn page detection was enabled), but not subtle corruptions that may affect
the finer logical consistency of b-trees and so on.
Let me know if you have any further questions.
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.
"Steve Myers" <stephencmyers@.hotmail.com> wrote in message
news:3e0c4493.0311051955.140ac3e6@.posting.google.com...
> Can someone explain what the differences are between the DBCC
> CHECKDB(XXXX) WITH PHYSICAL_ONLY verses the DBCC CHECKALLOC()?
> If you could run one of these, which one and why?
> Our production SAP database is too large and the impact of the DBCC is
> too great on a daily basis. So, we are going to run a full CHECKDB
> only weekly. However, for "comfort zone", we would at least like to
> verify the database structure daily during night hours.
> They both appear to be similar by reading the books.
> thanks
> Steve