doh
-
Hi,
Imagine the scenario. A user who doesn't listen, a scheduled backup to a hard drive which is never checked for free space (what a good idea), and a badly designed db. And one junior dba getting the flack.
Ok, so the db corrupts. The user in their infinite wisdom hard reboots the server several times as 'It stopped working', therefore stopping the auto recover. Fine I think, I'll double check the logs, run sp_who2 to make doubly sure no rollback is running, then I'll manually restore. I do the following procedure:
1. Run sp_resetstatus
2. Set the db to single user
3. dbcc checkdb repair_rebuild
My question, as this has happened a number of times at various sites, is that the above procedure doesn't always work. Is there a better, safer, more reliable way of recovering a suspect db when there is no recent backup?
Thanks
Ritch
"I didn't do anything it just got complicated" - M Edwards -
Ritch,
First thing I'd follow up on is the "this has happened a number of times at various sites" bit. I've worked with SQL2000 quite a bit and seen a real corruption about twice. One needed a point in time recovery from a backup, an another I got lucky and only need to recreate an index.
If you're getting it often then time to get Microsoft involved.
To answer your question, I think recovery from backups is usually the most reliable way of getting over a corruption... but there's always that niggling suspicion that the backup also contains the seed of a corruption.
Just a comment on your opening para, and I don't mean to be harsh because there's already enough flak, but it really should be your job to check that backups worked - regardless of the cause of any failure (not enough disk space, target file locked by tape backup software, SQL Agent failed, etc etc). This is one of the things you'll need to put on your daily must-do list.
Also, the "badly designed db" bit - not your fault I know, and we've all worked with them. Your job is now to use all the tools available and determine the areas in most need of improvement and start testing alternatives and making recommendations. Obviously
Cheers,
- Mark -
Thanks for the reply. I totally agree with what you said but theres a political issue with the customer they are supposed to provide their own 1st line support. I have suggested implementing email notification for failed back ups etc, that got dismissed, as 'we are to busy'. Ever tried talking to a brick wall??
I think the corruption due to dodgy data being passed from a third party source, thats not being validated correctly.
Cheers
"I didn't do anything it just got complicated" - M Edwards -
Okay, I understand your situation now. Better you than me.
As for the corruption, even dodgy data should not cause SQL Server corruptions - it should simply be rejected. You might want to get MS involved - could be a bug.
Cheers,
- Mark -
I don't reckon it's a bug, it happens on the SQL 7 servers as well. It's almost definately a problem with out code.
I live in hope of the dev team sorting it 😀
"I didn't do anything it just got complicated" - M Edwards -
Be sure that every statement is documented. Keep copies of everything. Print out their emails and keep a file. If they are ignoring your best practices, that's fine. But you should protect yourself. If, down the line, their database becomes unusable, and they turn around to sue you - because you are the DBA - you will need those documents.
Just a thought.
Patrick
Quand on parle du loup, on en voit la queue
-
Thanks, no worries on that one my backs got more coverage than the gulf war had
"I didn't do anything it just got complicated" - M Edwards -
I ran across something that made me think of your corruption issue. From SQL Server 2000 System Administration - Microsoft Press:
(paraphrased to shorten) "Beware of write caching disk contollers - unless the controller is designed for use with a database, you risk corrupting your database. SS2000 relies on the operating system to notify it when an i/o operation has completed. If the write-caching controller notifies the operating system that a write operation has completed that never actually completes, your database can become corrupt. This can occur if the power fails or the reset switch is pressed before a write operation of data in the cache has actually been written to the drive. Write-caching controllers designed for DBs use a number of mechanisms to prevent this from occurring."
It may be something to consider. Also, you may want to run chkdsk on the hard drive(s). I recently had some odd problems that I thought were due to SS/DB corruption, but it turned out to be due to bad sectors on my hard drive. I replaced the HD, re-installed SS and restored from backups prior to the corruption. Since then things are running smoothly.
Good luck.
Bill
-
I ran across something that made me think of your corruption issue. From SQL Server 2000 System Administration - Microsoft Press:
(paraphrased to shorten) "Beware of write caching disk contollers - unless the controller is designed for use with a database, you risk corrupting your database. SS2000 relies on the operating system to notify it when an i/o operation has completed. If the write-caching controller notifies the operating system that a write operation has completed that never actually completes, your database can become corrupt. This can occur if the power fails or the reset switch is pressed before a write operation of data in the cache has actually been written to the drive. Write-caching controllers designed for DBs use a number of mechanisms to prevent this from occurring."
It may be something to consider. Also, you may want to run chkdsk on the hard drive(s). I recently had some odd problems that I thought were due to SS/DB corruption, but it turned out to be due to bad sectors on my hard drive. I replaced the HD, re-installed SS and restored from backups prior to the corruption. Since then things are running smoothly.
Good luck.
Bill
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply