| Author |
Topic |
|
wyx2000
Starting Member
5 Posts |
 Posted - 2010-11-15 : 19:20:44
|
| I have two sql servers, db 1 in server A is in suspect after restart the sql service on server A. The db 1 is a Principal db which is in mirroring with a db in server B.I tried to find solution to fix the suspect db. Here is what I tried1, update sys table to set the status of the db 1 to emergency. I failed to do it since SQL server 2005 doesn;t allow change to system table.2, I tried to ALTER DATABASE db1 SET EMERGENCY, I got "Database db1 cannot be opened. It has been marked SUSPECT by recovery."3, I then tried ALTER DATABASE ablebase SET PARTNER OFF, it tells me the db1 is in suspect, cannot change.Any idea? thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-16 : 00:28:11
|
| First. Do not detach that database. Do not restart the server.I would honestly suggest that you fail over the mirroring (force if necessary), though it should have failed over automatically if you're running in high safety. Take a backup of the now-principal, drop the suspect database and restore. You're in the fortunate situation that you have another copy of the DB.To see why it became suspect, check the SQL error log. There will be an entry in there saying why SQL marked the DB as suspect.--Gail ShawSQL Server MVP |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-16 : 00:29:42
|
quote:
Originally posted by tkizer
Have you tried sp_resetstatus?
quote:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER DATABASE instead.
The recommended way on SQL 2005+ is ALTER DATABASE SET EMERGENCY. sp_resetstatus should not be used any longer.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-16 : 02:17:09
|
| Have you guys ever actually had sp_resetstatus work? It usually just goes right back to suspect when I used to use it. Restore from backup would be the best course of action IMO.-Chad |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-16 : 03:08:13
|
quote:
Originally posted by tkizer
I find it very odd that MS doesn't just remove deprecated commands after two releases.
Their deprecation cycle is 2 releases. Deprecated in 2005, final deprecation in 2008. 2008 R2 wasn't a full release, so doesn't count. If they do follow that cycle, resetstatus should be gone in the next version.--Gail ShawSQL Server MVP |
|
|
|
wyx2000
Starting Member
5 Posts |
Posted - 2010-11-16 : 13:31:33
|
| Thanks for the replies.I tried sp_resetstatus, but it did nothing.Then I tried ALTER DATABASE db1 SET EMERGENCY and it says the db is in mirroring or something, cannot process.The error message in sql log isAn error occurred during recovery, preventing the database 'ablebase' (database ID 6) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.For fail over, I am using .NET 2, and I have four dbs for the CMS. Although I have them mirroring to another SQL server, but I don't have much confident on the auto fail-over, I remember something could trigger one db fail-over but not others, then make a mess. I remmber there are some articles about how to fail-over multi-db with some SQL commands, but I am not sure how it handle the in-time fail-over. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-17 : 02:32:16
|
| As the error says, you need to restore from a known good backup. This one does not look repairable.Arrange a maintenance window, shut down the app, fail all the databases over to the secondaries, restart the app then drop and restore the suspect one. If you don't have confidence in mirroring, why are you using it?--Gail ShawSQL Server MVP |
|
|
|
wyx2000
Starting Member
5 Posts |
Posted - 2010-11-17 : 19:53:59
|
| I don;t have other choice? do I?At least with mirroring I can have a real-time copy in another server.And SQL server 2005 cannot handle multi-db fail-over, right? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-18 : 00:44:17
|
| There's more than one options for HA. Depends on your needs, depends on your budget. Discussion for elsewhere.Mirroring (in any version of SQL) is per-database (as opposed to clustering which is per-server). If you want multiple databases to fail together, you need job/monitoring that fails the rest over should one go. My advice from the previous post still stands.--Gail ShawSQL Server MVP |
|
|
|
|
nightmare! how to handle db in suspect?