[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.
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: http://technet.microsoft.com/en-us/library/cc708578(WS.10).aspx.
“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.
Highlight the subject in question.
Select the edit button to modify.
Change the number of days (from 7 to 3 days).
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.
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$SCE Event ID: 1827 Task Category: (2) Level: Error Keywords: Classic Description: CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database. 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.
Log Name: Application Source: MSSQL$SCE Event ID: 1827 Task Category: (2) Level: Error Keywords: Classic Description: CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.
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. **
Open the SCE instance in SQL Server management Studio Express
Select New Query
USE OperationsManager (database specified in the error above)
Execute Button (Shown Below)
This will return the current database size.
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.
Q. Can I manually uninstall/reinstall SCE in EBS?
A. No – Manually uninstalling reinstall SCE is not supported with EBS.
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. (http://msdn.microsoft.com/en-us/library/ms187510.aspx).
To view the current Maximum Days data is kept
SELECT MaxDataAgeDays, * FROM StandardDatasetAggregation
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).
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.
Enter the following into the blank query window
UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 15 WHERE MaxDataAgeDays > 29
SET MaxDataAgeDays = 15
WHERE MaxDataAgeDays > 29
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.
Thanks for this post! It is very helpful! I have been struggling with both issues described here.
Just for the record : The last query should be :
SET MaxDataAgeDays = 15
WHERE MaxDataAgeDays > 29
The first line is missing in the article, resulting in an execute error.