February 5, 2010 at 2:02 am
I'm building up a new set of servers. I've got two databases (one tiny and the other 600GB ish) which are both mirrored. I'm using SQL Standard 2008 on Server 2008. The witness is on a single node cluster (for now, the next node is being built up).
The DB is replicated (and mirrored replication is working) plus we have mirrored service broker using this as the target.
I've been happily doing various tests of failovers and the like to ensure that our systems stay up as planned. That was, until yesterday.
I forced an automatic failover by re-starting the SQL service on the principal. It failed over as expected. I then failed the mirror over the same way, but the database hadn't finished synchronising properly. The tiny database failed over fine, but the larger one went into recovery mode (not entirely unexpected. I was trying to prove a point, which I did too successfully).
I figured it would recover overnight so turned off my alerts and left it to it. However, it's still in recovery mode this morning.
I can't see any easy way of finding what progress this is through recovery (suggestions needed please). I'm more than happy to bin the DB and restore from backups to just before it went funky. However, I can't delete the DB because it's replicated and the combination of mirroring, replication and recovery mode etc...are making it difficult to bin the DB completely.
Any ideas on the best way to go about removing it so that I can restore from backups, re-run various config scripts and re-build my mirror ?
Thanks
Ryan
February 5, 2010 at 3:55 am
quick update.....
On what was the principal, it shows as (Restoring..... - with no mention of the mirror) I can bring up general properties and that's it.
On the mirror (as it was) it shows the DB (but I can't expand) and I can get the general properties. However the status is empty.
If I try running ALTER DATABASE MyMessedUpDB SET PARTNER OFF it tells me :
Msg 945, Level 14, State 2, Line 1
Database 'laterooms' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Using Spotlight I can see that SQL is using 188Mb of 28GB. Drive space wise I have 222Gb free on one drive (data files take up 272Gb on this drive). My other data drive has 122Gb free and the data files take up 366Gb for this DB. I can throw more space at this if needs be (temporarily) but is this the answer ? It seems strange to need to do this.
February 5, 2010 at 4:28 am
I think I've sussed this.
I'd made a change to the location of one of the files before I failed the mirror back over. It's taken a hell of a long time for an error to get generated, but I eventually got this from one of my alerts :
DESCRIPTION:FileMgr::StartSecondaryDataFiles: Operating system error 2(failed to retrieve text for this error. Reason: 15105) occurred while creating or opening file 'L:\MyMessedUpDB\Journal_01.ndf'. Diagnose and correct the operating system error, and retry the operation.
I copied the files in question to the new location and re-started the SQL service (this was on the server that I intended to work as the mirror).
Now, the DB shows as Principal and Synchronizing. The good news is that my publications are showing again as well. Best bet here I reckon is to break the mirroring and re-apply it. My original principal now lets me delete the DB so it should all be good to go.
So....if I wanted to repeat this (not that I will). It was to fail over the mirror, change a file location (as I'd messed up when doing the original restore on the mirror) then fail back over before it finished synchronizing.
Ryan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply