Kevin Holman's System Center Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

After moving your OperationsManager Database–you might find event 18054 errors in the SQL server application log

After moving your OperationsManager Database–you might find event 18054 errors in the SQL server application log

  • Comments 13
  • Likes

I recently wrote about My Experience Moving the Operations Database to New Hardware

Something I noticed today – is that the application event log on the SQL server was full of 18054 events, such as below:

Log Name:      Application
Source:        MSSQL$I01
Date:          10/23/2010 5:40:14 PM
Event ID:      18054
Task Category: Server
Level:         Error
Keywords:      Classic
User:          OPSMGR\msaa
Computer:      SQLDB1.opsmgr.net
Description:
Error 777980007, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

You might also notice some truncated events in the OpsMgr event log, on your RMS or management servers:

Event Type:    Warning
Event Source:    DataAccessLayer
Event Category:    None
Event ID:    33333
Date:        10/23/2010
Time:        5:40:13 PM
User:        N/A
Computer:    OMMS3
Description:
Data Access Layer rejected retry on SqlError:
Request: p_DiscoverySourceUpsert -- (DiscoverySourceId=f0c57af0-927a-335f-1f74-3a3f1f5ca7cd), (DiscoverySourceType=0), (DiscoverySourceObjectId=74fb2fa8-94e5-264d-5f7e-57839f40de0f), (IsSnapshot=True), (TimeGenerated=10/23/2010 10:37:36 PM), (BoundManagedEntityId=3304d59d-5af5-ba80-5ba7-d13a07ed21d4), (IsDiscoveryPackageStale=), (RETURN_VALUE=1)
Class: 16
Number: 18054
Message: Error 777980007, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Event Type:    Error
Event Source:    Health Service Modules
Event Category:    None
Event ID:    10801
Date:        10/23/2010
Time:        5:40:13 PM
User:        N/A
Computer:    OMMS3
Description:
Discovery data couldn't be inserted to the database. This could have happened because  of one of the following reasons:

     - Discovery data is stale. The discovery data is generated by an MP recently deleted.
     - Database connectivity problems or database running out of space.
     - Discovery data received is not valid.

The following details should help to further diagnose:

DiscoveryId: 74fb2fa8-94e5-264d-5f7e-57839f40de0f
HealthServiceId: bf43c6a9-8f4b-5d6d-5689-4e29d56fed88
 Error 777980007, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage..

 

After a little research – apparently this is caused when following the guide to move the Operations Database to new hardware. 

Marnix blogged about this issue http://thoughtsonopsmgr.blogspot.com/2009/02/moving-scom-database-to-another-server.html which references Matt Goedtel’s article http://blogs.technet.com/b/mgoedtel/archive/2007/08/06/update-to-moving-operationsmanager-database-steps.aspx

 

Because in this process – we simply restore the Operations Database ONLY, we do not carry over some of the modifications to the MASTER database that are performed when you run the Database Installation during setup to create the original operations database.

For some OpsMgr events, which stem from database activity, we get the event data from SQL.  If these messages do not exist in SQL – you see the above issue.

What is bad about this – is that it will keep some event rules from actually alerting us to the condition!  For instance – the rule “Discovery Data Submission Failure” which will alert when there is a failure to insert discovery data – will not trigger now, because it is looking for specific information in parameter 3 of the event, which is part of the missing data:

 

image

 

To resolve this – we need to add back the missing information into the MASTER database. 

  • IF you have moved your OperationsManager database to new hardware

AND:

  • IF you are seeing event 18054 events in the application log of the OpsDB SQL instance server.

Then you are impacted.  To resolve this – you should run the attached SQL script against the Master database of the SQL instance that hosts your OperationsManager Database.  You should ONLY consider running this if you are 100% sure that you are impacted by this issue.

See attached:  Fix_OpsMgrDB_ErrorMsgs.sql

Attachment: Fix_OpsMgrDB_ErrorMsgs.sql
Comments
  • Would another option be to run the OpsMgr installer on the new SQL server to only install the OperationsManager database and then restore your backup over the top of newly created OperationsManager database?

  • @Mike -

    Absolutely.  That would work fine, as would dropping the newly created DB first and then just restoring to a new DB.

  • Kevin,

    This only works if you have a clustered DB.   We simply added our new hardware as new nodes to the db cluster, then evicted the old hardware from the cluster.  This made for a very smooth transition by avoiding a new SQL server name and some of the difficulties with the Master Database outlined here.

  • That's a really cool idea, if you have a cluster and can retain the same cluster names... for sure.

  • Kevin,

    I am seeing these errors on my Data Warehouse DB after moving it. Is there a similar query to run for the DW?

    Brian

  • @Bhansen - can you be more specific or provide more examples?

  • Hi Kevin,

    Im also seeing these errors on both the sql server and the RMS but the strange thing is we have not moved the DB, would this script resolve this issue.

    Phil

  • I ran into the same problem, but your fix didn't help me. I used dbcreatewizard to fix the problem:

    www.normanbauer.com/.../error-18054-and-error-777971002-in-sql-log-after-moving-system-center-operations-manager-2007-database

    Thanks anyway...

  • I know this is an older post, but I'm getting ready to perform this action as well and was wondering if just backing up MASTER and restoring it to the new server would be ok?

    social.technet.microsoft.com/.../4fe22f94-086a-415c-9a6a-5f8a503e0894

  • Anyone saw issue like this:

    Class: 16

    Number: 777980008

    Message: Health service ( (null) ) should not generate data about this managed type ( Microsoft.SystemCenter.DataWarehouse.DataSet ) object Id (R245686A0-7720-2725-0BD1-1C9G53132429 ).

    problem is :Health service ( (null) )  where to find this "null" and why this is showing "null" after restore I started to get this kind of alerts hmm

    Anyone have idea how to troubleshooot this?

  • "Michael D" I'm facing the same problem with "Health service ( (null) )", it looks like alert is pointing to non existing healthservice. If you will check alert context in console you will find HealthService ID but it not exist in SCOM.

  • This issue is Still present in SCOM 2012 R2 Does anybody what is the sql script to fix that issue

  • I am getting the following Error in SCOM 2012 R2 -- the BE is SQL 2012 SP1 ----Exception: Error 777971002, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage. I have the SQL Statement but this did not resolve the issue ..any idea what could be Wrong in here

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs