• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6842
  • Last Modified:

Operations Manager Database full (Management Server)

Hi all,

In the event log of the Essential Business Server's Management server, I noticed loads of errors coming from the MSSQL$SCE (System Center Essential's Operations Manager DB). Basically the errors are always the same like:

"Could not allocate space for object 'dbo.Event_51'.'idxc_Event_51_TimeAdded' in database 'OperationsManager' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."

and

"CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database."

It seems that the DB has become too big. As far as I know the EBS Management Server is using the Desktop version of SQL 2005. What would you recommend to fix this?

- can I shrink the DB?
- is there another way except moving to the full version of MS SQL?
- if moving to the full version of MS SQL: are you aware of a how-to?

Thanks!
0
axeon78
Asked:
axeon78
2 Solutions
 
Justin OwensITIL Problem ManagerCommented:
MSSQL 2005 Express has a couple of limits that will apply to you:
  • One GB memory limit for the buffer pool
  • Databases have a 4 GB maximum size
Source: http://msdn.microsoft.com/en-us/library/ms345154%28SQL.90%29.aspx

You can clean up white space in a MSSQL 2005 Express DB by running "dbcc shrinkfile" against your ndf file.

This is only a short term solution for you, though.  You will probably need to move to the full version of SQL.  Here is a good walkthrough on how to upgrade your SQL from Express to another version:

http://msdn.microsoft.com/en-us/library/ms403393%28SQL.90%29.aspx

Justin
0
 
vindenCommented:
Did you manage to resolve this ? We're having the exact same issue with our management server.

I think moving to Full SQL is not an option. EBS 2008 Standard was designed for SQL Express.
0
 
vindenCommented:
Ok Here's how I solved this :

I used an SQL query (see code) to query the largest table. Turns out a table called dbo.LocalizedText was taking up a few gigs of data (with alerts going way more back than the 7 days default database grooming -you hear that MS?). A colleague than found another piece of SQL code to clean it all up. See code2. And then another piece of code to reindex the database. code3.

Oh yeah, make sure you backup the db before doing all of this.

I think this is the same issue as addressed here in a blog by Kevin Holman. Still need to read it through entirely.
http://blogs.technet.com/kevinholman/archive/2008/10/13/does-your-opsdb-keep-growing-is-your-localizedtext-table-using-all-the-space.aspx

Send flowers to vincent@nvvandenbussche.be ;) Cheers.
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
vindenCommented:
Code1
0
 
vindenCommented:
CODE1: Query which table is taking up all the data

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

CODE2 : Clean up localized text

-- 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

CODE3: REINDEX the DB

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 ('?')"

0
 
DNadon57Commented:
The solution Vinden points out does resolve the issue.  I had the same problem and followed Kevin Holmon's blog as well as the information in Steve Rachui's blog at http://blogs.msdn.com/steverac/archive/2009/06/09/localizedtext-issues-gone-in-r2.aspx.  

There are 2 tables affected by the problems with both Ops Mgr and SCE, LocalizedText being one.  I'll have to check my notes at work to remember the second one.  The problem is caused by a 2 monitoring rules for Exchange 2007.  On my system, the LocalizedText table reduced from 3.2 million rows to 93,000 rows after I ran the SQL queries and re-indexed the tables.  This reduced the database by 3GB!!!
0
 
vindenCommented:
0
 
eddogjrCommented:
im having this same issue can anyone help on step by step instructions for this because Im not to familar with sql server express 2005.  Thank you. We do have ebs 2008
0
 
Justin OwensITIL Problem ManagerCommented:
eddogjr,
You need to open up your own Question for Experts to address your situation directly.
Justin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now