Event 18054 Logged by SQL After OpsMgr Data Warehouse DB Move/Restore

Couple of weeks ago, we had to completely rebuild a SQL server hosting OpsMgr 2012 R2 Data Warehouse DB (reinstall OS, SQL, etc). After I restored the OperationsManagerDW database from the backup, the following error was logged to the Application Event log by SQL every minute:

Error 777971002, severity 14, state 10 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.

Many people have already blogged this issue after moving / restoring the OpsMgr Operational DB (OperationsManager). i.e.

http://blogs.technet.com/b/kevinholman/archive/2010/10/26/after-moving-your-operationsmanager-database-you-might-find-event-18054-errors-in-the-sql-server-application-log.aspx

http://blogs.technet.com/b/mgoedtel/archive/2007/08/06/update-to-moving-operationsmanager-database-steps.aspx

but I could find any solutions for the Data Warehouse DB. Luckily, this article from Marnix Wolf pointed me to the right direction:

Moving the OM12 Operations Database: Don’t Forget The Master Database

Basically, Marnix managed to find the SQL script from the installation media to create the custom SQL messages to the master DB.

After only few minutes, I managed to find the section of the SQL script to create these SQL messages for the Data Warehouse DB from the OpsMgr 2012 R2 install media as well.

the SQL script is located at: “<Install media>\setup\AMD64\Datawarehouse.Initial.Setup.sql”

the section I need to run is at the very end of this long SQL script (starting from line 17931 to the end of the script, which is line 18055)

I copied and pasted this section into SQL management studio and executed it against the master database. This has stopped the Event 18054 in the application log.

Since this SQL script is an intellectual property of Microsoft, I won’t post it in this article. You should be able to easily find it from the installation media.