Post-Upgrade Tasks when Upgrading from Operations Manager 2007 R2

The following table shows the tasks that you need to complete after you have upgraded to System Center 2012 – Operations Manager. It also indicates when to perform the task.

Run the following SQL query on the Operational database in each management group to clean up the Localizedtext table and the Publishmessage table.

-- Create a temporary table to quickly find a PublisherId when you know the MessageId.
BEGIN TRY
CREATE TABLE #PublisherMessageReverseIndex(MessageStringId UNIQUEIDENTIFIER, 
   MessageId INT)
CREATE CLUSTERED INDEX #PublisherMessageReverseIndex_CI ON #PublisherMessageReverseIndex(MessageStringId)
INSERT INTO #PublisherMessageReverseIndex (MessageStringId, MessageId)
SELECT MessageStringId, MessageId
FROM dbo.PublisherMessages

-- Create a temporary table of message lengths, message IDs, and message hashes with the
-- MessageStringId to quickly determine whether a message is duplicated. Index the table. 

CREATE TABLE #LTHashStrings (MessageStringId UNIQUEIDENTIFIER, 
 LTValueLen INT, 
 LTValueHash VARBINARY(32),
 MessageId INT NULL)
CREATE CLUSTERED INDEX #LTHashStrings_CI ON #LTHashStrings(MessageStringId)
CREATE NONCLUSTERED INDEX #LTHashStrings_NCI1 ON #LTHashStrings(LTValueLen, MessageId, LTValueHash)

-- Create a temporary table for the orphaned PublisherStrings that you find. Orphaned PublisherStrings 
-- are rows in PublisherMessages whose corresponding events have already been groomed. They still
-- have corresponding rows in LocalizedText.  Do not add rows for PublisherMessages; they are not
-- for duplicated messages.

CREATE TABLE #OrphanedPublisherStrings (PublisherId UNIQUEIDENTIFIER, 
MessageStringId UNIQUEIDENTIFIER)
CREATE CLUSTERED INDEX #OrphanedPublisherStrings_CI ON #OrphanedPublisherStrings(MessageStringId)

-- Create a temporary table so that you can determine whether a PublisherMessages row still
-- has a corresponding event. These events do not have an index on the PublisherId, so do 
-- not query the EventAllView. If a PublisherId occurs multiple times in the event tables,
-- it is only needed one time in the temp table; therefore, the unique clustered index
-- must contain IGNORE_DUP_KEY. This keeps the temporary table relatively small and saves
-- time when you want to see the orphaned PublisherMessages.

CREATE TABLE #EventAllPublishers (PublisherId UNIQUEIDENTIFIER)
CREATE UNIQUE CLUSTERED INDEX #EventAllPublishers_CI ON #EventAllPublishers (PublisherId)
WITH (IGNORE_DUP_KEY = ON)

-- Populate the temporary table by scanning EventAllView one time.
INSERT INTO #EventAllPublishers(PublisherId) 
SELECT PublisherId 
FROM EventAllView

-- Populate the first temporary table to determine which messages are duplicated.
INSERT INTO #LTHashStrings (MessageStringId, LTValueLen, LTValueHash, MessageId)
SELECT LTStringId, len(LTValue), HashBytes('SHA1', LTValue), MessageId
FROM dbo.LocalizedText LT 
JOIN #PublisherMessageReverseIndex PM ON PM.MessageStringId = LTStringId

-- Create the second table to determine which messages are duplicated.  
CREATE TABLE #LTCountByMessage( LTValueLen INT, 
MessageId INT, 
LTValueHash VARBINARY(32), 
MsgCount INT)
CREATE CLUSTERED INDEX #LTCountByMessage_CI ON #LTCountByMessage(LTValueLen, MessageId, LTValueHash)

-- Populate second message for duplicate message detection by scanning the INDEX of
-- the first one and by doing a grouped count.
INSERT INTO #LTCountByMessage (LTValueLen, MessageId, LTValueHash, MsgCount)
SELECT LTValueLen, MessageId, LTValueHash, COUNT(1) 
FROM #LTHashStrings
GROUP BY LTValueLen, MessageId, LTValueHash

