Matt Goedtel on Operations Management

Posts in this blog are provided "AS IS" with no warranties, and confers no rights as specified in the Terms of Use

Update to moving OperationsManager Database Steps

Update to moving OperationsManager Database Steps

  • Comments 8
  • Likes

It was discovered by some of my colleagues, that when they completed the steps to move the OperationsManager database from one SQL Server computer to another, several events are written to the Application Event Log.  An example of those events is:

Event Type:  Error
Event Source:  MSSQLSERVER
Event Category:  (2)
Event ID:  18054

Description:

Error 7779800008, 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.

This event is generated because when we documented the steps to move the OperationsManager database from one SQL Server computer to another in the Operations Manager 2007 Backup and Recovery Guide, we were not aware of the fact that some specific configuration information is stored in the Master database. 

You see during Operations Manager setup, after the OperationsManager database is created, error messages specific to Operations Manager are not tied to the database, but are stored in the sys.messages Catalog View in the Master database. 

We are in the process of updating the Operations Manager 2007 Backup and Restore Guide accordingly.  In the meantime, if you are preparing to perform this operation, you will need to execute the attached snippet of code.  This snippet of code will ensure that Error Messages specific to the OperationsManagerDB are available on the new SQL Server.  Open the attached file in SQL Server 2005 Query Designer and execute against the Master database.

This posting is provided "AS IS" with no warranties, and confers no rights.

Attachment: Fix_OpsMgrDB_ErrorMsgs.SQL.sql
Comments
  • PingBack from http://systemcenterforum.org/fix-update-to-steps-moving-the-operationsmanager-database/

  • Is this procedure still necessary?

  • Luis,

    I don't believe this is required any longer and the Operations Guide has the most up-to-date and accurate information.  

    If you find that is not the case, please let me know.

    Thanks,

    Matt

  • Hello Matt!   We recently did a DB move for SCOM OP and DW and we are still getting this error as of 6/15/09.  Granted, when we executed this procedure, we did it on the RTM version of SCOM.  Just wanted to drop a line and let you know.

    LC-J

  • Hi Matt,

    I'm getting an error as shown below:

    Error 778263, 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.

    U used your sql file and altered it so error 778263 is now also added.

    But i still don't know what kind of error this is. Can you help me?

  • Hello Matt

    I recently moved the OperationsManager  and OperationsManagerDW database due to a DR. The DBA could not move the Reporting Services Database (So I am getting a specific error stating SCOM cannot connect to the Reporting Database). Forgetting the Reporting Server database for a moment (unless it is related to my issue below). I have the following type or error as described in your post. My issue is I receive the following errors in the following order i.e. first, second etc

    Event ID 31561

    Failed to enumerate (discover) Data Warehouse objects and relationships among them. The operation will be retried.

    Exception 'SqlException': Error 777970008, 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.

    One or more workflows were affected by this.  

    Workflow name: Microsoft.SystemCenter.DataWarehouse.Discovery.StandardDataSet

    Instance name: Client Monitoring data set

    Instance ID: {77BF2D8B-DDAD-2240-E3EC-570CC68A626D}

    Management group: XXXX

    Event ID 31561

    Failed to enumerate (discover) Data Warehouse objects and relationships among them. The operation will be retried.

    Exception 'SqlException': Error 777970008, 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.

    One or more workflows were affected by this.  

    Workflow name: Microsoft.SystemCenter.DataWarehouse.Discovery.StandardDataSet

    Instance name: State data set

    Instance ID: {91134D4D-B1CE-CD48-C215-CD883816511E}

    Management group: XXXX

    Event ID 31561

    Failed to enumerate (discover) Data Warehouse objects and relationships among them. The operation will be retried.

    Exception 'SqlException': Error 777970008, 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.

    One or more workflows were affected by this.  

    Workflow name: Microsoft.SystemCenter.DataWarehouse.Discovery.StandardDataSet

    Instance name: Performance data set

    Instance ID: {56EACCD9-8AD9-D50B-2E59-9B697731F893}

    Management group: XXXX

    Event ID 31561

    Failed to enumerate (discover) Data Warehouse objects and relationships among them. The operation will be retried.

    Exception 'SqlException': Error 777970008, 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.

    One or more workflows were affected by this.  

    Workflow name: Microsoft.SystemCenter.DataWarehouse.Discovery.StandardDataSet

    Instance name: Alert data set

    Instance ID: {4D6765C1-5C91-B99B-38B6-D1367AB45B74}

    Management group: XXXX

    The DBA did not recovery the Master Databae I believe, rather they attached the OperationsManager and OperationsManagerDW databases to an already installed instance

    I have SCOM 2007 R2 CU3 and SQL 2008

    Does your script still apply to my environment? (particularly as the DBA did not recover the maste database)

    Will your Script work on SQL 2008? (as it states SQL 2005)

    Please let me know, Thanks in advance

    Ernie

    ErnestBrant@Hotmail.co.uk

  • Hello Again

    Apart from the above, can you please clarify do I run the SQL script on the OperationsManager or the OperationsManagerDW database?

    Thank you

    Ernie

    ErnestBrant@Hotmail.co.uk

  • Hi Matt

    We moved the infrastructure of the Service Manager DB (DW, transactional and Portal) and after this movement we present this issue. I'm wandering if the script works for Service Manager 2012.

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