| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2004-08-18 : 14:43:46
|
| When sorting uot a Suspect Database, caused by some sort of unscheduled reboot(e.g. power cut), and assuming the Unscheduled Reboot might happen again, are there any precautions which should be taken?Today I was worried that an unanticipated reboot would cause SQL AGENT to resume running whilst I was in the process of trying to sort out the mess. That would trigger backups whilst I was trying to do DBCC CHECKDB and also start running hourly data import tasks and so on.I disabled SQL AGENT (in SERVICES) whilst I was messing around, but it occurred to me that SQL was set to "Restart SQL Agent if it stops unexpectedly"Anything else I should have done?Kristen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 14:47:58
|
| "Restart SQL Agent if it stops unexpectedly"The key part is unexpectedly. When you manually stop the service or disable it, that isn't unexpected. In case changing the suspect status doesn't fix the problem and you need to restore to a point in time, just make sure you have valid backups. You can't prove that they are valid until you do a restore so you should be restoring these on a test system sometime after the backups complete. We restore our tlogs after they are backed up by doing log shipping. That doesn't test out the fulls, so we test those out separately.Tara |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 15:07:46
|
| That's a good point. Next time I will restore the Suspect DB's backup to a different database (maybe different server) and do the DBCC CHECKDB, and [if all is well] then restore to the original DB.I was worried about ME needing to restart SQL [after using sp_resetstatus OR because of yet-another-power-cut] and SQL AGENT then starting up and running its normal scheduled tasks the moment it got going - which would have caused mayhem - on top of the mayhem I already had to sort out.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 15:09:11
|
| Well the tests of the backups should be part of regular jobs, meaning this should be happening on a scheduled basis. You shouldn't have to test it out while in a disaster.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 15:22:11
|
| Yes, I agree. In your case would you be comfortable that the latest backup would already have been tested by the disaster-recovery-mechanisms??If so that's well cool!Even so, not sure I would trust it - the DB in this case was only a couple og Gig, so only a few minutes to restore & test. If it was an hour or so to restore I'd be needing to go with the disaster-recovery-mechanism - as getting it restored would be time-critical.There again, I suppose if the database was that big I would have log-shipping and would have pressed the warm-standby into action instead.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 16:12:07
|
| Our backups aren't currently being tested each day, but I can guarantee that one of them on disk would be valid. If they aren't, well then I'd just fail over to the disaster recovery site since it's only 15 minutes behind anyway.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 16:31:12
|
| If you fail over to the Warm_Standby is it a major hassle to then bring the original server back as the primary?Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 16:44:52
|
| Well, when we fail over to the disaster recovery site, we would then setup log shipping to the primary site again. It's setting up log shipping that's a pain. Not for one database, but when you've got 15 or so. Most of the pain though is with configuring the apps to point over to the new server. BTW, we fail over to the disaster recovery site at least twice a year to make sure we can do it in the case of a real disaster. So this is something that we do regularly.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 16:49:39
|
| Makes sense. Are you seriously at risk when you fail over, and before the Primary has been set up for log shipping? (Sort of "window of opportunity" stuff)Bit of a moot point I suppose, but I'm just curious about how you treat that scenario [e.g. "all hands on deck because although we are OK we are now seriously at risk if there is a second failure"]Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 16:58:19
|
| Yes we are at risk but only for a small window. But we setup backups at the disaster recovery site and copy those over to the primary the night that we move over to the disaster recovery site. Setting this stuff up is before we setup log shipping which happens in a couple of days. So until log shipping is setup, the files are being copied. They just aren't being applied. So the only risk is between the fail over and the short time before the files are copied over.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 17:00:41
|
| Elegantly simple.Kristen |
|
|
|
|
Suspect Database Woes