-- You are now set up to detect both orphaned PublisherStrings and duplicated messages
-- by joining to our relatively small (and correctly indexed) temporary tables.
-- Determine the OrphanedPublisherStrings that have duplicate messages.
INSERT INTO #OrphanedPublisherStrings (PublisherId, MessageStringId)
SELECT PM.PublisherId, PM.MessageStringId 
FROM dbo.PublisherMessages PM 
JOIN #LTHashStrings LTS ON (LTS.MessageStringId = PM.MessageStringId AND LTS.MessageId = PM.MessageId)
JOIN #LTCountByMessage LTC ON (LTC.LTValueLen = LTS.LTValueLen AND
LTC.MessageId = LTS.MessageId AND LTC.LTValueHash = LTS.LTValueHash)
WHERE PM.PublisherId NOT IN (SELECT PublisherId FROM #EventAllPublishers) AND
LTC.MsgCount > 1

-- Deleting all the OrphanedPublisherStrings and all the corresponding LocalizedText rows
-- at one time may be too large for the transaction log to handle.  Create a numbered
-- or ordered table so that you can delete them in relatively small batches and not
-- overtax the transaction log.
CREATE TABLE #NumberOrphanPublisherStrings(OrphanNum INT IDENTITY,
   PublisherId UNIQUEIDENTIFIER, 
   MessageStringId UNIQUEIDENTIFIER)
CREATE CLUSTERED INDEX #NumberOrphanPublisherStrings_CI on #NumberOrphanPublisherStrings(OrphanNum)

-- Populate the numbered table.
INSERT INTO #NumberOrphanPublisherStrings (PublisherId, MessageStringId)
SELECT PublisherId, MessageStringId FROM #OrphanedPublisherStrings
END TRY
BEGIN CATCH
GOTO Error
END CATCH

-- Set up variables so that you can delete the orphaned rows.
-- If the transaction log fills up, try to reduce the @OrphanIncrement value,
-- which controls the number of rows that are delete at the same time.
DECLARE @OrphanNum INT
DECLARE @OrphanIncrement INT
DECLARE @OrphanLimit INT
SET @OrphanNum = 0
SET @OrphanIncrement = 10000
SELECT @OrphanLimit = MAX(OrphanNum) FROM #NumberOrphanPublisherStrings
BEGIN TRY
WHILE @OrphanNum < @OrphanLimit
BEGIN
DELETE dbo.LocalizedText FROM
#NumberOrphanPublisherStrings OPS JOIN dbo.LocalizedText LT
ON LT.LTStringId = OPS.MessageStringId
WHERE OPS.OrphanNum >= @OrphanNum AND OPS.OrphanNum < @OrphanNum + @OrphanIncrement
DELETE dbo.PublisherMessages FROM
#NumberOrphanPublisherStrings OPS JOIN dbo.PublisherMessages PM
ON PM.PublisherId = OPS.PublisherId
WHERE OPS.OrphanNum >= @OrphanNum AND OPS.OrphanNum < @OrphanNum + @OrphanIncrement
SET @OrphanNum = @OrphanNum + @OrphanIncrement
END
END TRY
BEGIN CATCH
GOTO Error
END CATCH

Error:
IF @@ERROR <> 0
   SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;

-- Try to drop all the temporary tables
BEGIN TRY
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#PublisherMessage%')
DROP TABLE #PublisherMessageReverseIndex
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#OrphanedPublisherStrings%')
DROP TABLE #OrphanedPublisherStrings
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#LTHashStrings%')
DROP TABLE #LTHashStrings
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#EventAllPublishers%')
DROP TABLE #EventAllPublishers
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#LTCountByMessage%')
DROP TABLE #LTCountByMessage
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#NumberOrphanPublisherStrings%')
DROP TABLE #NumberOrphanPublisherStrings
END TRY
BEGIN CATCH
   SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH

After completing the upgrade, UNIX/Linux agents must be assigned to a resource pool to enable highly-available monitoring and agent administration. For more information on creating resource pools, see How to Create a Resource Pool.