Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

We have a site collection with its own database where we have been testing migration of a department's shared drive content (about 40GB) via a third party tool. The test migrations went fine. Before running the final migration, we deleted the test content off of the site collection. Now the SharePoint storman page reports the correct size of the final migration (40GB) but SQL server is reporting a size that is double the correct size. From the research I have done SQL server will not let go of space that once had content allocated.

How can we reclaim those space by not affecting SharePoint?

share|improve this question

4 Answers 4

up vote 4 down vote accepted

I have never done it personally but Microsoft has a document that lays out the steps pretty clearly. As always, be sure to take a good backup before attempting this.

share|improve this answer
    
+1 for document link. I don't think backup is needed since it is migrated content. If something goes wrong you just migrate things again (been there done that) –  Vedran Rasol Apr 26 '12 at 20:29
2  
Doing regular db maintenance using health monotoring and DBCC CHECKDB and shrinking database and log files should be part of our IT governance plan :-) –  Anders Rask Apr 26 '12 at 20:59
    
A lot of the database maintenance is handled by Health Analyzer in SP2010 and up –  Garrett Dec 12 '12 at 17:04

So if you deleted your content (also from SharePoint Recycle Bin) next step is to shrink Sharepoint database to claim back your space.

Here is simpele tutorial (with pictures): How to shrink SQL / SharePoint database / log files

I belive only first 5 steps are needed:

  1. If you don’t have it yet, download and install SQL Server Management Studio express from here.
  2. Run the Management Studio and connect to your SQL Server.
  3. Expand “Databases” and select your config database “SharePoint_Config”.
  4. Right Click it, select Tasks –> Shrink –> Files
  5. In the new window select Release unused space and click OK.
share|improve this answer

I was asked to reduce a content DB recently, heres what I found. Whilst the customer was great in deleting content from their sites, SharePoint has a 2 stage recycle bin, so whilst the items were deleted from the site level recycle bin there was still 30 days left on the site collection recycle bin. So please ensure you check that and flush it.

Yes you'll find that the DB will still be large at this stage. So as described above, use SQL Management studio and locate your content database in question, it won't be the SharePoint configuration database as the example shows above but rather WSS_Content_something in all likelihood.

After that you find you will be able to shrink the database file (mdf). BUT! beware chances are your database is set to full recovery mode and when the shrink operation takes place all it will do is blow the transaction log out as many GB as you have removed. So the choice here is you might want to switch the DB into simple mode and then perform the shrink operation> I was lucky in that I had sufficient disk space when the shrink operation was taking place and removing 40GB blew the logs out 40GB, once that was complete the mdf was then shrunk 40GB. After this I switched the DB to simple mode and shrank the log file, then changed it back to full.

share|improve this answer

I'm gonna throw this in because it needs to be added. It's not an 'answer' but it's related to the others here as a supplement, and it's an absolute golden Best Practice that absolutely should be performed on every single SharePoint Content Database that you manage.

Once you release the free space from a truncated LDF after a Simple Mode backup to do whatever movement of the files that you need to - PREGROW ALL FILES BACK TO WHAT THEY WERE, OR WHAT YOU CAN AFFORD TO DO SO.

When SharePoint content gets added or delete, or even updated, SQL Server will populate the MDF and LDF files (the ratio of which depends on your recovery model set on that particular database). SharePoint does this a lot, especially with busy sites.

When you start using a SharePoint site straight after releasing the freespace, the LDF/MDF files for the database start to get written to. Because they're technically maxed out (because you shrunk them), SQL will grow the files automatically depending on some setting (usually increase by 10% or, at it's worst configuration: 10MB).

This growing has a performance penalty. A pretty noticeable one too.

So, if you shrink your files, the database will claim back that space over time anyway, and it WILL slow down the site for your users as it does this. Give them that little bit of experience bonus and save SQL from autogrowing.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.