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 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 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 therefore, the unique clustered index -- must contain IGNORE_DUP_KEY. This 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)
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 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 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 therefore, the unique clustered index -- must contain IGNORE_DUP_KEY. This 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)
SC2012_OpsMgr_Deployment.docx