Skip Ribbon Commands
Skip to main content

Quick Launch

Todd Klindt's home page > Todd Klindt's SharePoint Admin Blog > Posts > SharePoint doesn’t respect the Model db
October 08
SharePoint doesn’t respect the Model db

All of SharePoint's success must have gone to its head. It refuses to use SQL's Model database when it creates new databases. To recap, when databases are created in SQL, they are created using the same settings as System database called Model. Microsoft does a better job than I do explaining it here on MSDN.

When I do my SQL and SharePoint sessions I preach the importance of changing the default database growth settings. Unless you change them your Content Databases will be in Full Recovery Mode. For those that aren't familiar with SQL, this can result in drives filled with Transaction Logs. The databases will also grow at a paltry 1 MB at a time which is laughable for SharePoint. The Transaction Logs grow at 10% at a time, which isn't much better. While Full Recovery Mode is good for production environments, the autogrow settings don't make any sense. I tell folks to change the autogrow settings to something more reasonable like 1 GB at a time, and to pregrow databases so it doesn't have to happen during heavy use. This keeps database file fragmentation low, and users hopefully won't see any slowdowns due to database growth.

I was building a test environment today and I decided to automate some of this. I wrote a script that set my Model database to Simple Recovery Mode and set both data and log autogrow to 100 MB. Neither of those are good production environment settings, but for my test environments they made sense. Imagine my surprise when the next Content database I created had the 1 MB and 10% autogrow settings! It was in Simple Recovery mode, so it wasn't a complete loss. I couldn't believe my eyes. I verified my Model database settings were at 100 MB growth, they were. SharePoint was just dancing to the beat of its own drummer. I reproduced this in a few different farms and had some SharePoint admin buddies of mine verify it.

The moral of this story is that you should pay attention to your database settings and you'll need to manage them manually. You cannot rely on your Model database settings. This is especially important if you have a DBA managing your databases for you. They may expect SharePoint to copy the Model database settings.

tk

Comments

Great Info

Thanks for Sharing. 
 on 10/9/2009 7:47 AM

Re: Great Info

Sometimes the simplest comments are my favorite. :)

tk
Todd O. KlindtNo presence information on 10/9/2009 8:37 AM

M Ghosh

Great tip, many thanks.
 on 10/21/2009 5:26 AM

Consider Centralpoint

Centralpoint CMS Portal gives Sharepoint users a viable alternative. This platform picks up where sharepoint leaves off, allowing you to create multiple web projects from a 'singular' database, instead of creating many databases which pose challenges to federate a search 'across' them. Basically, Centralpoint gives you one database, considate of Roles, Audience Types, and AD Integration to tie it all togehter, and relate information together...based on the role of the individual accessing it.

Centralpoint is a document management system, as much as it is a content management system, email broadcasting, forms management, roles management....portal.
 on 12/19/2009 10:22 PM

Does not seem to apply to SharePoint 2010

Todd,

It doesn't appear this applies to SharePoint 2010, do you agree?  I performed a quick test in a VM of switching the recovery mode in Model to "Simple" and then creating a new content database through CA and the new content DB was set to Simple recovery.  I realize this is an old article, just figured it's worth clarifying if this applies only to 2007 (I haven't had an opportunity to test this on 2007).

-Bryan (www.ofonesandzeros.com)
 on 5/20/2011 12:25 PM

Re: Does not seem to apply to SharePoint 2010

Bryan,
Right you are. I just checked this on a couple of SharePoint 2010 VMs and it did respect at least the recovery model options. I didn't test much else.

Thanks for the heads up.

tk
Todd O. KlindtNo presence information on 5/23/2011 8:08 PM

Re: Does not seem to apply to SharePoint 2010

As I read through my original article again, everything is correct. In my original article I said that SharePoint 2007 DID copy the Model's Recovery Model setting, but it DID NOT copy the Model's autogrow settings. With some quick testing SharePoint 2010 behaves exactly the same way.

tk
Todd O. KlindtNo presence information on 5/28/2011 9:17 PM

Add Comment

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.

Title


Body *


Today's date *

Select a date from the calendar.
Please enter today's date so I know you are a real person

Twitter


Want a message when I reply to your comment? Put your Twitter handle here.

Attachments

 

 Please Support my Sponsors