Dealign with a large SharePoint 2010 content database

I have a content database that's nearly 200GB (SharePoint 2010 with the content DB hosted on a separate SQL 2008 Server, DB is called WSS_Content).

Here's the output of STSADM -o enumsites -url http://mySPSite

<Sites Count="22">
  <Site Url="http://mySPSite"  ContentDatabase="WSS_Content" StorageUsedMB="177452.6" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/mysites" ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.9" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user1"  ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user2"  ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.9" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user3"  ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user4"  ContentDatabase="WSS_Content_MySites" StorageUsedMB="50.1" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user5"  ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user6"  ContentDatabase="WSS_Content_MySites" StorageUsedMB="449.9" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user7"  ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user8"  ContentDatabase="WSS_Content_MySites" StorageUsedMB="75.6" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user9"  ContentDatabase="WSS_Content_MySites" StorageUsedMB="5.7" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user10" ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user11" ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user12" ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user13" ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user14" ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user15" ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user16" ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user17" ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user18" ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/personal/mySP_user19" ContentDatabase="WSS_Content_MySites" StorageUsedMB="1.8" StorageWarningMB="0" StorageMaxMB="0" />
  <Site Url="http://mySPSite/sites/Office_Viewing_Service_Cache" Id="acc28147-6948-4f2e-a4ce-33b607256e84" Owner="mySP\spsetup" ContentDatabase="WSS_Content_MySites" StorageUsedMB="1128.7" StorageWarningMB="0" StorageMaxMB="102400" />

I have a few questions on this:
1. What's the process to make it smaller?
2. What content should I split out? The one site collection http://mySPSite is really big (177GB), there a way to somehow break that up without significantly restructuring our SharePoint environment?

LVL 12
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Easiest would be to create additonal content databases and move sites (import/export) to the new databases.

Once a site is moved the whitespace within the original content database is not cleaned up/reduced though.

Once I have moved all sites from the original and verified that it is clean, I usually re-create it and new sites will homed on it..
Yagya ShreeCommented:
I would recommend to run powershell command on the one site with 177 GB content and first check out if this is actual content or some unwanted files filling space.

As i have seen users deleting content regularly and then keeping their recycle bin full.

Once i identify which specific location in the site is using most of the space, you can move that site out of content database and create a new one for the same.

So identification and cleanup of the site using high volumn should be first task.

hope that helps.
@ yagyashree

"Once i identify which specific location in the site is using most of the space, you can move that site out of content database and create a new one for the same."

Could you please eloborate on the underlined, is the new site created within the original content database or a new content database..
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Yagya ShreeCommented:
New content database
Julian123Author Commented:
Thanks for the help. In terms of moving sites to the new content database, a few questions:
1. At what level can I move sites? My site http://mySPSite has 177GB, can I move, say just http://mySPSite/big_sub_site1 to a new content DB and keep http://mySPSite/big_sub_site2 in the existing content DB?
2. If I do #1, will the user experience change at all?
2. What's the process for moving the sites?
3. Per the mention above of whitespace in the DB, how do I resolve that? Is there a way to shrink the DB?

Yagya ShreeCommented:
1. Yes, you can move subsite1 from your site collection to new site collection and keep your subsite2
2. You would need to be careful while doing this as URL of the site would change for subsite user who content would be moved to new site collection. Also you need to make sure any customization your done on source site collection need to be done on destination site collection as well can use powershell, STSADM or any third party tool like Metalogix to perform the action
4. Once the content is moved  you can perform a db shrink to recover free space.

Hope this helps.
Julian123Author Commented:
Thanks, yagyashree. Concerning your answer #2 above, if I moved http://mySPSite/big_sub_site1 to a new content DB , you mentioned the URL would change.  Would I need to make any changes to the sites still in the old content DB ( the main http://mySPSite or http://mySPSite/big_sub_site2, a sub-site not moved) for site navigation to still work?

Would the moved site now be something like http://mySPSite_2/big_sub_site2?
Yagya ShreeCommented:
No changes are required in old site collection or subsite.

When you move the subsite to detination, the new site will read as http://myspsite_2 as the move will on the top level of the site

Note: make sure you take DB backup before activity and keep it till you get user's confirmation that all is working well.
Julian123Author Commented:
Thanks. If I have links on the http://myspsite/ page that pointed to  http://mySPSite/big_sub_site1 and then I move that subsite to a new content DB, it sounds like that site's url would now be  http://mySPSite2/big_sub_site1. Would the links on the http://myspsite/ page that pointed to the old url now be broken? Or would those get fixed automatically?
Justin SmithSr. System EngineerCommented:
Splitting a site collection up is never a good idea.  All subsites in a site collection should be kept together.  Reason being, the subsites have dependencies on the root site in the site collection.  While it can be done, it's not a good idea.

All links would in fact be broken.  You can't fix them automatically other than developing a script to do it.

A better idea would be to clean up the site if it has unnecessary data.  Or move this site collection "http://myspsite" to it's own content database.  THis is accomplished by using the Move-SPSite command.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.