Disconnected Mirror / In Recovery

Hello,

I'm having a problem with two mirrored databases, using SQL Server 2005 SP2, that autmatically failed over last night for unknown reasons. I was trying to fail them back over to the primary SQL server because it says the mirror is disconnected and out of sync. Other databases failed over too, but I was able to fail those back over without a problem. It's just these two. I removed the mirror from the secondary server thinking that this would allow me to restore the database back on the primary server, but that didn't help.

These two databases show a status of (Principal, Disconnected / In Recovery) and I still cannot do anything with the two databases on the primary server. When I try to pull up the properties for them, I get the following error:

"Database <Name> is enabled for Database Mirroring, but neither the partner nor the witness server instances are available: the database cannot be opened.(Microsoft SQL Server, Error: 955)"

I cannot delete, detach, Alter or do anything with the databases. If I could just delete them that would be fine so I can just do a restore, but I can't seem to do anything.

Does anyone know what I can do to resolve this problem.

Thanks in advance!

-Jay

I was finally able to get the two databases to come back up online. I was trying so many different things, I 'm not sure which one, or combination, fixed it. But I checked the processes and there was one process running on one of the databases so I killed it. It seemed to come back right away, but after that, I was able to detach the DB. I reattached it and both of the databases came up with an online status.

I hope this helps.

-Jay

|||

This sounds similar to problems I have had (see my post below on split brain). I too had to kill connections to the principal in order to do anything to fix it, it went into recovery when I tried to remove mirroring and all users were disconnected.

I think this is very undesirable. There is no way both nodes should ever be the principal. If failover happens, it should either complete or not, there should be no half way, whatever the mode of failure.

|||

Sound like it, also mention what kind of configuration mode you are using for DB Mirroring.

As suggested referring to witness server log will helps a bit to see what is going on.

Disconnect Client Connection via T-SQL/Store Proce

Hi Gents,
If there are some client connections on the database and I would like to
detach the file, it's failed when execute the store proc "sp_detach_db". Any
SQL statement or Store Procedure on clearing/kicking out the current client
connections? Thanks in advance.
Regards,
CurtisLYou can use the KILL command.|||Moreover, using kill <id> with statusonly. So this way I can see the rollbac
k
progression out of danger.
"markc600@.hotmail.com" wrote:

> You can use the KILL command.
>

Disaster Recovery: SQL Server 2000 on Win 2000 Server.

This past Monday, our Raid 5 array failed on our SQL 2000 server (standard edition). Long story short, we were able to get the data back, mostly. In SQL server Ent. Mgr., I had to restore 4 databases that were 'Suspect' and "greyed out". No problems there... I was able to restore them.

What was interesting was that the SQL Server Agent would not start. As it turned out, a few files were either corrupt or missing after we got the machine back up. We had to restore a few files from tape backup and now the SQL Server Agent starts... but now I have a new problem.

For some reason, my default SQL Server Agent "Job" that is scheduled to run once per day fails. It appears that all of the databases get backed up, but for some reason, the msdbdata.mdf database is NOT being backed up.

I have a few questions. Could my msdbdata.mdf be corrupt? I have a successful backup of the msdbdata.mdf table from 9/10/2007. Can I just restore from that? Will there be any repercussions to any of the other database tables if I do?

Furthermore, should I just restore the msdbdata.mdf, master.mdf and the model.mdf? I really have no idea what these tables do and the repercussions of modifying them. I am not a DBA (obviously) and I just don't want to make an mistakes.

Thanks, CFDev.
Can you pls provide the details of the job history if any ? I guess you mean to say that msdb database backup fails if i interpret it correctly and Yes you can go ahead and restore the msdb.bak dated 9/10/2007 and check........the only issue is that all the jobs,dts packages created since 9/10 will not be available if you restore it.........I don't think its needed its enough if you restore msdb no need to restore master or model dbs..........
|||The "Job" is part of the databases default "maintenance plan". It consists of four parts:
1. DB backup
2. Integrity checks
3. Optimizations
4. Transaction Log Backup

This should be familiar to you, no? Anyway, would be okay to restore the msdb database then? How about the model and master databases? No tables, stored procedures, dts packages or anything has been changed. I just want peace of mind that if I were to restore any of these database tables, it would not effect any of my website's database tables. Make sense?

|||

Yes i am familiar with it if you feel there is no change done in msdb after 9/10 you can go ahead and restore msdb. Master stores all the login information and model acts as
template for other dbs so just restore msdb as it contains all the jobs and dts packages....I dont think restoring msdb would affect your db tables.......

|||

It would also be a good idea to run checkdb on all databases to make sure that they're clean.

You haven't made changes since 9/10, but you'd really rather not trip across hidden problems later.

Disaster Recovery: SQL Server 2000 on Win 2000 Server.

This past Monday, our Raid 5 array failed on our SQL 2000 server (standard edition). Long story short, we were able to get the data back, mostly. In SQL server Ent. Mgr., I had to restore 4 databases that were 'Suspect' and "greyed out". No problems there... I was able to restore them.

What was interesting was that the SQL Server Agent would not start. As it turned out, a few files were either corrupt or missing after we got the machine back up. We had to restore a few files from tape backup and now the SQL Server Agent starts... but now I have a new problem.

For some reason, my default SQL Server Agent "Job" that is scheduled to run once per day fails. It appears that all of the databases get backed up, but for some reason, the msdbdata.mdf database is NOT being backed up.

I have a few questions. Could my msdbdata.mdf be corrupt? I have a successful backup of the msdbdata.mdf table from 9/10/2007. Can I just restore from that? Will there be any repercussions to any of the other database tables if I do?

Furthermore, should I just restore the msdbdata.mdf, master.mdf and the model.mdf? I really have no idea what these tables do and the repercussions of modifying them. I am not a DBA (obviously) and I just don't want to make an mistakes.

Thanks, CFDev.
Can you pls provide the details of the job history if any ? I guess you mean to say that msdb database backup fails if i interpret it correctly and Yes you can go ahead and restore the msdb.bak dated 9/10/2007 and check........the only issue is that all the jobs,dts packages created since 9/10 will not be available if you restore it.........I don't think its needed its enough if you restore msdb no need to restore master or model dbs..........
|||The "Job" is part of the databases default "maintenance plan". It consists of four parts:
1. DB backup
2. Integrity checks
3. Optimizations
4. Transaction Log Backup

This should be familiar to you, no? Anyway, would be okay to restore the msdb database then? How about the model and master databases? No tables, stored procedures, dts packages or anything has been changed. I just want peace of mind that if I were to restore any of these database tables, it would not effect any of my website's database tables. Make sense?

|||

Yes i am familiar with it if you feel there is no change done in msdb after 9/10 you can go ahead and restore msdb. Master stores all the login information and model acts as
template for other dbs so just restore msdb as it contains all the jobs and dts packages....I dont think restoring msdb would affect your db tables.......

|||

It would also be a good idea to run checkdb on all databases to make sure that they're clean.

You haven't made changes since 9/10, but you'd really rather not trip across hidden problems later.