SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC CHECKDB ON PRODUCTION SERVER?


DBCC CHECKDB ON PRODUCTION SERVER?

Author
Message
rollercoaster43
rollercoaster43
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 435
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 !!
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24668 Visits: 14905
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)

Group: General Forum Members
Points: 119315 Visits: 45555
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

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


rollercoaster43
rollercoaster43
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 435
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!!!
Talib123
Talib123
Right there with Babe
Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)

Group: General Forum Members
Points: 775 Visits: 1931
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.
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24668 Visits: 14905
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Talib123
Talib123
Right there with Babe
Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)

Group: General Forum Members
Points: 775 Visits: 1931
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)

Group: General Forum Members
Points: 119315 Visits: 45555
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

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


Shabba
Shabba
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

Group: General Forum Members
Points: 621 Visits: 1846
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search