SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Paul Randal / The Perils of Running Database Repair / Latest Posts

RE: The Perils of Running Database Repair

Paul Randal — Wed, 27 Jun 2012 06:14:35 GMT

It entirely depends on which system tables are corrupt, and what the corruption is. Basically, it's a crap-shoot whether it'll work or make things worse because the system tables are quite twitchy when direct physical changes are made to them.Thanks

RE: The Perils of Running Database Repair

Satish Nagaraja — Wed, 27 Jun 2012 06:10:24 GMT

Thanks for this article , all this while in my DBA career was wondering why REPAIR_REBUILD did not work most times on cluster index chain broken.But observed in one of the client places REPAIR_ALLOW_DATA_LOSS did work for corrupt system tables without data loss but yes backups are best bet's as you said.CheersSatish

RE: The Perils of Running Database Repair

timothyawiseman — Mon, 25 Jun 2012 13:56:16 GMT

Fantastic article, thank you.

RE: The Perils of Running Database Repair

cfradenburg — Mon, 25 Jun 2012 10:59:24 GMT

[quote][b]Paul Randal (6/25/2012)[/b][hr]Be careful though because SQL Agent will only capture the first X bytes of the output (unless that changed in recent versions). You may want to go the route of using the undocumented WITH TABLERESULTS and storing the output in a table for later perusal. See [url=http://www.mssqltips.com/sqlservertip/2325/capture-and-store-sql-server-database-integrity-history-using-dbcc-checkdb/]here[/url] for an example.[/quote]We write the output to a file and that has the entire output. Use whichever works better for you.

RE: The Perils of Running Database Repair

Chrissy321 — Mon, 25 Jun 2012 10:50:42 GMT

Thanks Paul I will definitely explore that option. I have a monitoring system for failed jobs so it sounds like I would at least be notied that something is amiss. Seems like a good example of maintenance plans being not quite as robust as built or borrowed maintenance scripting.

RE: The Perils of Running Database Repair

Paul Randal — Mon, 25 Jun 2012 10:43:29 GMT

Be careful though because SQL Agent will only capture the first X bytes of the output (unless that changed in recent versions). You may want to go the route of using the undocumented WITH TABLERESULTS and storing the output in a table for later perusal. See [url=http://www.mssqltips.com/sqlservertip/2325/capture-and-store-sql-server-database-integrity-history-using-dbcc-checkdb/]here[/url] for an example.

RE: The Perils of Running Database Repair

Paul Randal — Mon, 25 Jun 2012 10:40:25 GMT

The job will fail and @@ERROR will be set to the last severity 16 (or higher) message that is output.

RE: The Perils of Running Database Repair

cfradenburg — Mon, 25 Jun 2012 10:40:21 GMT

[quote][b]Chrissy321 (6/25/2012)[/b][hr]Will a job with the following fail if corruption exists? Or will the job succeed?DBCC CHECKDB(N'master') WITH NO_INFOMSGS[/quote]The job would fail and report at least some of the errors it encountered. To see all errors you need to use "WITH ALL_ERRORMSGS". That can be combined with NO_INFOMSGS.

RE: The Perils of Running Database Repair

Chrissy321 — Mon, 25 Jun 2012 10:37:14 GMT

Will a job with the following fail if corruption exists? Or will the job succeed?DBCC CHECKDB(N'master') WITH NO_INFOMSGSI'm suddenly feeling a bit exposed since I am not sure if I would get notified if corruption exists.

RE: The Perils of Running Database Repair

jwbrown65 — Mon, 25 Jun 2012 10:26:00 GMT

Thanks Paul. You are extremely gracious with your time on these matters. You helped me a few years ago on a discussion board covering this very topic. We used the REPAIR_ALLOW_DATA_LOSS on a db with more torn pages than an eighth grade math book (ok, not that many). We recovered and restored an old copy of the db as you've suggested here to determine the problem data was outdated. Thanks again.

RE: The Perils of Running Database Repair

mohammed moinudheen — Mon, 25 Jun 2012 10:17:39 GMT

[quote]...we created a test system that took a known database, corrupted it randomly..How do you manually corrupt the database? [/quote][url=http://www.sqlservercentral.com/articles/CheckDB/88963/]How to Create a Corrupt SQL Server Database [/url]

RE: The Perils of Running Database Repair

Paul Randal — Mon, 25 Jun 2012 10:17:11 GMT

There are test hooks inside SQL Server that the product group can use.There are plenty of blog posts out there describing how to do it yourself using a hex editor - Google for me and XVI32 and you'll find one.

RE: The Perils of Running Database Repair

kevin77 — Mon, 25 Jun 2012 10:12:32 GMT

[quote]...we created a test system that took a known database, corrupted it randomly..[/quote]How do you manually corrupt the database?

RE: The Perils of Running Database Repair

jswong05 — Mon, 25 Jun 2012 08:22:20 GMT

[quote][b]I still do not comprehend a lot of the disk mirror/replication[/quote]Using software like doubletake, you can replicate (copy) disk array at data-block level to another environment and keep them in synch. Only the changed data-blocks will be copied.

RE: The Perils of Running Database Repair

Thomas LeBlanc — Mon, 25 Jun 2012 07:40:39 GMT

Excellent article and thanks for answering the questions posted. It took me a couple of years and hard experience to finally upstanding the concepts of backup and restore plus database mirroring after coming from a development background. I still do not comprehend a lot of the disk mirror/replication, have to leave that to the networking/SAN group.Thanks,Thomas

RE: The Perils of Running Database Repair

Paul Randal — Mon, 25 Jun 2012 06:08:14 GMT

No, you're not guaranteed that you'll have data loss, but it's more likely. As long as you're doing frequent log backups, and you're able to do a tail-of-the-log backup, you should be able to get away without data loss as long as your full and diff backups don't contain corruption. But there's no guarantee.

RE: The Perils of Running Database Repair

Geoff A — Mon, 25 Jun 2012 06:01:20 GMT

again, thank for clarifying that Paul...so unless you have some hardcore redundancy, you most likely will always incur some data loss....you can mitigate that loss by running DBCC CHECKDB often.i have seen some recommendations on the web that you run it once a week and i was always puzzled by that strategy :hehe:

RE: The Perils of Running Database Repair

Paul Randal — Mon, 25 Jun 2012 05:57:04 GMT

You don't know which logs you can restore up to. If you want to guarantee zero data loss you can't rely on just backups - you need to have a redundant copy of the database that is synchronously updating from the production database - either using synchronous database mirroring or some kind of synchronous I/O subsystem replication/mirroring. Defense in depth - just like with security.

RE: The Perils of Running Database Repair

Geoff A — Mon, 25 Jun 2012 05:52:29 GMT

thanks Paul,so i take daily fulls at 2 am. I take logs every 15 minutes. I run DBCC checks daily at 8pm and have processes in place to email me when DBCC CHECKDB encounters errors.....so if at 8 pm, i receive an error, how does one know what logs i can restore up to?doesn't a corrupted DB backup corrupted?

RE: The Perils of Running Database Repair

Paul Randal — Mon, 25 Jun 2012 05:40:00 GMT

Glad you like the article.Backup strategy is dictated by what restores you want to be able to do, what the data volume is, what storage space you have, and so on. If you have an RTO of 8 hours with a zer data-loss RPO, you need to be able to restore from your most recent full backup and all the way up to the most recent log backup in 8 hours. Depending on the amount of change in the database, you're likely going to be using differential backups as well. Answering this question is an entire series of articles in itself.As far as consistency checks are concerned, if you're able to, run them on the production system as often as you can. The quicker you can find that you have corruption, the more likely you'll be able to recover with the minimum downtime and data loss. If you can't run them in production, take your full backup, restore it somewhere, and run consistency checks on it. If its clean, you know the production database was clean at the time the backup was taken. This is another article-sized answer, but I hope that helps.Thanks

RE: The Perils of Running Database Repair

Geoff A — Mon, 25 Jun 2012 05:15:45 GMT

this is a great article about the perils of using the ALLOW_DATA_LOSS command, but i would be curious to know what Pauls recommendation are for a backup strategy.when is the best time to run DBCC checks vs when you take a full backup?and based on the timing of those, how does one ensure not to incur data loss?

RE: The Perils of Running Database Repair

GilaMonster — Mon, 25 Jun 2012 02:58:07 GMT

If you're talking about the SQL Server database engine, Paul's said before on Twitter that it's mostly C++ with some assembler

RE: The Perils of Running Database Repair

mohammed moinudheen — Mon, 25 Jun 2012 02:50:32 GMT

Thanks for taking your time in writing this useful article. By the by, in which language is the coding done.

The Perils of Running Database Repair

Paul Randal — Sun, 24 Jun 2012 23:30:58 GMT

Comments posted to this topic are about the item [B]The Perils of Running Database Repair[/B]