How to Reduce the Size of the WSS_Content Database

All replies

• 

  

  1

  Sign in to vote

  Look at the database itself -- there will be too files: the mdf (the database itself) and the ldf (the log file).  The log files are used for if you want to do point-in-time back ups and as a result every transaction for the database is logged.  This can cause the file to get very large. Normally if you were to look at the sizes of both the database file and log file it will be obvious if the log file is too large.  If it is, these following steps will help you.  Based on the information you listed above, it looks like your MDF is the one that's large so it might be best for you to skip to the last paragraph.
  
  If it is your LDF that is too large and you have no intention of doing point-in-time backups the easiest thing to do is (assuming SQL 2005 here) to right click on the database name then go to options.  Change the recovery mode to "Simple" - this will prevent the log file from growing.  Ok out of that dialog and go back to the main SQL Studio screen again.  Right click on the database and select Tasks > Shrink (these instructions might be slightly off since I don't have SQL running in front of me).  Then on the Shrink dialog, there should be a dropdown that asks what you want to shrink -- choose Log file.  At the bottom of the screen choose the option to recover free space.  Then hit ok.  Your log file should now be smaller and it won't grow any more thanks to simple recovery.  Also, be aware that running shrink on your databases is generally NOT recommended.  The only time I suggest using it is if your log files get VERY large, or disproportionately large compared to your content db.  I had a client run it last week and we recovered 300GB of space.  That was an example of a good time to use it.
  
  If your database file is large, then your options are to split the content into separate contentdbs -- which would require a new site collection or two.  Or you could archive your content which would also require some planning.  This option isn't too difficult but definitely requires a lot more planning than simply shrinking your log file.  The only way to make your MDF smaller is to either reduce the amount of content in it or split that content out.
  

  ---

  John
  SharePoint911: SharePoint Consulting
  Blog: http://www.rossonmoss.com
  Twitter: JohnRossJr
  
  MOSS Explained: An Information Workers Deep Dive into Microsoft Office SharePoint Server 2007

  • Proposed as answer by Sara_111 Monday, March 2, 2009 9:43 PM
  • Marked as answer by Mike Walsh FIN Sunday, March 8, 2009 2:52 PM

  Monday, March 2, 2009 1:25 PM
• 

  

  2

  Sign in to vote

  John's advice on the db files and ContentDBs is spot on.
  
  There are also a few content cleanup things you can attempt to do that may buy you some time before splitting out the Content DBs.
  
  

  1. Look at the libraries where Versioning is turned on and see if you are holding a large number of previous versions.  In some systems I've managed it wasn't uncommon to see poorly managed libraries with 200+ versions of a file.  Since complete copies of the file are stored it can add up quickly, especially with those spreadsheets that finance folks love to store and update daily.  If this is an issue, try and set it to only keep a managable number of copies.
     
  2. Review your Recycle Bin settings and see if there is a lot of content sitting in there.  If somebody was moving around a lot of documents its possible that there are 100s of documents in there that are no longer needed.
     

  Hope that helps.
  

  ---

  SharePoint Developer | Administrator | Evangelist -- Twitter - https://twitter.com/next_connect -- Blog - http://nextconnect.blogspot.com

  • Marked as answer by Mike Walsh FIN Sunday, March 8, 2009 2:52 PM

  Monday, March 2, 2009 5:00 PM
• 

  

  0

  Sign in to vote

  I agree with the two previous posts, and would like to add a little more:
  
  While it's possible that the logs, file versions, and recycle bins contents are part of the problem, it's more likely that the increase in content database size is caused by the increase in content.  You will need to do a thorough inventory of your site collections and figure out which sites are consuming the most space.  You may have a user (or several users) who have uploaded things to SharePoint that shouldn't be there (like their Outlook PST files, for example).  One way to quickly tell the size of your site collections is to look at the size of the backup files - I use stsadm to backup my site collections, so each site collection is a separate file.
  
  Once you've identified the problem sites, it may be a good time to implement size quotas...
  
  Joel Oleson's blog has some good reading on content db here:  http://blogs.msdn.com/joelo/archive/2006/08/01/684691.aspx

  ---

  Andre Galitsky

  Monday, March 2, 2009 9:02 PM
• 

  

  0

  Sign in to vote

  I never said that content doesn't grow, I was merely trying to point out two ways that you can temporarily reduce the amount of data while preparing to make farm/content db level changes that can have a major impact.  

  ---

  SharePoint Developer | Administrator | Evangelist -- Twitter - https://twitter.com/next_connect -- Blog - http://nextconnect.blogspot.com

  • Proposed as answer by rhymeAsyluM Friday, July 3, 2015 7:01 PM
  • Unproposed as answer by rhymeAsyluM Friday, July 3, 2015 7:01 PM

  Monday, March 2, 2009 9:06 PM
• 

  

  0

  Sign in to vote

  All these are right and this happens everywhere in the SharePoint world. The primary cause i may say the lack of SharePoint governance in place - what to store ?, how to store ? and the content expiry. The best option left off to reduce the content database size is as said, just to purge the content by deleting the aged content. You may have write some apps to find and delete the content based on the timestamp of the documents.

  http://sharepointcoder.blogspot.com

  ---

  Consultant / Analyst

  Wednesday, May 13, 2009 10:31 AM
• 

  

  1

  Sign in to vote

  Since it was mentioned, truncating the versions seems to be the best way to reduce the .mdf besides of course cleaning up white space.  Easy to do in powershell:

  Get-SPWebApplication | Get-SPSite -Limit All | Get-SPWeb -Limit All | ForEach-Object { ForEach($list in $_.Lists) { If($list.EnableVersioning -eq $true) { $list.MajorVersionLimit = 1; $list.Update(); ForEach($item in $list.Items) { $item.URL; $item.SystemUpdate() } } } }

  Post about what you're doing is here: http://iedaddy.com/2011/11/sharepoint-powershelldelete-old-versions-of-documents-and-items/

  We do it all the time when attaching prod to a test system so we don't waste space.

  If you want to deep dive into the storage being used, these are also pretty good, I'll run them as a before and after when I truncate versions just to get an idea of how badly versioning is affecting the production system.

  http://iedaddy.com/2011/05/sharepoint-2010powershell-to-dump-out-the-site-collection-storage-usage-for-a-farm/

  http://iedaddy.com/2011/06/sharepoint-2010powershell-to-determine-database-sizes-in-farm/

  I usually end up with about a 2% reduction, so haven't bothered to change production versioning or track down our rogues (default for our governance is only keeping 10 versions anyway, but some users know how to go in and change that)

  ---

  ieDaddy
  Blog: http://iedaddy.com
  Twit: @iedaddy

  Monday, November 19, 2012 4:49 PM
• 

  

  1

  Sign in to vote

  Just to add to the knowledgebase here in case someone searches for reducing sharepoint database sizes, you can also run a powershell script to dump the audit data:

  http://iedaddy.com/2012/12/sharepointuse-powershell-to-delete-audit-data-from-farm/

  $user = "<doman\user>"
  
  foreach ($site in get-spsite -Limit ALL)
  {
    if (($site.Owner.UserLogin -eq $user) -OR ($site.SecondaryContact.UserLogin -eq $user))
      {
      Write-host 'Deleting audit data for site: ' $sc.URL
      $i = -350
      do {
       Write-Host $site.URL ' - Delete day ' $i ' : ' ([System.DateTime]::Now.ToLocalTime().AddDays($i))
       $site.audit.deleteentries([System.DateTime]::Now.ToLocalTime().AddDays($i))
       $site.audit.update()
       $i++
       }
       while ($i -le 1)
      }  $site.Dispose()
  }
  

  
  

  ---

  ieDaddy
  Blog: http://iedaddy.com
  Twit: @iedaddy

  Friday, March 22, 2013 5:13 PM