How to Maintain and Reduce the Amount of Data Stored in the Systems Center Essentials Databases
[Today’s post comes to us courtesy of Ed Walters]
System Center Essentials 2007 (SCE) is a unified management solution that is included with the Essential Business Server package. The application is installed on the Management Server and is designed to give the network administrator the ability to proactively manage IT environments through the use of monitors and alerts. This article addresses some “Best Practices” regarding database maintenance and performance. It also outlines steps to remove unnecessary data from the SCE databases if the databases reach the 4GB capacity.
SCE gathers agent data through predefined objects, monitors and filtering rules that are installed through Management Packs. An initial set of Management Packs are installed during the SCE installation providing basic functionality, with additional Management Packs available through the Management Pack Catalog. Data is then gathered by various scripts and detection methods outlined within these Management Packs, and inserted into SCE’s databases for reference and reporting.
In order to keep track of the information gathered, SCE installs with Microsoft SQL Server 2005 Express Edition. Even though the Microsoft SQL Server 2005 Express Edition Express edition is conveniently included, it also includes a database size limitation of 4GB (log files excluded). Depending on variables like the number of users, number of Management Packs installed and the number of day’s data is kept, it is possible for the SCE database to reach its 4GB capacity within a few months time.
WSUS Server Cleanup Wizard
Before we get into database grooming, a “Best Practice” to consider when developing a server maintenance plan would be incorporating the WSUS Server Cleanup Wizard into your monthly maintenance “to-do” list. The WSUS Server Cleanup Wizard allows you to remove out-of-date update files, superseded updates and computers that are no longer available. The recommendation by the WSUS team is to “use the Cleanup Wizard on a regular basis”, which would be considered once a month if possible, or at least once a quarter. For more information concerning the WSUS Server Cleanup Wizard: https://technet.microsoft.com/en-us/library/cc708578(WS.10).aspx.
Defining Database Grooming Settings.
“Out of the box”, SCE has a set of default Database Grooming Settings that specify the amount of time (number of days), data is kept before it is removed from the database. These settings address records like Resolved Alerts, Event data and Performance data (See the screenshot below for the full list) which can rapidly fill a database.
The “Records to delete” settings, shown in the Database Grooming Settings window below, is purely an initial baseline configured during the SCE installation. These settings can be easily modified at anytime, and can be safely set to a 3 day period.
To view\edit the Database Grooming Settings:
Open the System Center Essentials console
Select administration or the 
icon
Select Settings on the left and then Database Grooming on the right.
This will open the Global Management Settings window.
How do I modify the Database Grooming Settings?
Highlight the subject in question.
Select the edit button to modify.
Change the number of days (from 7 to 3 days).
Select OK.
The objects listed under “Database Grooming Settings” are a small subset of all of the objects stored in the SQL database. These settings will help with basic maintenance, but depending on the number of SCE agents returning data and the type of information SCE is gathering, simply modifying these settings may not prevent the SQL database from reaching its limit.
What happens if a System Center Essentials database does reach the 4GB limit?
One of the first things that you notice is an error displayed in the Essential Business Server console under the “Computers and Devices” tab: “There are problems displaying data on this page”. If you try to open the SCE console it will fail to open also.
Event viewer will return errors similar to:
Log Name: Application
Source: MSSQL$SCEEvent ID:1827Task Category: (2)
Level: Error
Keywords: Classic
Description:
CREATEDATABASEorALTERDATABASEfailedbecause theresultingcumulativedatabasesizewouldexceedyourlicensedlimitof4096MBperdatabase .Log Name: Application
Source: MSSQL$SCE
Event ID: 1105
Task Category: (2)
Level: Error
Keywords: Classic
Description:
Could not allocate space for object 'dbo.PerformanceData_xxx' .'idxc_PerformanceData_xxx_TimeSampled' 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.
**Note: the error will point to the database in question. Another database that is utilized by SCE is the OperationsManagerDW database. If this database is specified in the error, then the SQL query listed below will need query the OperationsManagerDW database instead of the OperationsManager database that is used in this example below. **
What is the actual size of the Operations Manager database?
Open the SCE instance in SQL Server management Studio Express
Select New Query
USE OperationsManager (database specified in the error above)
exec sp_spaceused
Execute Button (Shown Below)
This will return the current database size.
Q & A
Q. Can I Upgrade SQL? Can I uninstall/reinstall SCE?
Q. If the database has reached its limit, can I upgrade to a different version SQL 2005 to remove the 4GB limitation? Can I uninstall/reinstall SCE in order to reset the database?
A. The answer to both of these questions is “NO” . This is not supported.
Q. Can I Upgrade SQL 2005 Express instance to a Standard or Enterprise version of SQL?
A. No - this is not supported with Essential Business Server.
(https://technet.microsoft.com/en-us/ebs/dd183189.aspx -
Q. Can I manually uninstall/reinstall SCE in EBS?
A. No – Manually uninstalling reinstall SCE is not supported with EBS.
(https://technet.microsoft.com/en-us/ebs/dd183189.aspx
Q. Can I uninstall SCE?
A. SCE is integrated into EBS 2008 and cannot be uninstalled.
** You can remove System Center Essentials through Programs and Features; however there is not a way to reinstall it manually once it’s been removed. If System Center Essentials has been removed, a Replacement Mode install is the only option to reinstall SCE. Do not uninstall System Center Essentials! **
Q. How can I reduce the amount of unnecessary data currently stored in the SQL databases if the databases have reached the 4GB limit?
A. In addition to modifying the Database Grooming Setting, you can reduce the amount of data currently stored by modifying the number of days that data is retained directly through SQL.
Before making any changes to any of the SQL databases, make sure you have created a full backup of the OperationsManager, and the OperationsManagerDW before starting. ( https://msdn.microsoft.com/en-us/library/ms187510.aspx ).
To view the current Maximum Days data is kept
Select New Query
Use OperationsManagerDW
SELECT MaxDataAgeDays, * FROM StandardDatasetAggregation
Execute button
In this example we see several datasets that hold data up to 37 days .
(If you want to get an idea of what a particular DatasetId is responsible for, scroll to the right, under BuildAggregationStoredProcedureName or GroomStoredProcedureName and it will return the Aggregate).
How can I modify the number of days that data is kept in the database?
The following procedure changes any settings greater than 30 days to 15 days.
To change the number day’s data is kept from 30+ days to 15 days.
Select New Query
Enter the following into the blank query window
UPDATE StandardDatasetAggregation
SET MaxDataAgeDays = 15
WHERE MaxDataAgeDays > 29
Select execute
Once the changes have been made, you can verify the days have been modified by following the steps listed above (To view the current Maximum Days data is kept) .
You will need to allow at least 24 hours for all of the grooming processes to complete. Once the grooming processes have completed, the database will be available and the SCE console should open without error.