Calculating Storage Trends with a Pivot Table

If you have a heavily used Site Collection you have to keep an eye on content database size. Generally speaking the content database for a single site collection should not exceed 200Gb, and the limit is lower when you have multiple site collections in a single content DB; Here's the current guidance

With this in mind there's a particular library responsible for most of my Content DB growth, and I was asked to project the growth rates going forward. This will allow us to plan when we might need to move content out of the Site Collection to keep within limits.

Out of the box there's no capacity planning charts waiting for you, but with a little Excel magic you can get a chart in two minutes flat.

First create a view in the library for the time period you're interested in. The only columns of interest are Created (date) and File Size.

Create View

You end up with an apparently dull view like this;

Select Columns

Now export that view to excel by clicking the ribbon button for the purpose;

Export to Excel

Still nothing exciting, but now the fun starts;

Spreadsheet Example

Click on 'Summarize with PivotTable' as seen towards the left of this screenshot;

Select Pivot

Select your Created and Filesize columns;

Select Columns

To the right of the pivot screen, tick the two columns;

Tick Columns

Select just a single value in the sheet. Then right click and click 'Group';

Group Options

Group by Months and Years;

Month and Year

Your data should be summarized into Month/Year automatically now. Time to get the chart by clicking 'PivotChart';

Click the chart

Your chart is displayed as below. This tells me my users are socking away about 3Gb of data a month into my library. At 36Gb a year I have a little time to play with before hitting size limits.

The finished chart

Beware with this technique. For example if you have versioning turned on you may well be using much more space than this would tell you about. In that case you might want to use monitoring tools at the SQL Server end of the chain!

Happy SharePointing!

Disclaimer: The software, source code and guidance on this website is provided "AS IS"
with no warranties of any kind. The entire risk arising out of the use or
performance of the software and source code is with you.

Any views expressed in this blog are those of the individual and may not necessarily reflect the views of any organization the individual may be affiliated with.