Truncate all Items in an SPList in SharePoint Using ProcessBatchData Command

This came up recently in regards to some of our test systems, where we were testing one of our systems that used large amounts of rows in an SPList.  As we were adding items through a scripted process “someone” (ok, it was me…) fat-fingered one of the scripts and next thing I knew we had roughly 30K rows from the wrong test bank sitting in our testing system.  Rather than just delete the SPList and start over I thought that there must be a better way to just truncate the table. 

All I could find were scripts that went through the item collection and called the .Delete() function on each item.  This delete process takes roughly a quarter of a second per item, with 30K items to delete we were looking at 2 hours to iterate through.  Not too bad, but not great when you’re sitting on a deadline.  So with a little investigation I found the ProcessBatchData function, which allows us to process multiple requests to the server per transaction.  Using this method we can pass XML formatted Collaborative Application Markup Language (CAML) that contains the commands, which consists of a Batch element and any number of subordinate Method elements that each specify a SharePoint Foundation remote procedure call (RPC) method.

It worked so well that I wrote it up as my very first Gallery submission to MSDN.

Here’s the link, if you like it, rate me a 5 Smile

http://gallery.technet.microsoft.com/sharepoint/Truncate-all-Items-in-an-2841c740

And for posterity, here is the original text of the submission… 

Introduction

This script will demonstrate how to delete Items from a Microsoft SharePoint list using Windows PowerShell and Microsoft SharePoint PowerShell snapin utilizing the ProcessBatchData command and building an XML command structure.

Scenario

Occasionally I have received requests to delete large amounts of data consisting of over 30,000 items. Previous scripts I used would iterate through the Items in the SPList and delete each item on a per item basis. However this was taking considerable time, using this method we can quickly delete items in batches of 100 even 1000 and remove these items.

Script

This script leverages the capability of Windows PowerShell and the ProcessBatchData to pass an XML request to the parent web to delete the list items in a batch.

You can use this script by following steps:

  1. Download the script and copy it to a Microsoft SharePoint 2010 Server which will be used for testing.
  2. Run the .\Truncate-SPList command inside SharePoint Management Powershell.
  3. Enter the URL and Name of the list ( or pass in parameters for -SiteURL and -ListName
  4. Confirm that you want to remove all items from the list ( ‘y’ )

Copy Code

 

## Truncate-SPList 
## Remove all items in a SharePoint List, batched up for processing in large chunks for a quick delete process 
## NOTE: This is not a true "Truncate", as SharePoint will never reuse an Item ID so seed is not reset 
## 
## Usage: Truncate-SPList -SiteUrl http://Server/Site  -ListName List 
## 
## Author:  
##    ieDaddy  
##    web: http://iedaddy.com 
##    twitter: @ieDaddy 
##              
##          
 
Param( 
    [Parameter(Mandatory=$true)] [String] $SiteUrl, 
    [Parameter(Mandatory=$true)] [String] $ListName 
    ) 
     
Function TruncateSPList  
{ 
    ## Parameter validation 
    Try  
    {  
        ## Use Get-SPWeb get the website we want, on error don't go further 
        $spWeb = Get-SPWeb -Identity $SiteURL -ErrorAction Stop -Verbose:$false  
    }  
    Catch   
    {  
        #If Get-SPSite failed for any reason, this function will be terminated.  
        Write-Host $Error[0] 
        return $null  
    }  
    ## Get the specified list, $spList is a instance of Microsoft.SharePoint.SPList class  
    $spList = $spWeb.Lists[$ListName] 
 
     
    if ($spList -ne $null)  
    { 
        ## This looping goes through one by one, but maybe a better way? 
        #ForEach ($item in $spList.items) 
        #{ 
        #    $ItemToDelete=$splist.GetItemById($item.ID) 
        #    write-host 'Deleting : ' $ItemToDelete.ID 
        #    $ItemToDelete.Delete() 
        #} 
         
        ## Set up a do..until loop so we can batch the removals 100 records at a time     
        do  
        { 
            ## Set up our Items Collection for Batch Processing 
            $spQuery = new-object Microsoft.SharePoint.SPQuery  
            $spQuery.RowLimit = 100 
            $spQuery.Query = "" 
            ## Get collection of items to be deleted for the batch delete 
            $spListItemCollection = $spList.GetItems($spQuery) 
            $batchRemove = '<?xml version="1.0" encoding="UTF-8"?><Batch>';    
            ## The command is built out to iterate through the ItemCollection to build out batch command 
            $command = '<Method><SetList Scope="Request">' +   
                $spList.ID +'</SetList><SetVar Name="ID">{0}</SetVar>' +   
                '<SetVar Name="Cmd">Delete</SetVar></Method>';    
            foreach ($item in $spListItemCollection)   
            { 
                $batchRemove += $command -f $item.Id;   
            }   
            $batchRemove += "</Batch>";    
             
            ## Remove the list items using the batch command  
            $spList.ParentWeb.ProcessBatchData($batchRemove) | Out-Null 
 
        } until ( $spList.GetItems().Count -eq 0 ) 
    } 
    Else 
    { 
        Write-Host $Error[0] 
        return $null  
    } 
 
    ## Dispose SPWeb object, it's just good manners 
    $spWeb.Dispose()  
} 
 
$confirm = Read-Host "This script will delete all items from the list, Proceed [y/n]" 
if ($confirm -ne 'y') 
{ 
    Exit 
}     
TruncateSPList 
 
 
 
 

Prerequisite

Windows PowerShell 2.0

Microsoft SharePoint 2010 PowerShell Snapin