Advertisement

  • SharePoint Best Practices - Pre-grow or Auto-grow Database File Size?

    One of the many good and highly recommended SharePoint best practices is that you should pre-grow your SQL data file to its planned and expected maximum size.

    This recommendation, however, laudable has caused some confusions amongst SharePoint technology specialists. Does pre-grow mean determining and deciding the database file size during planning and setting same during deployment and configuration? The answer, by no means no. Pre-growing your database does not equate to pre-determining and setting a fixed file size for your database.

    Pre-growing your database helps reduce the impact of fragmentation on performance. It also helps reduce the number of IOPM, and resource contentions associated with auto-growing database. However, using fixed sizes for your databases and logs files could quite easily violate the SharePoint Health model, thereby causing one of the error events; specifically Event ID 3758; to occur.

    My Recommendations
    Pre-grow does not negate auto-grow rather it is complimented by it. You should analyse and determine your database potential file size during planning. Set the initial size of your database file accordingly. Your database is now said to be pre-grown. You do not need to disable Autogrowth. The key to using autogrowth correctly and efficiently is to avoid changing the database size rather frequently. If you have correctly planned and estimated the potential growth rate of your database over a specific interval; say bi-monthly, quarterly, yearly, etc; you can reasonably grow your database by the determined size to accommodate the growing data.

    Resource(s)
    Event ID 3758 (Windows SharePoint Services health model)
    Patterns & Practices: Chapter 14 — Improving SQL Server Performance

1 comments:

  1. Anticipating the database growth of SharePoint in an organization is really a challenge. All the business admin can do whatever they want, can store anything down the road. I would recommend to have the pre-defined quota for each site collection so that they can retire the content when they want to continue with the same instance.