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.

You end up with an apparently dull view like this;

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

Still nothing exciting, but now the fun starts;

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

Select your Created and Filesize columns;

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

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

Group by Months and Years;

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

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.

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!