SCOM 2007 OperationsManagerDW (Emergency) Issue

SQL Server Management Studio reports a "OperationsManagerDW (Emergency)" status.  The Application Event Log lists: 211, 608, 17310, 3414, 3313, 324, 18056... Event IDs.

Running DBCC CHECKDB and DBCC CHECKCATALOG (have no idea what these do) does not seem to help.  The volume containing the 'tempdb.mdf' and 'templog.ldf' has ~26 GB of available diskspace, with both set for Autogrowth by 10%, unrestricted growth.

All SQL services (Automatic) start successfully after a reboot.ERRORLOG.1-justBeforeReboot.txtERRORLOG-rightAfterReboot.txt

Knowing very little about the inner workings of SQL Server 2008 R2 (v10.50.1617), and just slightly more about SCOM, I'd appreciate any assistance.
ffincAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
Three reason 1.files are missing from the drive 2. drive is missing from the server 3. Drive is currupted and can not be read by SQL server.

Now connect SQL server and execute this query and check the files exists at the location mentioned in physical_name column

select database_id, DB_NAME(database_id), name, physical_name from sys.master_files
where database_id=DB_ID('OperationsManagerDW')

Open in new window

ffincAuthor Commented:
Thanks for getting back to me.

Both files exist in the data paths returned by the query, and have a time stamp of: 6/25/2015/4:30 pm.
Deepak ChauhanSQL Server DBACommented:
Can you share the sql server error log when this database.

execute this query and share the result.

SELECT    [state_desc] , log_reuse_wait_desc FROM    sys.databases
WHERE    [name] = N'OperationsManagerDW';

execute this command and share the result.
Execute DBCC CheckDB(OperationsManagerDW)
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ffincAuthor Commented:
SELECT    [state_desc] , log_reuse_wait_desc FROM    sys.databases
 WHERE    [name] = N'OperationsManagerDW';

>> Results TAB
state_desc = EMERGENCY | log_reuse_wait_desc = NOTHING

 execute this command and share the result.
 Execute DBCC CheckDB(OperationsManagerDW)

>> Messages TAB
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Deepak ChauhanSQL Server DBACommented:
Do you have last one good backup of this database.
ffincAuthor Commented:
Negative
Deepak ChauhanSQL Server DBACommented:
Try to put this database in multiuser mode.

First try to make it online.

alter database OperationsManagerDW set Multi_user 

Open in new window


Else this needs to be repair.
ffincAuthor Commented:
Result of query:  alter database OperationsManagerDW set Multi_user

>> Message TAB
Command(s) completed successfully.
Deepak ChauhanSQL Server DBACommented:
now check the database emergency mode should not be there.

You can execute this query  and result should be (OperationsManagerDW | Online)

select name, state_desc from sys.databases
 WHERE    [name] = N'OperationsManagerDW';
Deepak ChauhanSQL Server DBACommented:
if the database not comes online then take it offline and bring online again

alter database OperationsManagerDW set offline with rollback immediate
go
alter database OperationsManagerDW set online with rollback immediate

the above steps for bring the database without repair. If database does not comes online only option is repair OperationsManagerDW DB

Since you dont have backup only option is repair the database
ffincAuthor Commented:
select name, state_desc from sys.databases
  WHERE    [name] = N'OperationsManagerDW'

>> Results TAB
name OperationsManagerDW | state_desc SUSPECT

DB switched to SUSPECT mode after taking offline and bringing back online
ffincAuthor Commented:
alter database OperationsManagerDW set online with rollback immediate

>> Messages TAB

Msg 926, Level 14, State 1, Line 1
Database 'OperationsManagerDW' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 926, Level 14, State 1, Line 1
Database 'OperationsManagerDW' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x23d83c80; actual: 0x8e7952c9). It occurred during a read of page (1:36591) in database ID 8 at offset 0x00000011dde000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\OperationsManagerDW.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database 'OperationsManagerDW', an error occurred at log record ID (908:18336:100). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'OperationsManagerDW' (database ID 8) 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.
Deepak ChauhanSQL Server DBACommented:
Alright page (1:36591) is currupted recovery require multiple steps

1. Take the database offline

Alter database OperationsManagerDW set offline.

2. Since you dont have backup so we have to work only on this database with recovery. Now copy the database file to another location Just copy dont move.

These files are ('D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\OperationsManagerDW.mdf') and .LDF

3. Alter database OperationsManagerDW set online.

4. run the below statement.

Alter database OperationsManagerDW set emergency
go
Alter database OperationsManagerDW set single_user with rollback immediate

go

DBCC TRACEON(3604)
go
DBCC PAGE('OperationsManagerDW',1,36591,3) WITH TABLERESULTS
GO

Open in new window


check which table or index is currupted.
Now copy the result of DBCC page command into file.

Next step will be this to repair the currupted page. but before that check the DBCC page command result.

DBCC CHECKDB (N'OperationsManagerDW', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
ffincAuthor Commented:
>> Ran
DBCC CHECKDB (N'OperationsManagerDW', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;

>> Message TAB
Warning: The log for database 'OperationsManagerDW' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
ffincAuthor Commented:
Thanks for your help so far.  I need to step away, so I'll check for updates over the weekend.
Deepak ChauhanSQL Server DBACommented:
No problem.

Just make sure TempDB drive should have enough space.

DBCC CHECKDB (N'OperationsManagerDW', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS, tablock;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Deepak ChauhanSQL Server DBACommented:
Hi ffinc, have you done with recovery or still need help. Please feel free to reach us any time.
ffincAuthor Commented:
Hello,

While the OperationsManagerDW database appears to be fixed (thanks for that), I'm now having similar issues with the OperationsManager SCOM 2007 database.  

Over the last few days I've tried running the same steps already provided earlier-on in this thread -- Without much success, and would appreciate additional guidance.
ffincAuthor Commented:
Thanks for the help Deepak.  While the OperationsManagerDW database appears to have been repaired successfully.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.