March 18, 2005 at 3:59 pm
Hi,
I had database in suspect mode since yesterday. Somehow no rollback connection. I have one week back backup copy. I reset the status and put it in emergency mode to run dbcc checkdb . Now can not come back from emergency status. What should I do?
Appreciate your help.
Tx
March 19, 2005 at 1:35 am
Firts of all, what is the reason for the database to be in suspect mode? check sql server error logs, have you tried to restart the server? are there error messages on startup?
March 21, 2005 at 3:16 am
Forget emergency mode - see if you can get it back online by changing the status and then do a backup. Otherwise stop the SQL services and take a copy of the mdf and ldf (just in case)
March 21, 2005 at 7:26 am
March 21, 2005 at 5:47 pm
Following are the error messages:
SQL Server log has following error messages:
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
***********************
The Scheduler 0 appears to be hung. SPID 52, ECID 0, UMS Context 0x037B9C80.
******************
Event log : Application : at 9:26 am:
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 60 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
************************
Event log : System : at 9:28 am:
System Information Agent: Health: Correctable memory error detected. The errors have been corrected, but the memory module should be replaced.
Board or cartridge: '0' Module: '5' Spare part number: '' Module size: '524288' System id: 'CPQ0712'
[SNMP TRAP: 6056 in CPQHLTH.MIB]
Steps were taken to correct the situation:
After rebooting the server, db was still in emergency mode.
Reset the status of the db.(No change)
DB set to emergency mode and then set to single user mode and then tried to run
Dbcc checkdb (‘dbname’, REPAIR_REBUILD ): It did not work.
After leaving db in emergency mode, I was able to DTS 70% of data to an empty database.
Restored backup copy from earlier date (Two days earlier) backup and restored some data from empty db. (One day earlier backup was 0 bytes in size and notification was somehow automatically turned off by the system.)
We still lost about half day of worth of work.
What is the best way to handle this type of situation?
Thanks
March 22, 2005 at 1:06 am
From what you posted I understand that it was a memory-related problem that brought down the server. What was the output of running DBCC CHECKDB (without repair clauses) ? Was there a corruption reported from db? This is decisive factor.
By putting db into emergency mode, extracting data and merging it with data from old backup you took a hard road. Much easier and cleaner would be to perform full backups on for example nightly basis, and during the day perform log backups as often as needed (hourly, every 10 minutes etc.) Then you would just restore last full backup, archived log backups and possibly active log (if available) up to the point of failure without worries about db consistency. The good practice is to check backup processes and restore regularly on test machines as well as to run consistency checks on regular basis.
March 22, 2005 at 6:34 am
Windows 2000: SQL Server 2000: SP3
DB size: about 5 GB
dbcc checkdb was not able to read pages. I had nightly full backup at(3:30 am)and tran backup every 3 hours (Between 6:30 am and 6:30 pm) scheduled on this database. DBs are not mission critical dbs on this server. This db has very few transaction per day (600-800 tran/day). Scheduler was stop suddenly and that's the reason we did not have full backup or tran backup for more than a day. Even today, I can not run optimization for any of the other three databases. If I run optimization on any of the three databases, Db goes into suspect mode right away and optimization doesn't complete. We are in process to change the memory chip.
Tx
March 22, 2005 at 7:02 am
From my point of view, when situation like this arised (no recent backups, server failure, db corruption), you probably did all that was possible. But I would wait for the memory to be fixed and then try running consistency checks again if it's possible.
Just one thought: If there was a chance that your transaction log file was intact you could have taken the log file, create dummy db and perform log backup as described in this ms support article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;253817
since it should contain all transactions since the last log backup you might apply this to the last full backup you have and recover all changes to the point of failure, but this might be tricky and there's no guarantee that it'll work in your situation. (mainly after all actions taken since the failure)
March 22, 2005 at 7:46 am
Another possibility is to shut down the SQL Server Service, take a physical copy of the .mdf and .ldf and rename them.
Bring up the SQL service and try attaching the DB as another name. The attach process will try to make it consistent. Then you can rebuild the orig database and do a DTS between the two.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply