question

Sharma avatar image
Sharma asked

Database consistency, allocation and corruption issues.

Hi, What is the best approach to prevent database corruption (DBCC CHECKDB, CHECKCATALOG etc.)? Please suggest any routine check process to avoid database corruption issues? MS SQL 2000/2005. OS- XP/Windows Server 2003.
corruption
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
In checking for corruption/inconcistency you could use DBCC CHECKDB. But that's not the way to prevent inconsictensy. To do that, I'd say you should scan your harddrives for errors, make sure you have backup power for your servers so they don't just shut down on a power outage etc. Restoring backups every now and then is also a good idea. Here's a short article about actions to take when you discover corruption. [ http://www.sql-server-pro.com/dbcc-checkdb.html][1] [1]: http://www.sql-server-pro.com/dbcc-checkdb.html
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
I agree with @Magnus, corruption happens. More important is how you deal with it. Make sure your backups are good (consider restoring them to a test server on a regular basis). Make sure you have a restore strategy; what should be done and in what order. Consider if data loss is acceptable and how much. You can also use [database mirroring automatic page repair][1], if SQL Server detects corruption, the page is collected from the mirroring partner and used to repair the damage. This runs automatically and transparent to the users, a wonderful addition to the system IMO. [1]: http://msdn.microsoft.com/en-us/library/bb677167(v=SQL.100).aspx
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ARGGH, missed the version again!!
1 Like 1 ·
@sqlchamp - Note that database mirroring automatic page repair (catchy name) is only available from SQL 2008 and above - not for SQL 2000 / 2005
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
The two things I would work on to deal with database corruption are knowledge and protection. Knowledge, learn how to recover from that type of disaster and no, REPAIR_ALLOW_DATA_LOSS is not a valid approach in most cases. Protection, get good backups in place and test them. You can go further and look into various types of disaster recovery scenarios such as mirroring or SAN-level snapshots or any number of other approaches. But you need to start with knowledge. I'd suggest reading up all the Books Online topics on the Database Consistency Checker and then move on to reading Paul Randal's blog over at SQLSkills.com. Between those two things, you should at least be prepared.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SmithSean avatar image
SmithSean answered

The best approach is to restore the database from updated backup copy. If backup is not updated then, you can go with other valid approaches as mentioned by Grant Fritchey. Read this article on SQL database corruption https://social.technet.microsoft.com/wiki/contents/articles/51727.sql-server-how-to-recover-from-corrupt-sql-server-database-when-recovery-via-tsql-might-not-be-possible.aspx

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.