How To Resolve Low-Space Issue In SP2007 And SP2010

As we all know, every version of the document takes a space in the content database so if we have many versions of documents, then every version will have a separate memory allocated in the content database. What we can do in this kind of case, we can remove all the versions of the document except the latest one and we can have a Multi-line field in the document library (Let’s say ‘Notes’) which will have all the version history for all the version filled in it.

Problem Statement

If a document library has a large number of documents and each document has too many versions, then one day, the content database will reach its maximum limit and have a space issue, preventing the addition of any new records/documents in the site. This scenario is valid for SP 2007 and SP2010 as there is no Shredded Storage feature before SP2013 versions.

Solution

As we all know, every version of the document takes a space in the content database so if we have many versions of documents, then every version will have a separate memory allocated in the content database. What we can do in this kind of case is, we can remove all the versions of the document except the latest one and we can have a multi-line field in the document library (let’s say ‘Notes’) which will have all the version history for all the versions filled in it.

Most of the time, these kinds of issues occur in production and there are not many free tools available for this. Also, customers will not allow you to install anything on the production server due to several reasons. Therefore, I came up with the below PowerShell script to achieve the goal.

Please find the PowerShell script attached with this blog. Do let me know if you need any help with respect to the script. I will be glad to help you.

  1. ##############################################################################################################################  
  2. ## Developed By- Sandeep Kumar  
  3. ## Purpose - Powershell script to be used to retain only the lates verison of file and put version history in Notes Column.  
  4. ##############################################################################################################################  
  5.   
  6. if(!(Get-PSSnapin Microsoft.SharePoint.PowerShell -ea 0))  
  7. {  
  8. Write-Progress -Activity "Loading Modules" -Status "Loading Microsoft.SharePoint.PowerShell"  
  9. Add-PSSnapin Microsoft.SharePoint.PowerShell  
  10. }   
  11.      
  12.  #Custom Function to get the version history for the document.  
  13. function GetVersionHistory([Microsoft.SharePoint.SPListItem]$item)   
  14. {       
  15.     $versions = $item.Versions   
  16.     $versionStr = "$($item["Title"])`n"   
  17.     $fldStr = ""   
  18.     for($i = 0; $i -lt $versions.Count; $i++)   
  19.     {   
  20.         $currentVersion = $versions[$i]   
  21.         $checkInComment = $item.File.Versions[$item.File.Versions.Count - $i].CheckInComment   
  22.         if($i -eq 0)   
  23.         {   
  24.             $fileSize = $item.File.Length   
  25.         }   
  26.         else   
  27.         {   
  28.             $fileSize = $item.File.Versions[$item.File.Versions.Count - $i].Size   
  29.         }   
  30.         if($fileSize -lt 1MB)   
  31.         {   
  32.             $fileSize = "{0:N1}" -f ($fileSize / 1KB) + " KB"   
  33.         }   
  34.         else   
  35.         {   
  36.             $fileSize = "{0:N1}" -f ($fileSize / 1MB) + " MB"   
  37.         }   
  38.         $modifiedTime = $web.RegionalSettings.TimeZone.UTCToLocalTime($currentVersion.Created)   
  39.         # CSV formatting: escape double quotes allow quotations, new line and commas within cell. Do not use space between comma and double quote escapes due to csv formating.   
  40.         $versionStr += ",$($currentVersion.VersionLabel),$($modifiedTime),""$($currentVersion.CreatedBy.User.DisplayName)"",""$($fileSize)"",""$($checkInComment)"",`n"   
  41.         $fldStr += "<b>Version: $($currentVersion.VersionLabel)</b></br> Modified time: $($modifiedTime) </br> Created By: ""$($currentVersion.CreatedBy.User.DisplayName)""</br> File Size:""$($fileSize)""</br> Comment:""$($checkInComment)"",</br>"   
  42.         if($i -lt ($versions.Count - 1))   
  43.         {   
  44.             # If more than one version:   
  45.             $previousVersion = $versions[$i+1]   
  46.             foreach($field in $currentVersion.Fields)   
  47.             {   
  48.                 if(($field.ShowInVersionHistory -eq $true) -and ($currentVersion[$field.Title] -ne $previousVersion[$field.Title]) -and ($currentVersion[$field.Title] -ne "<div></div>"))   
  49.                 {   
  50.                     $fieldStr = GetFieldValue $field $currentVersion   
  51.                     $versionStr +=",,""$fieldStr""`n"   
  52.                     $fldStr +="""$fieldStr""</br>"   
  53.                 }   
  54.             }   
  55.         }   
  56.         else   
  57.         {   
  58.             # If first version:   
  59.             foreach($field in $currentVersion.Fields)   
  60.             {   
  61.                 if(($field.ShowInVersionHistory -eq $true) -and ($currentVersion[$field.Title] -ne "<div></div>"))   
  62.                 {   
  63.                     $fieldStr = GetFieldValue $field $currentVersion   
  64.                     $versionStr +=",,""$fieldStr""`n"   
  65.                     $fldStr +="""$fieldStr""</br>"   
  66.                 }   
  67.             }   
  68.         }   
  69.     }   
  70.     return $fldStr   
  71. }  
  72.   
  73.  #Custom Function to get the value of different field types like lookup, User etc.  
  74.  function GetFieldValue([Microsoft.SharePoint.SPField]$field, [Microsoft.SharePoint.SPListItemVersion]$currentVersion)   
  75. {   
  76.     if(($field.Type -eq "User") -and ($currentVersion[$field.Title] -ne $null))   
  77.     {   
  78.         $newUser = [Microsoft.SharePoint.SPFieldUser]$field;   
  79.         $fieldStr = $newUser.GetFieldValueAsText($currentVersion[$field.Title])   
  80.         $fieldStr = "$($field.Title): $fieldStr"   
  81.     }   
  82.     elseif(($field.Type -eq "Lookup") -and ($currentVersion[$field.Title] -ne $null))   
  83.     {   
  84.         $newLookup = [Microsoft.SharePoint.SPFieldLookup]$field;   
  85.         $fieldStr = $newLookup.GetFieldValueAsText($currentVersion[$field.Title])   
  86.         $fieldStr = "$($field.Title): $fieldStr"   
  87.     }   
  88.     elseif(($field.Type -eq "ModStat") -and ($currentVersion[$field.Title] -ne $null))   
  89.     {   
  90.         $newModStat = [Microsoft.SharePoint.SPFieldModStat]$field;   
  91.         $fieldStr = $newModStat.GetFieldValueAsText($currentVersion[$field.Title])   
  92.         $fieldStr = "$($field.Title): $fieldStr"   
  93.     }   
  94.     else   
  95.     {                               
  96.         $fieldStr = "$($field.Title): $($currentVersion[$field.Title])"   
  97.     }   
  98.     return $fieldStr   
  99. }    
  100.  
  101.  #Custom Function to Copy Files from Source Folder to Target  
  102. Function Copy-Files($SourceFolder, $TargetFolder)  
  103. {  
  104.     write-host "Copying Files from:$($SourceFolder.URL) to $($TargetFolder.URL)"  
  105.     #Get Each File from the Source  
  106.     $SourceFilesColl = $SourceFolder.Files  
  107.   
  108.     #Iterate through each item from the source  
  109.     Foreach($SourceFile in $SourceFilesColl)  
  110.     {  
  111.         #Copy File from the Source  
  112.         $NewFile = $TargetFolder.Files.Add($SourceFile.Name, $SourceFile.OpenBinary(),$True)  
  113.    
  114.         #Copy Meta-Data from Source  
  115.         Foreach($Field in $SourceFile.Item.Fields)  
  116.         {  
  117.             If(!$Field.ReadOnlyField)  
  118.             {  
  119.                 if($NewFile.Item.Fields.ContainsField($Field.InternalName))  
  120.                 {  
  121.                     $NewFile.Item[$Field.InternalName] = $SourceFile.Item[$Field.InternalName]  
  122.                 }  
  123.             }  
  124.         }  
  125.         $NewFile.Item['File Version History'] = GetVersionHistory $SourceFile.Item  
  126.         #Update  
  127.         $NewFile.Item.UpdateOverwriteVersion()  
  128.        
  129.         Write-host "Copied File:"$SourceFile.Name  
  130.     }  
  131.       
  132.     #Process SubFolders  
  133.     Foreach($SubFolder in $SourceFolder.SubFolders)  
  134.     {  
  135.         if($SubFolder.Name -ne "Forms")  
  136.         {  
  137.             #Check if Sub-Folder exists in the Target Library!  
  138.             $NewTargetFolder = $TargetFolder.ParentWeb.GetFolder($SubFolder.Name)  
  139.     
  140.             if ($NewTargetFolder.Exists -eq $false)  
  141.             {  
  142.                 #Create a Folder  
  143.                 $NewTargetFolder = $TargetFolder.SubFolders.Add($SubFolder.Name)  
  144.             }  
  145.             #Call the function recursively  
  146.             Copy-Files $SubFolder $NewTargetFolder  
  147.         }  
  148.     }  
  149. }  
  150.  
  151. #Variables for Processing  
  152. $WebURL="http://xxxxxxxxxxxxxxxxxxxxxxxxx/sites/SPTesting/"  
  153. $SourceLibrary ="Source Library"  
  154. $TargetLibrary = "Destination Library"  
  155.   
  156. #Get Objects  
  157. $web = Get-SPWeb $WebURL  
  158. $SourceFolder = $web.GetFolder($SourceLibrary)  
  159. $TargetFolder = $web.GetFolder($TargetLibrary)  
  160.   
  161. #Call the Function to Copy All Files  
  162. Copy-Files $SourceFolder $TargetFolder