How to create and apply site collection limits for SharePoint Foundation Installs using SQL Server 2008 Express and R2

If you have installed SharePoint in standalone mode (hopefully only if you are evaluating SharePoint, and even then, create a farm instead using SQL Server Express 2008 R2, with a larger 10GB limit), you are left with a SharePoint implementation that has a limit to how large a SQL Content Database can be. In these cases, you absolutely want to ensure you have defined a Site Collection Quota Limit that is less than the maximum size available, to ensure you don’t fill/cram” your database and end up left with a broken implementation that you now need to get very creative with.


Here’s some bullet points for you to start (’cause I like me some bullets)…


  • If you install SharePoint 2010 in standalone mode (Foundation or Server), you are installing the product with a 4GB database limit as it utilizes/installs locally SQL Server Express 2008. SQL Server 2008 Express R2 shipped shortly before SharePoint 2010 was released, however, not in time to update their installer. So here’s my recommendations:
    • #1 – Don’t install SharePoint in standalone mode, ever. You’ll be left with an install that won’t scale (no farm). And you’ll be starting with a 4GB database limit. You can of course upgrade SQL to SQL Server 2008 Express R2 and move to a 10GB limit but you’ll still be limited in scaling due to no farm
    • #2 – If you are using Foundation and aren’t paying for SQL Server (though have you thought about SQL Server Workgroup Edition? You’ll get the DB sizing you need and maybe you don’t need full standard SQL capabilities for your smaller installation), be sure to at least install SQL Server 2008 R2 first, prepare you installation accounts and then install SharePoint as a farm attaching to this SQL install
    • #3 – always consider using a real SQL Server installation. Enjoy the benefits (again, even workgroup edition) of database maintenance plans, the SQL Agent for alerting and management jobs, larger database sizes and something that is supported(able)
    • 2010 is unlike WSS 3.0 that came with the WID (Windows Internal Database), which had an unlimited size available…
  • As always, even though SQL databases (outside of the express editions) can grow well beyond 200GB, you still want to target your maximum content database sizing to somewhere in the 100-200GB range (in MOSS/WSS we used to say 50-100GB, and that still isn’t a bad idea). It’s not that it won’t scale or work, it is about SLA’s (Service Levels) and the ability to restore a site collection/database in a reasonable timeframe. So plan your content accordingly, ensuring you are creating site collections where appropriate, so you can divide up your data amongst separate databases
  • It is better to be in control of your databases (deal with the fallout, before the fallout happens, by being notified when a site collection is about to hit its threshold). That’s what the remainder of this post is about, how to set a Site Collection Limit, suitable for SQL Server 2008 Express R2 and how to apply it to your Site Collection


How to set a site collection quota


Figure 1: In Central Administration, open Application Management


Figure 2 : Select Specify Quota Templates


Figure 3 : Give your quota a name and set the max size and the warning size


The above is important. I’m assuming you are using SQL Server 2008 Express R2 (if you are using SQL Server Express 2008, upgrade, to get the larger DB size – or if you are stubborn, be sure to set your limits to match its 4GB database limit). So, I limit space at 9GB and warn at 8GB. This helps ensure that:

  • A – the database won’t crash once it hits 10GB, because it can’t
  • B – I’m warned at the 8GB mark so I can work with the customer to discuss either upgrading to full SQL or archiving some content or moving some sites into a new site collection


How to Apply the New Quota


Figure 4 : In Central Administration, Select Application Management


Figure 5 : Select Configure Quotas and Locks


Figure 6 : apply your new portal to the appropriate Site Collection(s)