Deleting all the items from a large list in SharePoint

Question

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.

Answer 1

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();
   }
  }

Answer 2

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."

Answer 3

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.

Answer 4

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.ServerRelativeUrl.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.");
}

Answer 5

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 ?

Answer 6

PowerShell is the way to go. The following will parse 500 items at a time into the recycle bin and then remove them from the recycle bin. I've used this on lists up to 700k items.

# Enter your configuration here
$siteUrl = "http://sitecollectionurl"
$listName = "Name of List"
$batchSize = 500

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."

I did see some formatting errors when copying into the forum but the script is solid.

Answer 7

I know this is old but...

Easiest way for me is to open in access view,

• Select "Link to data on the SharePoint site"
• Select all items and "Delete"
• Save changes to SharePoint site

DONE :)

Answer 8

#Check and add SharePoint PowerShell snap-in
if(-not(
Get-PSSnapin | Where { $_.Name -eq "Microsoft.SharePoint.PowerShell"})
) {
Add-PSSnapin Microsoft.SharePoint.PowerShell;
}
$SITEURL = "http://sitename"

$site = new-object Microsoft.SharePoint.SPSite ( $SITEURL )
$web = $site.OpenWeb()

$oList = $web.Lists["Catering Consumption"];

$collListItems = $oList.Items;
$count = $collListItems.Count - 1

for($intIndex = $count; $intIndex -gt -1; $intIndex--)
{
    $collListItems.Delete($intIndex);
} 

above method might be slow in bigger lists, to delete bigger lists faster, you can go for below method

Get-SPWeb $FooUrl | % { $_.Lists.Delete([System.Guid]$_.Lists["Catering Consumption"].ID) }

Please note in above example list name is : Catering Consumption

Answer 9

What about linking to the list in Access, then doing a SQL query: DELETE * FROM [MyTable] ?

Answer 10

The answers below were certainly helpful. However, for those that are not "programmer" savvy, there is another way using Microsoft Access (part of the Office365 suite), but it's painfully slow, and it removes the record and it does not put it into the recycle bin.

I had over 130,000 records in one list I created by accident. I used this method, by basically setting it to run and forgot about it. Next morning, all the records were gone and I was able to delete the List, without any errors.

1. Open the MS Access app

2. Goto External Data

3. New data source...

4. From Online Services...Sharepoint List

5. Fill in the requirements in the form

6. Make sure you've chosen the Link to the Data Source option

7. Select the list in question (if more than one)

8. Next, open a New Query window in Access

9. Type the DELETE sql command for the linked table:

   DELETE [myList] FROM [myList];

Run the query.

Answer 11

All you have to do is create a datasheet view with no filter, select all rows, and hit the delete button.

Answer 12

Maybe You can use this code:

protected void Page_Load(object sender, EventArgs e)
{
    SPSite site = new SPSite([Sharepoint Server]);
    SPWeb web = site.OpenWeb();
    web.AllowUnsafeUpdates = true;
    SPList list = web.Lists[List Name];

    deleteAllRerecords(list);
    web.Update();

}

public void deleteAllRerecords(SPList list)
{
    SPListItemCollection items = list.Items;
    for (int i = items.Count - 1; i >= 0; i--)
    {
        items[i].Delete();
    }

}