September 9, 2013 at 5:33 am
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 !!
September 9, 2013 at 7:17 am
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[/url] 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[/url] is the way the majority of people do it. You should also check out this post[/url] 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
Consultant - Straight Path Solutions
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
September 9, 2013 at 7:18 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2013 at 9:16 am
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!!!
September 12, 2013 at 6:40 am
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.
September 12, 2013 at 6:56 am
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
Consultant - Straight Path Solutions
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
September 12, 2013 at 7:22 am
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.
September 12, 2013 at 8:11 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2013 at 1:24 am
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!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply