ConfigMgr Maintenance: Keeping the Database and Content Libraries Oiled and Running

Are you one of those people doesn’t take their car into the shop until it has a problem? Well, I think you know what you should do instead—take it in for regular servicing to avoid problems in the first place!

The same is true of your Configuration Manager databases and libraries. In this blog, I’ll tell you how to do regular maintenance. This will help keep Configuration Manager running at optimal performance and avoid those nasty breakdowns.

SQL Server Database Maintenance

A key performance consideration with SQL is to keep the database indexes up to date and the database fragmentation levels down. Microsoft has included built-in maintenance tasks that can take care of some of this. For example, the rebuild indexes task as pictured below.

However, there is a better way! Microsoft MVP Ola Hallengren has published a script that undertakes all of the key performance maintenance tasks. By default this script will set up SQL jobs that you can schedule to run tasks such as integrity checking, re-indexing and backing up all databases within the SQL instance.

“Installation” of the solution is taken care of by running the maintenance SQL script, essentially a process that creates the required storage procedures to call and SQL jobs to undertake the various type of maintenance. If you are unfamiliar with this process, follow the steps below. (Remember to always have a SQL backup before undertaking any custom SQL tasks):

1. Download the MaintenanceSolution.SQL script
2. Open SQL Server Management Studio
3. Connect to the instance running your Configuration Manager DB
4. Click on Open and go to File
5. Select the script you have just downloaded and click Open
6. Click on the Execute button and you should have a screen similar to the one below

At this point you should now have several SQL Server Agent jobs. You can run them either interactively or via a schedule, so they proactively maintain your databases.

Steps contained within the database backup job

Scheduling a job in SQL Management Studio

You might also consider creating your own set of steps for the optimization job, such as something like this:

# Rebuild or reorganize all indexes with fragmentation on all user databases,
performing sort operations in tempdb and using all available CPUs

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,
INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @SortInTempdb = 'Y',
 @MaxDOP = 0

The solution is also not specific to Configuration Manager, so you could use the same approach to maintain your WSUS database if it resided on a different SQL instance. Personally, I have seen massive performance impacts right through to the Configuration Manager console by implementing this solution.

WSUS Database Maintenance

Now that I’ve mentioned WSUS, let’s dig a little depeer into it. Maintaining the WSUS database is vital as it contains a lot of information that can become obsolete quite quickly. Up until now this has been the area of community solutions to decline superseded and expired updates in the database, Johan Arwidmark for example has this covered in an old and still accurate blog post. However, there is something very cool in Configuration Manager CB 1906 which caters to this need.

To use it, go into the Configuration Manager Administration blade, then:

1. Expand Site Configuration
2. Click on Sites
3. Right-click on your Site
4. Click on Configure Site Components
5. Click on Software Update point

Now you will see in the properties that you now have a new WSUS Maintenance tab. Here you can select options to both add indexes to the database and maintain what is contained within.

You can find more information on how to perform WSUS performance maintenance in the Microsoft support article titled, The complete guide to Microsoft WSUS and Configuration Manager SUP maintenance.

Distribution Point Clean Up

Distribution points (DPs) are the mechanism in which your machines receive content during their entire lifecycle when managed through Configuration Manager. There are of course exceptions to that rule where clients can reach out directly or share content with other machines on the network. However, for this article we are going to look at what you can do to stop bloat on your DP’s as part of your maintenance strategy.

Over time with the publication and removal of applications, updates and other content, your distribution points build up a level of unused files. Without a maintenance process in place for these distribution points, you end up with excessive file storage consumption. In some cases, you may request \expanded storage to cope. Pruning unwanted content from your DP content libraries should also be part of your ongoing maintenance plan.

Fortunately, there is a command line tool included in Configuration Manager that does just this function. The tool has the very original name of ContentLibraryCleanup.exe, so its purpose should be very clear. It has been around for quite a number of years, but I still see sites where administrators overlook this area.

Now let’s look at how to first identify an issue and then how to perform a remediation action based on the figures we see. To run the tool, launch a PowerShell or Command Prompt window and browse to the following location:

 %CM_Installation_Path%\cd.latest\SMSSETUP\TOOLS\ContentLibraryCleanup\

Now we can run through an initial discovery to see the level of white space that could be released to the system. To do so, run the following command:

ContentLibraryCleanup.exe /dp “Distribution Point FQDN” /ps “Primary Site Server 
FQDN” /sc “Site code”

After a few minutes we should end up with an exported CSV file and a visual representation on screen as to the level of space that can be released.

Cleaning up the space is simply a matter of adding the “/delete” switch and “/q” switch to enable which will then run the process without prompts. Visually turning this to this: