SCCM 2012 Reporting Database – Out of Control Growth

While cleaning up some data, I ran into some older files that I saved up and had forgotten about. This took place about a year ago,  when I ran into an issue with my SCCM 2012 server where the VM had completely used up all my storage. Not being a production environment I had not pro-actively monitored my home lab server. Yes, yes who would have thought that I would be guilty of not monitoring my server, but then again it’s a lab environment that I run at home. So not so much of a big deal, but the issue appeared to have come out of the box. This means that someone somewhere may be experiencing issues with the System Center Configuration Manager’s Reporting Database growing out of control because by default the database was set to consume 2TBs.

Reporting Database Bloat1

Reporting Database Bloat

To solve this, I proceeded to get into the VM to pin-point why the VHDX file was maxed out. Immediately I see SCCM is completely pissed off at me for not having looked at it for a while and the database had completely eaten up all of my storage. My immediate solution is to check all logs that I can get access to and wipe any unnecessary logs followed by extending the drive an additional 5 gigs for immediate breathing room.

As you can see the ReportServer_log file had grown to 90 gigs without any care in the world and begun eating away at what little space the lab VM had assigned to it. To resolve this I needed to shrink the database back to what it should have been by utilizing the SQL Server Management Studio tools available to me. There are two ways of quickly shrinking the database, one is by using the New Query commands and typing the following:

ALTER DATABASE ReportServer SET RECOVERY SIMPLE;
GO
DBCC SHRINKDATABASE (ReportServer, 10);
GO

SQL Commands

SQL Query Alter Commands

This sets the database recovery mode to simple and allows you to shrink the database with 10% free space. The same or similar process can be accomplished as an alternate method by utilizing the GUI within the SQL Server Management Studio tool through the following steps:

  • Right Click on the desired database and select Properties.
  • In the Database Properties menu, select Options from the left hand side and in the Recovery model drop down box change from Full to Simple.
  • Click OK.
DatabaseBloat2

Database Properties – Recovery model

  • Once back on the main screen inside the Management Studio, right Click on the desired database and select Tasks, Shrink and click on Database.
DatabaseBloat3

Shrink Database

  • Inside the Shrink Database menu, make sure Release unused space is selected and click OK.
DatabaseBloat4

Shrink Database Options

  • Leave the process run for a period of time and once completed once again right click on the desired database and select Properties.
  • In the Database Properties menu, select Files from the left hand side and in the Database files selection, look for ReportServer_log.
  • Select ReportServer_log and look for the Autogrow/Maxsize menu and click on the three dots box.
  • Inside this change the Maximum File Size from 2TB to something more appropriate. I set mine to 50,152 since my drive is 140 Gigs.
DatabaseBloat5

Autogroth Max Size

The end result is getting all that unnecessarily consumed space and now having a safety net for the maximum file size to the reporting database log file. While I did not get a before and after disk usage report, the following report displays how much cleaner the database appears to be.

StorageReport

Storage Report

Unfortunately, I do not have a clear root cause but from what I have seen this can happen with SCCM 2012 unexpectedly in different environments. Therefore, it is better to be safe than sorry and pro-actively configuring the limits on the database files.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s