March 10, 2010 at 7:08 pm
Hello friends,
This morning one of my DB was marked as Suspect and I'm at a lost to fix it. I first noticed something was wrong because i started to receive replication email errors. I noticed that the Publication was missing from 'Local Publication' under replication. Lookin in the log the error tells me to either repair or restore with a full backup.
I first tried to recover the DB with the following code
Sp_configure 'allow updates', 1
Reconfigure with override
update master..sysdatabases SET status = status ^ 256 where name = 'mydbname'
And i received the following Error
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
I then tried to then put it into emergency mode and received the same error using the followin code
update master..sysdatabases set status = status | 32768 where name = 'mydbname'
So then i deiced to just restore from a backup, BUT because its in Suspect mode i can not restore over it. I can not delete it or detach it because it in replication even thou i can not drop the publication because its not showing up under Local Publication nor does sp_dropmergepublication work because the whole suspect db issue. So i was wondering if anyone could suggest a solution or way around so i can drop the db and restore the backup
Cheers 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
March 10, 2010 at 10:22 pm
The solution to my problems was to fix the database in suspect mode.
Note only use this to grant access to the db so you can drop it and accompanying replicated publications, this script will delete any data within the db that is associated with the corrupt allocation table.
EXEC sp_resetstatus 'dbname'
ALTER DATABASE dbname SET EMERGENCY
DBCC checkdb('dbname')
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('dbname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE dbname SET MULTI_USER
Enjoy :smooooth:
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
March 11, 2010 at 1:32 am
That's kinda the last resort to fixing corruption.
If you have clean backups you should, now that you can remove replication, restore the clean backups so that you haven't lost an unknown amount of data.
Don't suppose you ran CheckDB without a repair option first to see exactly what was wrong?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2010 at 1:40 am
Very true, because i was using merge replication i could point the web site to another server and not have any downtime, so my only concern was to create a quick fix. The problem was allocation within the transaction log.
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
March 11, 2010 at 1:51 am
Nasty.
You should do some root-cause analysis of this, otherwise it may happen again. See if there were any errors logged around the time that the DB went suspect. SQL error log, windows event log, RAID/SAN logs, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2010 at 2:09 am
Yeah the server is a bit old and is due to be replace in a few months, the event log didn't suggest anythin out of the blue, just runnin a disk scan to see if anythin wrong with the HD
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply