SQL Server
Database Integrity Check
WHY?
SQL DBA ensure that the integrity of his
database looks good to serve the clients in an efficient way. This is pretty much a simple task to
perform and there is not much if any interaction that needs to take place
between the DBA and SQL Server. Although this is pretty straightforward
to setup and run, this is not always implemented and secondly the output is
seldom reviewed to see if there are any issues
How?
Here I am going to discuss on key maintenance
tasks like integrity checks on SQL database & maintaining indexes in SQL.
Database integrity depends on both Physical & logical integrities.
•
Physical Integrity
–
Data pages are written on top of physical
storage as SQL Server requested and can be read correctly.
•
Logical Integrity
–
Data within pages is logically
correct.
Scenario:
I am going to
make use of CorruptDB database for our testing, which I restored from a
corrupted database backup. There are few Database Console Commands (DBCC), which we are going to use & check the integrity of
this database & then will run a repair over it to make the database
available for further use.
Keys:
- DBCC
CHECKALLOC - Checks the consistency of disk space allocation structures
for a specified database
- DBCC
CHECKCATALOG Checks for catalog consistency within the specified database.
- DBCC
CHECKDB - Checks for catalog consistency within the specified database.
- DBCC
CHECKTABLE Checks the integrity of all the pages and structures that make
up the table or indexed view.
The most widely
run option is DBCC CHECKDB because it checks the entire database, but the other
options are helpful for quicker spot checking. Here are some advantages of
DBCC CHECKDB.
–
Checks logical and physical integrity
in the database
–
Offers some repair options
–
Runs online using an internal
database snapshot
–
Synchronize executions with your
backup strategy
DBCC CHECKDB has some options which we can combine with the
integrity check while running it against any database.
|
Option
|
Description
|
|
PHYSICAL_ONLY
|
Only checks the physical
integrity to reduce overhead
|
|
NOINDEX
|
Does not perform
logical checks on nonclustered indexes
|
|
EXTENDED_LOGICAL_CHECKS
|
Performs additional
logical checks of indexed views, spatial, and XML indexes
|
|
TABLOCK
|
Uses locks instead
of database snapshots
|
|
ALL_ERRORMSGS
|
Returns all error
messages instead of the default action that returns the first 200
|
|
NO_INFOMSGS
|
Returns only error
messages and no informational messages
|
|
ESTIMATEONLY
|
Estimates the amount
of tempdb space that it
requires to run
|
• DBCC CHECKDB Repair Options
– Database needs to be in SINGLE_USER
mode
– REPAIR_REBUILD
– REPAIR_ALLOW_DATA_LOSS
Always ensure that you use REPAIR_REBUILD
option, which doesn’t affect your data. If the REPAIR_REBUILD haven’t fixed the
issue the best practice is to get back your data from a recent backup you have
rather than running REPAIR_ALLOW_DATA_LOSS.
But in case of NON-PROD data, it is fine to use the later one with negligible
data loss in your database. Let’s begin
As you
see above, I am running a DBCC CHECK on a database called CorruptDB , with
default options. This will give you results of all pages present in your
database along with faulty pages. Its bit hectic to scan through whole message
to identify the errors. In order to get only required errors we can use option
NO_INFOMSGS in CHECKDB.
Now
there are some consistency issues identified in table Orders as per the error message. Let’s run a select statement on
that table to see what happens.
When we
give a select * from Orders table it thrown an error. This shows something wrong with that table
present on this DB. But when we try to select particular order let see hat
happens.
As we
got the required row as expected, this clearly shows only some data of the
table got corrupted, which is preventing us to read the data from it. I can see
which data are missing from a relational table Order Detail. So that I ensure
those data got missed here.
This
clearly shows around 42 rows got corrupted here. To fix this I can make use of
DBCC REPAIR_REBUILD
(‘CorruptDB’). Since I know this is a database created from a backup of an
already corrupted database. This option is not going to work for me. So,
preferably I would go for a DB restore from a good database backup. Just for our discussion I will use second
option for fixing the integrity issues on my ‘corruptdb’.
After completion of the repair,
We will run a DBCC CHECK again to ensure issues got repaired & let’s try to access Order table & we can
see that we are getting data for our select statement but those 42 records will
be lost.
Thus we
had used DBCCs to check the database integrity & ran a repair to fix the
same.
VSG
SQL DBA
No comments:
Post a Comment