Menu Close

SCOM 2016, 2019 and 2022: Event 18054 errors in the SQL application log

 

image

Quick Download:   https://github.com/thekevinholman/SQLFix18054EventsSysmessages

When SCOM is installed – it doesn’t just create the databases on the SQL instance – it adds data to the sysmessages view for different error scenarios, to the Master database for the instance.

This is why after moving a database, or restoring a DB backup to a rebuilt SQL server, or when using SQL Always On and failing over to another instance – we might end up missing this data.

These are important because they give very good detailed data about the error and how to resolve it.  If you see these – you need to update your SQL instance with some scripts.  Or – if you KNOW you are using SQL Always On, or migrating a DB – be PROACTIVE and handle this manually, up front.

 

Examples of these events on the SQL server:

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..

I have created some SQL scripts which are taken from the initial installation files, and you can download them below.  You simply run them in SQL Management studio to get this data back.

 

These are for SCOM 2016, 2019, and 2022 versions

There is one file to run for the SQL instance hosting the Ops DB and a different file to run for the SQL instance hosting the Data Warehouse DB.

Download link:   https://github.com/thekevinholman/SQLFix18054EventsSysmessages

 

23 Comments

    • Kevin Holman
      Reply

      No – it only needs to be run ONCE, on any instance that *might* host the DB. So if there are two SQL servers replicating the database, it needs to be run on each SQL server, ONE time. The script simply adds messages into the master DB, which are specific to SCOM issues.

    • Kevin Holman
      Reply

      IF you know which server was “active” at the time you installed SCOM, then this only needs to be run on the “other” server in the AG. That said – it doesn’t hurt to run it on both, so I usually do just to be sure.

  5. Ioana
    Reply

    Hi Kevin,

    I saw you mentioned that these SQL scripts applies only for SCOM 2016 and 2019, but what can be the solution if those events are shown in SCOM 2012 R2 as well?

    Thank you

  7. Ollie Woodall
    Reply

    Hi Kevin. When executing this script in SQL Studio, does it matter if you have the OperationsManagerDW / OperationsManager DB selected as the DB? Or should you select Master?

  15. Christian
    Reply

    Hi Kevin,

    Jan posted up there that he has event 777971007, I have the same problem with 777971006… they’re just not in the list. Is there any solution for that? My PFE (or whatever you’re called these days) said that he found several long running tickets, but no solution…

    Thanks for all the great stuff you’re developing and posting here, it has been a great help for me, and I’m sure for lots and lots of other SCOM admins as well.

Leave a Reply

Your email address will not be published.