Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by
  • When: 9 Aug 2013 1:12 PM
  • Last revision by (cMVP, Microsoft Community Contributo)
  • When: 18 Sep 2013 10:58 AM
  • Revisions: 5
  • Comments: 4
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  SharePoint: Using PowerShell to Perform a Bulk Delete Operation

SharePoint: Using PowerShell to Perform a Bulk Delete Operation

SharePoint: Using PowerShell to Perform a Bulk Delete Operation

Introduction


Have you ever written a really great PowerShell script for migrating data into SharePoint, only to have made the mistake of adding tens of thousands of new list items into the wrong SharePoint list?

Oops.

Have you ever tried deleting a few hundred list items using SPListItemCollection.DeleteItemById(id) or SPListItemCollection.Delete(index)? It's painfully slow, so it's never going to work (when you're in a hurry) with tens of thousands of items, not to mention the problems with list threshold exceptions!

There is a solution! Using PowerShell, use the SPQuery class to query the list for the items that need to be deleted, and then use the query results to build an XML document that can be used with SPWeb.ProcessBatchData to perform a batch delete operation. Easy, and much faster than waiting for a for-each loop to delete all those items using DeleteItemById(id)!

Example


In this example SPQuery is used to query the smarEnteredTerritorialWaters list for items created by the current user since the time of the import. The query results are then used to create an XML file to be used with the SPWeb.ProcessBatchData method. The example comes from a real world scenario where 32,000 items needed to be deleted.

Create the query:

$list = (Get-Spweb http://devmy101).GetList("http://devmy101/Lists/smarEnteredTerritorialWaters")
$query = New-Object Microsoft.SharePoint.SPQuery;
$query.ViewAttributes = "Scope='Recursive'";
$query.RowLimit = 2000;
$query.Query = '<Where><And><Eq><FieldRef Name="Author"/><Value Type="Integer"><UserID Type="Integer"/></Value></Eq><Gt><FieldRef Name="Created"/><Value Type="DateTime" IncludeTimeValue="TRUE">2013-07-10T14:20:00Z</Value></Gt></And></Where>';
Build the batch operation command (note the query is limited to returning 2000 items at a time, and uses the ListItemCollectionPosition property to continue retrieving items in batches of 2000 until all the items have been queried. See the MSDN documentation on SPQuery.ListItemCollectionPosition Property for more info.) 
$itemCount = 0;            
$listId = $list.ID;            
[System.Text.StringBuilder]$batchXml = New-Object "System.Text.StringBuilder";            
$batchXml.Append("");
$command = [System.String]::Format( "{0}{1}Delete", $listId, "{0}" );
do
{
$listItems = $list.GetItems($query)
$query.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
foreach ($item in $listItems)
{
if($item -ne $null){$batchXml.Append([System.String]::Format($command, $item.ID.ToString())) | Out-Null;}
}
}
while ($query.ListItemCollectionPosition -ne $null)
$batchXml.Append("");
Lastly, and most importantly, run the query!
$web = Get-Spweb http://inceweb/HKMarineDB;            
$web.ProcessBatchData($batchXml.ToString()) | Out-Null;

This content originally came from a blog post of Matthew Yarlett, that can be found here: Well that was fun! (Bulk deleting items from a SharePoint list) Credit is also given to the following authors, who were credited in the original blog post:

Tim Odell - powershell-function-to-delete-all-sharepoint-list-items
Kirk Evens - iterating-large-sharepoint-lists-with-powershell
, , , , [Edit tags]
Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 18 Sep 2013 10:58 AM

    Richard Mueller edited Revision 3. Comment: Removed extra space in tag "SharePoint  2010"

  • 10 Aug 2013 2:17 AM

    Matthew Yarlett edited Revision 2. Comment: Fixed HTML tags at the end of the document

  • 10 Aug 2013 2:13 AM

    Matthew Yarlett edited Revision 1. Comment: Minor changes to wording.

Page 1 of 1 (3 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Posted by
    on 9 Aug 2013 3:10 PM

    The link to the blog at the end seems to be truncated (the title, the link itself works)

  • Posted by
    on 10 Aug 2013 2:13 AM

    Matthew Yarlett edited Revision 1. Comment: Minor changes to wording.

  • Posted by
    on 10 Aug 2013 2:17 AM

    Matthew Yarlett edited Revision 2. Comment: Fixed HTML tags at the end of the document

  • Posted by
    on 18 Sep 2013 10:58 AM

    Richard Mueller edited Revision 3. Comment: Removed extra space in tag "SharePoint  2010"

Page 1 of 1 (4 items)