A key maintenance task for OpsMgr environments is ensuring the size of the databases is kept under control. This requirement can be achieved through understanding how data retention is configured and managed for both the Operational database and the Data Warehouse database. Data retention settings are defined to let OpsMgr know how long each database should hold onto information related to the different monitoring datasets. These settings will also have a direct effect on the size of your backup jobs and ongoing storage requirements.
Microsoft recommends that the Operational database should always have at least 50% free space available to support growth and indexing. This requirement refers to the amount of free space inside the database file itself and not on the volume that hosts the database.
A nice visual way to check the free space of a database is to launch the Disk Usage report from the SQL Management Studio tool as shown in Figure 11.18.
Figure 11.18: Launching the SQL Disk Usage report on a database
When the report launches, you'll be presented with a view similar to Figure 11.19 where you can see an exact breakdown of how disk space inside the data files and transaction logs is distributed.
Figure 11.19: Viewing the Disk Usage report on a database
If the free space inside the Operational database drops below 40%, the self-monitoring Operational Database Space Free (%) monitor will automatically kick-in to alert you that the database is running out of space. This alert is shown in Figure 11.20 and it will detail the amount of free space currently available along with some information about how to resolve the issue.
Figure 11.20: Self-monitoring alert for Operational database free space
A common cause of the Operational database disk space getting used up quickly is if you've under-sized it initially during installation and then later add a large number of new agents or management packs. When you see this alert you have two options – either increase the size of the database or perform some database grooming to remove unnecessary monitoring data and reduce the size.
Unless you've sized the database incorrectly to begin with – remember to always use the OpsMgr Sizing Helper tool discussed in Chapter 1, Introduction to System Center Operations Manager - then we do not recommend increasing the size of the Operational database and instead prefer the database grooming method discussed in the next section to regain control of the space.
Note
In SQL, there's a feature called Autogrow that can be enabled or disabled on every database and the Operational database has this feature disabled by default. This setting should always remain disabled for the Operational database - ignore any DBA that tells you otherwise!
The reason for this is that in the event of a large amount of data being passed to the database, with Autogrow enabled, the Operational database will continue to grow at a rapid pace until your logical disk is full and OpsMgr stops working.
The process of configuring data retention settings is known as Database Grooming and for the Operational database; this can be configured from within the Administration workspace of the console.
Here's how to configure Operational database grooming:
- Open the Administration workspace; select Settings from the navigation pane, then double-click on Database Grooming as shown in Figure 11.21.
Figure 11.21: Configuring grooming on the Operational database
- By default, all datasets are configured with a data retention setting of 7 days – the only exception being Performance signature, which has a setting of 2 days. Select a dataset that you want to edit from the Records to delete column and click on the Edit button.
- Modify the number of days for the Older than value and click on OK to close the dialog box. Repeat this step for any additional datasets that you want to modify and when you're done, click on OK to close the Database Grooming dialog box.
Tip
To maintain optimal performance within the console and keep disk space usage for SQL to a minimum, it's not recommended to extend the data retention value of any of the Operational database datasets beyond the default 7 days. You should only use this grooming process for reducing the retention days of datasets that are less important to your organization.
The new database grooming settings you configure in the Administration workspace will be applied automatically at 12:00 AM every day when the Partitioning and Grooming rule kicks in. Shown in Figure 11.22, this rule is targeted at the All Management Servers Resource Pool class and it runs a workflow that sets the retention values you've previously specified.
Figure 11.22: Built-in partitioning and grooming rule
Tip
Microsoft's Kevin Holman has written an excellent deep-dive post on how the Partitioning and Grooming rule works including a very handy tip on how to quickly reduce the size of the Operational database using the EXEC p_PartitioningAndGrooming stored procedure. For more information, check it out here - http://tinyurl.com/opsmgrdbgrooming
The Data Warehouse database will likely be one of the largest SQL databases that most organizations will have to manage – mainly due to the fact that by default, it retains data related to every monitored object for up to 400 days! This monitoring information is stored in a number of different datasets and depending on the management packs deployed; each OpsMgr environment could have a different list of datasets. To add to the complexity, some of the datasets retain different aggregations (raw, daily and hourly) for the same type of data.
In the majority of cases, not all datasets and aggregations need to be configured with such long-term retention values and typically, engaging with your colleagues or customers in a discussion about their actual reporting requirements is a good way of understanding what needs to be retained short-term and long-term.
There's no option in the console to configure database grooming on the Data Warehouse database – instead you'll need to get familiar with the Data Warehouse Data Retention Policy (dwdatarp.exe) command line tool. Although some people might not be comfortable working with a command line tool, this is a much better improvement on earlier releases of OpsMgr – where you had to manually edit SQL tables to configure grooming and aggregation settings.
The dwdatarp.exe tool doesn't come bundled with the OpsMgr installation media and you'll need to download it from Microsoft's website here - http://tinyurl.com/opsmgrdwdatarp.
There's no installation required for this tool and these steps will walk you through grooming the data warehouse with it:
- Copy the
dwdatarp.exetool to the SQL server hosting the OpsMgr data warehouse, launch a command prompt with administrative permissions and browse to the folder that you've copied the tool over to. - At the command prompt, type the following line (substituting it with your SQL server and instance names):
dwdatarp.exe -s SQLSERVERSQLINSTANCE -d OperationsManagerDW > c:dwoutput.txt
- As shown in Figure 11.23, a newly generated text file outputting the current retention settings should now be available at the path you specified.
Figure 11.23: Creating a data retention and aggregation output file
- When you open the output file you'll be presented with a list of datasets along with their aggregation type, maximum retention age and current size within the database. In Figure 11.24 you can see the output settings of the common datasets in our data warehouse database - notice the Hourly Aggregations for Performance and State data are the two largest datasets in the database.
Figure 11.24: Viewing retention and aggregation settings
- At this point you should be having a conversation with your customer or colleagues to discuss the best retention policy for each of these datasets. When you have an agreement on the number of days to retain data for each set, return to the command prompt again and browse to the folder that the
dwdatarp.exetool is located. - Armed with the information from your discussion about how long to retain each dataset, type the following command (you will need to update the server and dataset name along with defining a numeric value for the agreed number of days you wish to retain the data for):
dwdatarp.exe -s SQLSERVERSQLINSTANCE -d OperationsManagerDW -ds "Performance data set" -a "Hourly aggregations" -m 90
- Repeat this step for each dataset that you wish to modify the retention for and when you're finished, it's a good idea to create a new output file a day or two later so you can compare the values and ensure the dataset sizes have reduced.
Tip
For a deeper dive into using the Data Warehouse Data Retention Policy tool, check out Kevin Holman's post here - http://tinyurl.com/kholmandwgrooming