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

What is the most efficient way to delete all the items of a large list in SharePoint? The code should take Query throttling limits into consideration.

share|improve this question

5 Answers 5

The most efficient way to a lot of transaction in SharePoint is using of SPWeb.ProcessBatchData method. For deleting operations there are very helpfull post. Code exaple from that post:

    // We prepare a String.Format with a String.Format, this is why we have a {{0}} 
   string command = String.Format("<Method><SetList Scope=\"Request\">{0}</SetList><SetVar Name=\"ID\">{{0}}</SetVar><SetVar Name=\"Cmd\">Delete</SetVar><SetVar Name=\"owsfileref\">{{1}}</SetVar></Method>", list.ID);
   // We get everything but we limit the result to 100 rows 
   SPQuery q = new SPQuery();
   q.RowLimit = 100;

   // While there's something left 
   while (list.ItemCount > 0)
   {
    // We get the results 
    SPListItemCollection coll = list.GetItems(q);

    StringBuilder sbDelete = new StringBuilder();
    sbDelete.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");

    Guid[] ids = new Guid[coll.Count];
    for (int i=0;i<coll.Count;i++)
    {
     SPListItem item = coll[i];
     sbDelete.Append(string.Format(command, item.ID.ToString(), item.File.ServerRelativeUrl));
     ids[i] = item.UniqueId;
    }
    sbDelete.Append("</Batch>");

    // We execute it 
    web.ProcessBatchData(sbDelete.ToString());

    //We remove items from recyclebin
    web.RecycleBin.Delete(ids);

    list.Update();
   }
  }
share|improve this answer
4  
Cannot resist mentioning, that this will move items to recycle bin instead of actually deleting them. –  Andrey Markeev Jan 10 '12 at 11:40
    
Didn't think of that. Nice catch. –  Dennis G Jan 10 '12 at 12:25
    
Thanks for remark! I updated code with batch deleting items from recycle bin. –  IAfanasov Jan 10 '12 at 12:30
1  
@Tom Resing we used this method in same scenario: list contains thousands items (in production it can grow to dozens of thousands) and per one operation we needed to delete hundreds items. We didn't measure perfomance, but it noticeably increased. –  IAfanasov Jan 11 '12 at 6:11
1  
thanks IAfanasov, but I get an error when I call : web.RecycleBin.Delete(ids); I store all the ListItem Unique GUID's in the GUID array, but still get the below mentioned error, any ideas: Delete failed, Error Message: Value does not fall within the expected range. Error Stack Trace: at Microsoft.SharePoint.SPRecycleBinItemCollection.GetSort edIds(Guid[] ids) at Microsoft.SharePoint.SPRecycleBinItemCollection.Delete(Guid[] ids) at CleanupWorkflowHistory.Program.PurgeHistoryList(String pSiteUrl, String pW ebUrl, String pListName, Int32 pNumDays, UInt32 pMaxHistoryListDelete, Boolean p ReportOnl –  user8217 May 6 '12 at 11:23

I like IAfanasov's response. ProcesBatchData is the way to go.

But you are asking about the "most efficient way to delete all the items of a large list". The most efficient way is to delete the list and re-create it, if you really want to delete all items. It will be a lot quicker than doing any iterative actions.

share|improve this answer
4  
This approach is definitely acceptable in some cases, but you should take into account, that the lookup fields, which point to the deleted list, will be broken. Also, all the list views will be removed from all pages and from all sites. –  Andrey Markeev Jan 10 '12 at 11:42
2  
Would it help to save the list as a template and NOT include the content? That should save the columns and views and permissions and so on... –  AlexPoint Jan 10 '12 at 12:02
    
@AlexPoint & omlin both correct. Saving the list as a template should take care of columns, views, permissions etc. - but sometimes it doesn't work, so I would first try it out before deleting the old list completely. –  Dennis G Jan 10 '12 at 12:24

I took a stab at converting IAfanasov's answer into PowerShell. Seems to work for me, but would be interested in any feedback.

[System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")

write-host 

# Enter your configuration here
$siteUrl = "http://mysharepointsite.example.com/"
$listName = "Name of my list"
$batchSize = 1000

write-host "Opening web at $siteUrl..."

$site = new-object Microsoft.SharePoint.SPSite($siteUrl)
$web = $site.OpenWeb()
write-host "Web is: $($web.Title)"

$list = $web.Lists[$listName];
write-host "List is: $($list.Title)"

while ($list.ItemCount -gt 0)
{
  write-host "Item count: $($list.ItemCount)"

  $batch = "<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>"
  $i = 0

  foreach ($item in $list.Items)
  {
    $i++
    write-host "`rProcessing ID: $($item.ID) ($i of $batchSize)" -nonewline

    $batch += "<Method><SetList Scope=`"Request`">$($list.ID)</SetList><SetVar Name=`"ID`">$($item.ID)</SetVar><SetVar Name=`"Cmd`">Delete</SetVar><SetVar Name=`"owsfileref`">$($item.File.ServerRelativeUrl)</SetVar></Method>"

    if ($i -ge $batchSize) { break }
  }

  $batch += "</Batch>"

  write-host

  write-host "Sending batch..."

  # We execute it 
  $result = $web.ProcessBatchData($batch)

  write-host "Emptying Recycle Bin..."

  # We remove items from recyclebin
  $web.RecycleBin.DeleteAll()

  write-host

  $list.Update()
}

write-host "Done."
share|improve this answer
    
Very Helpful and instructive –  imanabidi Jul 12 at 9:22

Navigate to DATA SHEET VIEW (List tools > List > data sheet view)

Just select & delete all the rows in the sheet and move back to STANDARD VIEW.

Does this sounds like an effective way using UI ?

share|improve this answer

In addition to IAfanosov's solution, I've written a method to clear the recycle bin from all items that were deleted from the given list:

/// <summary>
/// Deletes all items from the recycle bin in a web that originally came from the given list
/// </summary>
/// <param name="sourceList">List where the items originated from</param>
private static void TruncateRecycleBin(SPList sourceList)
{
    Console.WriteLine("Truncating recycle bin of all items deleted from list [{0}]", sourceList.Title);

    SPRecycleBinItemCollection recycleBin = sourceList.ParentWeb.RecycleBin;
    List<Guid> itemsToDelete = new List<Guid>();
    for (int i = 0; i < recycleBin.Count; i++)
    {
        // Only delete items from this specific list
        if (recycleBin[i].DirName.StartsWith(sourceList.RootFolder.ToString()))
        {
            itemsToDelete.Add(recycleBin[i].ID);
        }
    }

    Console.WriteLine("About to delete [{0}] items from recycle bin...", itemsToDelete.Count);
    if (itemsToDelete.Count > 0)
    {
        recycleBin.Delete(itemsToDelete.ToArray());
    }

    Console.WriteLine("Done truncating recycle bin.");
}
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.