Wednesday, March 28, 2012

Persistent Database Corruption

I am having an issue with persistent database corruption when copying
databases between instances. We have a production system with an 8GB (or
so) database that I copy over to our test systems on a regular basis.
A DBCC CHECKDB on this production database reports no issues. After
restoring the database (I do a backup and use the backup file to effect
the restore on a remote system via SQL Scripts) I have consistent
reports of corruption on the database when it is restored on the test
system.
A RESTORE VERIFYONLY on the backup file before restore only says "The
backup set is valid."
The amount of corruption varies; I have seen anywhere from 6 to 60
consistency errors.
I know this may be caused by underlying network or disk issues, but I'm
not sure how to go about confirming this when I don't see any other
related errors in the SQL or Windows event logs on the machine in question.
Stats:
SQL Server 2000 Enterprise SP3 (on an Active-Active cluster)
Windows Server 2003 Enterprise
4GB RAM
iSCSI SAN for all cluster-related disks using MS iSCSI drivers
Thanks,
JoshHmm. iSCSI drives. I have seen way too many problems with those. The MS
drivers may be just fine, but the iSCSI devices often cannot keep up with
the I/O load a SQL server can dish out. If he is paying attention, maybe
Dr. Tom Moreau will describe the hell he went through trying to use iSCSI
devices for a client.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joshua Andrews" <jrandrew@.nospam.hotmail.com> wrote in message
news:uNmE2Pu0GHA.4796@.TK2MSFTNGP06.phx.gbl...
>I am having an issue with persistent database corruption when copying
>databases between instances. We have a production system with an 8GB (or
>so) database that I copy over to our test systems on a regular basis.
> A DBCC CHECKDB on this production database reports no issues. After
> restoring the database (I do a backup and use the backup file to effect
> the restore on a remote system via SQL Scripts) I have consistent reports
> of corruption on the database when it is restored on the test system.
> A RESTORE VERIFYONLY on the backup file before restore only says "The
> backup set is valid."
> The amount of corruption varies; I have seen anywhere from 6 to 60
> consistency errors.
> I know this may be caused by underlying network or disk issues, but I'm
> not sure how to go about confirming this when I don't see any other
> related errors in the SQL or Windows event logs on the machine in
> question.
> Stats:
> SQL Server 2000 Enterprise SP3 (on an Active-Active cluster)
> Windows Server 2003 Enterprise
> 4GB RAM
> iSCSI SAN for all cluster-related disks using MS iSCSI drivers
> Thanks,
> Josh|||Can you post few consistency error you facing?
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Joshua Andrews" wrote:

> I am having an issue with persistent database corruption when copying
> databases between instances. We have a production system with an 8GB (or
> so) database that I copy over to our test systems on a regular basis.
> A DBCC CHECKDB on this production database reports no issues. After
> restoring the database (I do a backup and use the backup file to effect
> the restore on a remote system via SQL Scripts) I have consistent
> reports of corruption on the database when it is restored on the test
> system.
> A RESTORE VERIFYONLY on the backup file before restore only says "The
> backup set is valid."
> The amount of corruption varies; I have seen anywhere from 6 to 60
> consistency errors.
> I know this may be caused by underlying network or disk issues, but I'm
> not sure how to go about confirming this when I don't see any other
> related errors in the SQL or Windows event logs on the machine in question
.
> Stats:
> SQL Server 2000 Enterprise SP3 (on an Active-Active cluster)
> Windows Server 2003 Enterprise
> 4GB RAM
> iSCSI SAN for all cluster-related disks using MS iSCSI drivers
> Thanks,
> Josh
>|||Sreejith G wrote:
> Can you post few consistency error you facing?
Sure.
DBCC results for 'Table1'.
There are 3211496 rows in 94093 pages for object 'Table1'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'Table1' (object ID 1793857903).
*********
Bunch of clean tables
*********
DBCC results for 'Table2'.
There are 89870 rows in 2124 pages for object 'Table2'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'CMN_PersonsSearchInfo' (object ID 1629248859).
*********
Bunch of clean tables
*********
DBCC results for 'Table3'.
There are 196205 rows in 3616 pages for object 'Table3'.
CHECKDB found 0 allocation errors and 6 consistency errors in table
'Table3' (object ID 1565248631).
*********
Bunch of clean tables
*********
CHECKDB found 0 allocation errors and 64 consistency errors in database
'DataDB'.
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKDB (DataDB ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Of course, there are more, but the above is just a sample.
Thanks!
Josh

No comments:

Post a Comment