Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC CHECKDB ON PRODUCTION SERVER? Expand / Collapse
Author
Message
Posted Monday, September 9, 2013 5:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:48 AM
Points: 60, Visits: 400
Hi All,

We perform a monthly restore activity on a Test Server and then run CHECKDB on the test server itself.

There hasn't been any instance where a Checkdb was performed on the Prod server itself.
Can someone please suggest if running checkdb on the prod server itself mandatory to handle corruption issues ?

I assume that corruption due to the underlying hardware can be caught only if we run a consistency check on the prod server itself.

Any suggestions on this will be appreciated. Thanks in advance !!
Post #1492736
Posted Monday, September 9, 2013 7:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 11,141, Visits: 12,884
First, running CHECKDB monthly probably isn't often enough. Basically you are saying we can live with having to go back possibly months to a clean database. Check this post for frequency suggestions.

Depending on the size of the database and the resources on the server you can run CHECKDB on a production database, which you can see in this survey is the way the majority of people do it. You should also check out this post for other options.

You don't have to run CHECKDB on the production server, running it on a restored backup is fine to find corruption. The best practice for repairing corruptions is a restore of a clean (non-corrupt) backup which is why monthly probably isn't often enough, so it doesn't matter where you find the corruption, the repair process is the same and has to take place on the production server. There are corruptions that can be repaired without a restore, for example, a corruption found in a non-clustered index can normally be repaired by rebuilding the index, but you should still check the underlying I/O subsystem for problems after making this repair.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1492768
Posted Monday, September 9, 2013 7:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:10 AM
Points: 42,425, Visits: 35,485
Running CheckDB on a restored backup is fine. However, monthly is maybe not fine. Do you retain all your backups for a full month? If not, what are you going to do if CheckDB finds severe errors and all your backups contain that corruption?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1492770
Posted Monday, September 9, 2013 9:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:48 AM
Points: 60, Visits: 400
Thanks a lot Jack and Gail for your wonderful suggestions and good pointers towards the issue.

I agree we need to increase the frequency of the consistency checks and the next step would be to raise an email alert if any erros are encountered.. I will look at my resrouces and incorporate this practice at the earliest

We did face an issue wherein the databases got corrupted due to the Drives getting corrupted, and it was worse wherein we had to rebuild the entire database. We contacted Microsoft and they gave the sys admins some hotfixes to be applied after issues were encountered in chkdsk

Fortunately, we had a mirrored db which was not corrupted and luckily we had a very narrow escape..

Thanks Again!!! Cheers!!!
Post #1492828
Posted Thursday, September 12, 2013 6:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:20 AM
Points: 86, Visits: 515
One thing I never encountered is corruption of a db. It makes me nervous for some reason just thinking about.

Currently I backup and restore a few main production dbs to my DEv server and perform CHECKDB on a daily basis. The prod server is checked once a week. However I was under the impression that the DEV server would only check for logical errors or am I incorrect in that assumption.

Thinking about it the physical corruption would be present in the backup file would it not. If it wasn't we could just restore the corrupted db from any backup after the corruption occured.

Instead of going to a backup file before the corruption occured.
Post #1494136
Posted Thursday, September 12, 2013 6:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 11,141, Visits: 12,884
Talib123 (9/12/2013)
One thing I never encountered is corruption of a db. It makes me nervous for some reason just thinking about.

Currently I backup and restore a few main production dbs to my DEv server and perform CHECKDB on a daily basis. The prod server is checked once a week. However I was under the impression that the DEV server would only check for logical errors or am I incorrect in that assumption.

Thinking about it the physical corruption would be present in the backup file would it not. If it wasn't we could just restore the corrupted db from any backup after the corruption occured.

Instead of going to a backup file before the corruption occured.


Yes physical corruption will be present in the backup which is why restoring a backup and doing CHECKDB against it is an acceptable practice.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1494144
Posted Thursday, September 12, 2013 7:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:20 AM
Points: 86, Visits: 515
One thing I never encountered is corruption of a db. It makes me nervous for some reason just thinking about.

Currently I backup and restore a few main production dbs to my DEv server and perform CHECKDB on a daily basis. The prod server is checked once a week. However I was under the impression that the DEV server would only check for logical errors or am I incorrect in that assumption.

Thinking about it the physical corruption would be present in the backup file would it not. If it wasn't we could just restore the corrupted db from any backup after the corruption occured.

Instead of going to a backup file before the corruption occured.
Post #1494164
Posted Thursday, September 12, 2013 8:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:10 AM
Points: 42,425, Visits: 35,485
Restoring a backup of a database and running CheckDB on the restored backup is a perfectly acceptable way of doing integrity checks. The only problem with it is the potential for false positives.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1494195
Posted Friday, September 13, 2013 1:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 5:33 AM
Points: 307, Visits: 1,846
FWIW As part of our daily backup scripts we run checkdb on all production databases prior to the daily backup taking place. If problems are found then the database doesn't get backed up. This will then get picked up by whoever's doing the SQL checks the following morning and investigated.

Nothing worse than finding out you've been backing up corrupt databases for the last month or so!
Post #1494486
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse