Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Best Practices for SharePoint Content Databases in SQL Server

MSSQLTips author Ray Barley By:   |   Read Comments (14)   |   Related Tips: > Sharepoint

Problem
I'm an experienced SQL Server DBA, but new to administering SharePoint 2007 databases.  Our current SharePoint databases are very large and getting bigger every day.  Based on my initial research the content database is the one I am going to focus on initially.  Can you give me the details I need to know to implement best practices for this database? 

Solution
I want to start out with a bit of clarification about SharePoint.  When I use the name SharePoint in this tip, I am referring to Microsoft Office SharePoint Server (MOSS) 2007 or Windows SharePoint Services 3.0 (WSS).  WSS was included with Windows Server 2003, while MOSS is a part of Microsoft Office and requires additional licensing.  Both WSS and MOSS utilize content databases; therefore this tip will apply to either one.

There are quite a few SQL Server databases created and used by SharePoint.  The content database is the repository for a lot of information such as web applications, site collections, sites, documents, lists, etc.  Most things that business users do in SharePoint utilize the content database; search is an exception which uses its own database.  As a general rule the normal database best practices apply to SharePoint databases, but there are exceptions.  In this tip we'll focus on the exceptions.  Let's start out with a little bit of SharePoint administration then discuss some best practices around dealing with very large content databases.

SharePoint Administration

SharePoint provides two tools for administration: the SharePoint Central Administration web application and a command line tool called STSADM.EXE.  The web application is easy to use and adequate in most instances.  You can launch the web application from the Start menu by clicking All Programs, Microsoft Office Server, then SharePoint 3.0 Central Administration.  The default location for the STSADM.EXE utility is \Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN.  STSADM is a very versatile tool supporting many operations through a whole bunch of command line parameters.

When you create a web application using SharePoint Central Administration, a content database will be created for you.  However, there are no parameters that you can specify for the database other than its name.  In order to specify things like database file size, maximum size, growth, and path you will need to create the database yourself then use STSADM to create the web application. 

Best Practices

The following represents Microsoft's guidance and best practices on content databases, as specified in various places on technet.microsoft.com:

  • Limit the content database size to 100GB; use multiple content databases if necessary.
  • Create multiple data files and spread them across multiple disks; only create files in the primary filegroup.
  • The number of data files should be less than or equal to the number of core CPUs; count dual core processors as two CPUs; count each processor that supports hyper-threading as one CPU.
  • Create data files of equal size.
  • Consider using SQL Server backup and restore; the SharePoint built-in tools can only restore files to the backup location.

Based on the above recommendations, let's explore creating a content database and using STSADM to create a web application that uses the content database we created, add a content database to a web application and moving site collections from one content database to another.

Creating a Content Database

A sample script to create a content database is shown below:

CREATE DATABASE WSS_Content_MSSQLTIPS
ON PRIMARY
( NAME = MSSQLTIPS_01_data,
FILENAME =
    'F:\data\MSSQLTIPS_01_data.mdf',
SIZE = 20,
MAXSIZE = 40,
FILEGROWTH = 5 ),
( NAME = MSSQLTIPS_02_data,
FILENAME =
    'G:\data\MSSQLTIPS_02_data.ndf',
SIZE = 20,
MAXSIZE = 40,
FILEGROWTH = 5 )
LOG ON
( NAME = 'MSSQLTIPS_log',
FILENAME =
    'c:\data\MSSQLTIPS.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB );
go

alter database WSS_Content_MSSQLTIPS
set single_user with rollback immediate
go

alter database WSS_Content_MSSQLTIPS
collate Latin1_General_CI_AS_KS_WS
go

alter database WSS_Content_MSSQLTIPS
set multi_user
go

Below are the main points from the previous script:

  • Multiple data files being created in the primary filegroup; I'm working with Microsoft Virtual PC and I only have three drives available.
  • The size, maxsize and filegrowth parameters are just examples; specify appropriate values for your environment.  If you expect your content database will require more than 100GB, consider creating it with an initial size of 100GB then add content databases as necessary.
  • The collation Latin1_General_CI_AS_KS_WS is required.

After creating the content database, there are two steps to be performed

  • Set the database owner to the SharePoint Setup account
  • Add the Shared Service Provider service account to the db_owner database role

There are many user accounts in a SharePoint implementation; you will need to get the above accounts from your SharePoint administrator.  Please refer to the TechNet article Deploy Using DBA-Created Databases for additional details.

Using the Content Database in a New Web Application

When you create a new web application, you have to use STSADM in order to use a content database that you created.  A sample STSADM command is shown below (for ease of reading the parameters are shown on separate lines; they must all be on a single line when you execute the command):

STSADM.EXE 
-o extendvs 
-url http://biwsssql2008:6666 
-donotcreatesite 
-exclusivelyusentlm 
-databaseserver biwsssql2008 
-databasename WSS_Content_MSSQLTIPS 
-apidtype configurableid 
-description MSSQLTIPS 
-apidname MSSQLTIPS 
-apidlogin biwsssql2008\spcontentpool 
-apidpwd pass@word1

Hopefully you can have your SharePoint administrator handle this task.  Since our focus is on SQL Server, I'll just highlight the database-related parameters which should be self-explanatory: databaseserver and databasename.  Please refer to STSADM documentation on the extendvs operation for details on the above parameters.

Monitoring the Content Database Size

As users add content to their SharePoint sites, the content database will grow.  You will want to monitor the size of the database file to stay within the 100GB limit per Microsoft's recommendation.  Take a look at our earlier tip Collecting SQL Server Database Usage Information for an example of how to track the size of your content database. 

You should also monitor the size of the site collections in the content database.  You can use the following STSADM command to query information about the content databases in a web application:

STSADM.EXE -o enumsites -url http://biwsssql2008:6666

The -url parameter specifies the web application.  The above command will output an XML document showing the list of site collections, the content database where they live, the size of the site collection in megabytes, etc.  The following is a sample of the output:

In the next sections we'll discuss adding a content database to a web application and moving site collections between content databases.

Adding a Content Database

As you get a better idea of the growth of the content database, you can add one or more content databases to a web application if necessary.  In order to add a content database that you have created you will need to use the STSADM command.  The following is a sample command:

STSADM.EXE 
-o addcontentdb 
-url http://biwsssql2008:6666 
-databasename WSS_Content_MSSQLTIPS_02

Once again the parameters are shown on separate lines to make it easy to read; they need to be on a single line when you execute the command. 

When there is more than one content database associated with a web application, site collections are added to the content databases in a round-robin manner.

Moving Site Collections Between Content Databases

If you have a content database that is getting too big, you can move one of more of its site collections to a different content database.  Run the STSADM -o enumsites command described above and redirect the output to a file; e.g.:

STSADM.EXE -o enumsites -url http://biwsssql2008:6666 > sites.xml

Edit the output file using Notepad and delete the sites that you do not want to move; i.e. edit the sites.xml file so that it contains the list of site collections in a single content database that you want to move to another content database.  You can then use a command like the following to move the site collections to another content database:

STSADM.EXE
-o mergecontentdbs
-url http://biwsssql2008:6666
-sourcedatabase <databasename>
-destinationdatabase <databasename>
-operation 3
-filename sites.xml

Please note the following caveats on the mergecontentdbs operation:

  • You should have your SharePoint administrator take care of this.
  • Moving large site collections can take a long time.
  • The mergecontentdbs operation was added in SharePoint service pack 1.
  • There are some important fixes to mergecontentdbs in Windows SharePoint Services service pack 2.

Please refer to mergecontentdbs for additional details on this operation including space required, permissions, availability of site collections during execution, etc.

Next Steps



Last Update: 6/22/2009


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Saturday, April 03, 2010 - 4:44:28 PM - gmamata Read The Tip
Hi Rey, Thanks for excellent post. I'm a SQL DBA but new to maintain MOSS 2007 databases. Your post explains on how to create multiple data files before hand but I have different scenario as below: In our environment, the content database has created by Share point Admin From the Front end side and both the mdf file & ldf file got created on same disk. Later, I have moved the ldf file to different drive. After 6 months the database size is 50 GB. Now I want create a secondary datafile for this content database and added a new disk for this purpose. Now can I go ahead and create the secondary data file with size 50 GB. Is this allowed? Thanks for your inputs Mamata

Saturday, April 03, 2010 - 8:51:50 PM - raybarley Read The Tip

You can go ahead and add a data file on another drive.  You are supposed to add the new file to the primary file group. 

You should take a look at the following; it's been updated a few months ago:

SharePoint Guidance for SQL Server database administrators
http://technet.microsoft.com/en-us/library/ee721075.aspx

You will also find details about choosing your backup stategy in the above guide.

Use the command ALTER DATABASE ADD FILE ... to add your new file.

 

 

 

 

 


Sunday, December 18, 2011 - 2:29:22 PM - Keith Read The Tip

Hello,

I have a question that I haven't seen addressed anywhere, and was hoping you could assist.  I have a web app with a Single Site Collection in it, just one.  And it is approaching 100G in size (it currently has just under 6,000 sites).  All the literature I see talks about splitting up the content database by moving the site collections to another, second, content db.  But what to do if there's just one large, site collection?  How would I divide that up between content databases?

Thanks,

Keith


Sunday, December 18, 2011 - 8:06:13 PM - Ray Barley Read The Tip

You cannot split up a site collection across multiple content databases.  The entire site collection is stored in a single content database.

 

 


Thursday, March 08, 2012 - 10:39:40 AM - Benoit Read The Tip

Hello Ray,

I've a question about .ldf file growing. Is it acceptable to have .ldf file size of 10 Go after few month with a reasonable quantity of Site collection (about 10 at most)

Thx

 

Benoit


Thursday, March 08, 2012 - 11:37:30 AM - Ray Barley Read The Tip

A high level description is that if your content database is set to FULL recovery mode, the log file keeps growing until you do a log backup then space in the log will get reused (overwritten); this is a bit of a simplification.  If you're using SIMPLE recovery, space will get reused in the log; you don't do log backups in this case.

I think the answer to your question is really the size of the log needs to be the maximum size you need between log backups; in other words the log should be sized so that it's big enough to accomodate the number of transactions that occur between log backups.  Let's say you backup the log every 15 minutes.  You want to size it so it doesn't need to grow during the 15 minutes between log backups.

Can't say whether 10GB is good or bad.

For much more details search Transaction Log on mssqltips; you will see a number of helpful tips.


Friday, March 09, 2012 - 10:08:01 AM - SSN Read The Tip

What should be the Sharepoint Library or List column datatype to accept a import of binary data ( its PDF document / or word document) from SQL server?

In SharePoint Column , I want import varbinary data from sql server.

Example . I have pdf data as binary data in SQL server table column. I need to have Document column ( Type not know..? ) where that binary data( pdf document) from SQL server has to come.

This data transfer is done through SSIS technology, i.e, A program/task execution which reads data from sql server and exports it to sharepoint column. This data transfer is fine with data types like TEXT, Number, etc. but not happening with document.

Rgds,

SSN


Wednesday, March 14, 2012 - 7:09:53 AM - Ramesh Read The Tip

I've a question about .ldf file growing. Is it acceptable to have .ldf file size of 10 Go after few month with a reasonable quantity of Site collection (about 10 at most)


Wednesday, March 14, 2012 - 7:26:03 AM - Ray Barley Read The Tip

A high level description is that if your content database is set to FULL recovery mode, the log file keeps growing until you do a log backup then space in the log will get reused (overwritten); this is a bit of a simplification.  If you're using SIMPLE recovery, space will get reused in the log; you don't do log backups in this case.

I think the answer to your question is really the size of the log needs to be the maximum size you need between log backups; in other words the log should be sized so that it's big enough to accomodate the number of transactions that occur between log backups.  Let's say you backup the log every 15 minutes.  You want to size it so it doesn't need to grow during the 15 minutes between log backups.

Can't say whether 10GB is good or bad.

For much more details search Transaction Log on mssqltips; you will see a number of helpful tips.


Tuesday, March 20, 2012 - 2:10:14 AM - Brahma Kumar J Read The Tip

Hello,

Ray Barley

We have a requirement in our current project where we need to get the information from multiple content dbs.

And also from multiple config dbs (multi farm environment).

Does microsoft allow read from these databases?

We are not performing any writes or edits to the existing databases.

If you are unaware of this please let me know where should i contact to get a perfect answer.

Thanks in advance

Brahma Kumar J


Tuesday, March 20, 2012 - 4:32:04 AM - Colin Read The Tip

Hi Ray,

I've no experience of any DBA, however have inherited WSS 3.0.  Is there an easy way to amend table entries on the WSS Content Database and how do we link this back to our Sharepoint site?

I've trawled the web and cannot find any administration guides for WSS 3.0, not even on Microsoft's site, although it is mentioned in the product!

Thanks

Colin


Tuesday, March 20, 2012 - 6:18:16 AM - Ray Barley Read The Tip

What do you mean by "amend table entries"?  

The samples in this tip use SQL Server Management Studio (SSMS) which is available from the SQL Server program group on your start menu and the command prompt (for the STSADM commands)


Thursday, October 03, 2013 - 7:09:04 PM - Bill Boggs Read The Tip

Hi Ray,

 

    Learned a lot in your post, but have a specific Sharepoint/SQL question.  Our server that hosts our portal crashed and we can no longer access the portal.  We have rebuilt the server, restored the files and have at least 2 dozen databases that are attached or relative to the portal.  My question I guess then is, How do I know which content database to choose?  We have virtualized the server and created a new site, added the servers, services and added a new farm.  What is the best way, without starting all over, to go back and recover the right farm, server, services and whatever else I need?  Central Admistration is up and running, says our site is prepared and running, but can't browse to a home page.  Thanks for any advice you can give.

 

Bill


Friday, October 04, 2013 - 8:30:21 AM - Raymond Barley Read The Tip

I did 2 other tips that might be useful

http://www.mssqltips.com/sqlservertip/2629/managing-sharepoint-content-databases-with-central-administration/

http://www.mssqltips.com/sqlservertip/2608/managing-sharepoint-content-databases-with-powershell/

If your content databases are available in SQL Server then you can add them to a web application by using the PowerShell command Mount-SPContentDatabase

You may want to first use the PowerShell commandTest-SPContentDatabase to see whether the customizations in the content database are in the web application

 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.