ChrisTitus.com Resources for IT Professionals

System Center Essentials 2007 OperationsManager SQL DB too large (4GB +)

I run System Center Essentials 2007 and by default it installs into SQL Server Express on EBS Installations. This is normally not an issue, but if the database grows larger than 4GB than you will no longer get the benefit of SCE 2007 as SQL Server Express has a 4GB limitation on its databases. I noticed that my database was growing considerably and before I knew it my OperationsManager DB was larger than 4GB.

First, I groomed the database and reduced all retention periods via the EBS teams SCE 2007 blog post. For instructions on how to groom SCE 2007, here is their blog post. This outlined how to reduce the retention period of various SCE 2007 buckets in both SQL Server Management Studio and SCE 2007 Administrative Console.

Second, I used shrink on the OperationsManager and OperationsManagerDW Databases in SQL Management Studio. This is done via SQL Query or by Right Clicking Database -> Tasks -> Shrink -> Database. On next screen leave options default and hit OK.

These both helped me considerably, but I noticed the OperationsManager was still quite large. I found a solution on Experts Exchange that finally reduced the Database well below the 4GB limit. There is aparently a bug in SCE 2007 SP1 that bloats the dbo.LocalizedText table to several gigs if left unchecked for a year. Here is a complete Solution step-by-step.

Step 1: Create a Backup of the Database
-Right-Click OperationsManager -> Tasks -> Back Up...

Step 2: Verify you have a large dbo.LocalizedText table
-Run the Following SQL Query on OperationsManager. This is also great for checking other SQL Databases to see which table is eating up all the space.


USE OperationsManager
DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL,
row varchar(10),
reserved varchar(10),
data varchar(10),
index_size varchar(10),
unused varchar(10)
)
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT name FROM sys.Tables
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
INSERT INTO @TblNames EXEC sp_spaceused @str
SET @I = @I +1
END
-- Display results in Sorted order
SELECT tbl_name as TableNm, CAST(REPLACE(data, ' kb', '') as int) as TblSize,
CAST(REPLACE(index_size, ' kb', '') as int) as IdxSize
FROM @tblNames ORDER BY TblSize DESC


Step 3: Clean Up Localized Text
-Run the following SQL Query on OperationsManager. (This will take about 10 minutes per gig.)

-
- Create temp table to speed up looking for a PublisherId when we 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 temp table of message lengths, message id, and Message Hash with the
-- Message String Id so that we can efficiently figure out whether a given message
-- is duplicated. The duplicate messages generated by the converted MP have
-- different PublisherId's, but everything else is identical. INDEX this TABLE so that
-- we can look up quickly by the MessageStringId and also by values we expect to see
-- duplicated.
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 temp table for the Orphaned PublisherStrings that we find. These
-- are rows in PublisherMessages whose corresponding Events have already been groomed away.
-- They still have corresponding rows in LocalizedText. We won't add rows for PublisherMessages
-- which are not for a duplicated message.
CREATE TABLE #OrphanedPublisherStrings (PublisherId UNIQUEIDENTIFIER,
MessageStringId UNIQUEIDENTIFIER)
CREATE CLUSTERED INDEX #OrphanedPublisherStrings_CI ON #OrphanedPublisherStrings(MessageStringId)
-- Create temp table to use in looking up whether a PublisherMessages row still
-- has a corresponding Event. Event_01 etc. have no index on PublisherId, so we
-- don't want to do a query that keeps seeking into EventAllView.
-- If a PublisherId occurs multiple times in the Event tables we will only need it
-- once in our temp table, hence the unique clustered index with IGNORE_DUP_KEY.
-- This keeps the temp table relatively small and will be a time saver for
-- seeing which PublisherMessages are orphaned.
CREATE TABLE #EventAllPublishers (PublisherId UNIQUEIDENTIFIER)
CREATE UNIQUE CLUSTERED INDEX #EventAllPublishers_CI ON #EventAllPublishers (PublisherId)
WITH (IGNORE_DUP_KEY = ON)
-- Populate temp table by scanning EventAllView one time
INSERT INTO #EventAllPublishers(PublisherId)
SELECT PublisherId
FROM EventAllView
-- Populate first Temp table for figuring out 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 second table for figuring out 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 INDEX of
-- the first one and doing a grouped count.
INSERT INTO #LTCountByMessage (LTValueLen, MessageId, LTValueHash, MsgCount)
SELECT LTValueLen, MessageId, LTValueHash, COUNT(1)
FROM #LTHashStrings
GROUP BY LTValueLen, MessageId, LTValueHash
-- Now that we are set up to detect both Orphans and duplicated messages by
-- joining to our relatively small (and properly indexed) temp tables,
-- figure out 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 of the OrphanedPublisherStrings and corresponding LocalizedText rows
-- at once may be too large for the transaction log to handle. Create a
-- numbered / ordered table so that we 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 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 we can delete our orphaned rows
-- If transaction log fills up, try reducing the @OrphanIncrement value,
-- which controls the number of rows that we delete at a 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 of the Temp 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


Step 4: Reindex Database and finish
-Run the following SQL Query on the OperationsManager.

USE OperationsManager
go
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
EXEC SP_MSForEachTable "Print 'Reindexing '+'?' DBCC DBREINDEX ('?')"