Home / server / Questions / 31579
Accepted
Sean Howat
Asked: 2009-06-26 06:19:01 +0800 CST

Autogrow on a SharePoint database?

  • 772

So now that I've become the semi-dba at my current job I'm trying to improve a lot of the things that weren't really being considered before. One of these is trying to make sure that databases aren't set to autogrow and are assigned appropriate size limits. My problem is the SharePoint database. Since the databases for SharePoint could grow at unexpected rates I'm not sure what to do with them. I'm looking for some advice from other dbas who manage SharePoint databases.

sharepoint database

3 Answers

  1. Best Answer

    There are at least two ways you can handle trying to figure out the size of the database.

    1. Go through a long planning phase where you figure out the expected workload and usage patterns. You then correlate that back to how much disk space per site the quotas allow for. And then you identify how many sites you can support taking into consideration restore time, site performance, and your own comfort level. This should give you an estimated database size that should ideally be 100GB or less. You can then pre-expand your database to the expected maximum size.
    2. Just pre-expand it to 100GB. See this TechNet article.

    On top of this you should always be monitoring your database sizes and set up alerts to trigger whenever things look like they could fill up in X days. X will be the time it takes you to not only expand the database, but make sure that it is properly defragmented afterwards and your new restore time figured out. X may also include the time that it takes to add more storage to your SAN/DAS.

    The reason I picked 100 GB is because that is inline with the recomended max size of a sharepoint database taken from their documentation. It's not to say that you can't do larger, but need to understand the repurcusions of that choice.

    • 3

  2. Its tough becoming the unwilling DBA. You can set autogrow its just you should try to size your database up front to avoid fragmentation and performance issues with autogrow. Here's some articles by Paul Randal to help you out.

    Welcome to the DBA world: http://www.sqlskills.com/BLOGS/PAUL/post/Becoming-an-involuntary-DBA-youre-not-alone.aspx

    Sharepoint gold: http://www.sqlskills.com/BLOGS/PAUL/category/Sharepoint.aspx

    • 1

  3. First, for sharepoint you most certainly do want autogrow turned on. You do want to set a size limit of 100GB (the max recomended sharepoint database size). As you've discovered, sharepoint isn't like a regular database application. A month could go by before someone tries to create a new repository for stuff that uses 10gb, when your usually usage is 10MB a day. Here is a Microsoft doc on what maintenance should be done to a sharepoint database.

    • 1