consistency errors

Can somebody explain to me what is a consistency error and
how does it happens. Is there a way to prevent them ?A consistency error, in its most general form, is when an inconsistency
exists, based on some rules.
In SQL Server, a consistency error is when the structural or logical
integrity of the database or a table within the database has been broken.
Some examples:
1) page X in the database thinks its allocated to table Y, but scanning
table Y does not show any links to page X.
2) A record in the clustered index for table A does not have exactly one
matching record in non-clustered index B on table A
3) A record in the clustered index for table J has a text/image column with
timestamp K, but no text was found with timestamp K in the text index for
table J
There are literally hundreds of such consistency rules inside SQL Server,
all of which are verified with the DBCC CHECKDB command (and related
commands).
Such consistency errors are usually caused by bad hardware corrupting pages
in the database. Given that there's no way to prevent hardware going bad,
the issue is really how to ensure you can recover from a hardware-caused
corruption with minimal downtime and data loss. This involves having a
disaster recovery plan and a solid backup strategy.
I don't have pointers to hand to the existing best-practices docs and KB
articles but I'm sure an MVP will reply-group with them.
Regards,
Paul.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pollus" <anonymous@.discussions.microsoft.com> wrote in message
news:01e201c3b2c7$3d7af450$a501280a@.phx.gbl...
> Can somebody explain to me what is a consistency error and
> how does it happens. Is there a way to prevent them ?|||As Paul has already explained some issues, especially
hardware problems, can't be totally prevented. You want to
monitor your systems to stay on top of any potential
problems which can arise. Chapter 4 in the Operations Guide
on TechNet has some good checks, recommended frequencies,
info on backups, availability, etc:
http://www.microsoft.com/technet/prodtechnol/sql/maintain/operate/opsguide/sqlops4.asp
Also related would be the 2003 August and September issues
of SQL Magazine had several articles on disaster prevention,
disaster recovery:
www.sqlmag.com
-Sue
On Mon, 24 Nov 2003 12:12:09 -0800, "Pollus"
<anonymous@.discussions.microsoft.com> wrote:
>Can somebody explain to me what is a consistency error and
>how does it happens. Is there a way to prevent them ?|||> I don't have pointers to hand to the existing best-practices docs and KB
> articles but I'm sure an MVP will reply-group with them.
Below is my general recommendations (after going though it in the MVP group). I don't have KB
pointers, though...
Here are the general recommendations for handling a suspect or corrupt database:
0. Ensure you have a backup strategy that you can use to recover from hardware failures (including
corruption). I recommend performing both database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and KB for the error
numbers that CHECKDB gives you. There might be specific info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.; search Books Online and KB
for those errors. You don't want this to happen again! If the database is suspect, the file might
have been in use by for instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of course. If the database is
suspect, then the NO_TRUNCATE option for the RESTORE command must be used. Also, you might want to
do a file backup of the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per step 4, then you will
most probably have zero dataloss. You should restore the latest clean database backup and the
subsequent log backups including the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a secondary option but this
will often result in loss of data. Additional solutions, depending on the errors, may be to manually
rebuild non-clustered indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect" the database using
sp_resetstatus. Read about it (books online, KB, google etc). It might help but if the database is
too damaged, it might just pop back to suspect again. There's also something called "emergency mode"
which is a "panic" status you can set in order to try to get data out of a damaged database. I think
the name of that option speaks for itself. Again search the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you through the steps
appropriate for your particular situation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eQgnZsssDHA.3436@.tk2msftngp13.phx.gbl...
> A consistency error, in its most general form, is when an inconsistency
> exists, based on some rules.
> In SQL Server, a consistency error is when the structural or logical
> integrity of the database or a table within the database has been broken.
> Some examples:
> 1) page X in the database thinks its allocated to table Y, but scanning
> table Y does not show any links to page X.
> 2) A record in the clustered index for table A does not have exactly one
> matching record in non-clustered index B on table A
> 3) A record in the clustered index for table J has a text/image column with
> timestamp K, but no text was found with timestamp K in the text index for
> table J
> There are literally hundreds of such consistency rules inside SQL Server,
> all of which are verified with the DBCC CHECKDB command (and related
> commands).
> Such consistency errors are usually caused by bad hardware corrupting pages
> in the database. Given that there's no way to prevent hardware going bad,
> the issue is really how to ensure you can recover from a hardware-caused
> corruption with minimal downtime and data loss. This involves having a
> disaster recovery plan and a solid backup strategy.
> I don't have pointers to hand to the existing best-practices docs and KB
> articles but I'm sure an MVP will reply-group with them.
> Regards,
> Paul.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Pollus" <anonymous@.discussions.microsoft.com> wrote in message
> news:01e201c3b2c7$3d7af450$a501280a@.phx.gbl...
> > Can somebody explain to me what is a consistency error and
> > how does it happens. Is there a way to prevent them ?
>