How to delete all items in a List using PowerShell

Question

I am using following PowerShell script to delete all items of the List. It's working fine but take too long for huge lists. Is there any efficient way of clearing list items, something like truncate command for table?

$SITEURL = "http://serverurl"

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

$oList = $web.Lists["Employee"];

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

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

Answer 1

The approach of deleting the list and recreating the list is the fastest & most efficient (since you are deleting all the list items) -

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

The above deletes the list and you can then recreate the list with its template, etc.

Answer 2

I took a stab at converting Servy'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 have very similar code with one code difference and mine is pretty fast.

Change

$collListItems.Delete($intIndex);

to

$collListItems[$intIndex].Delete()

I think adding ; at the end of each line is optional in PS.

Answer 4

Deleting items one at a time is quite inefficient. You're best bet, if it's a feasible option, is to just delete the entire list and re-create it. You can save it as a template first so that all of the column metadata will be maintained. The downside here is that you get a new GUID for the list, so references to the list by GUID (i.e. workflows, alerts, and certain other types of out of the box functionality) will break. This is very fast though, and it's a constant time regardless of the size of the list.

If that's not an option (which it always won't be) then the next best choice is to use batches. The SPWeb.ProcessBatchData method will allow you to send batches of deletions together (I usually like to send batches of 500-1000) which will be quite a bit faster than individual deletion. There are a lot of code examples of using this, but most of what I've found is all in C# code. If switching to a C# console app is an option you could use these examples directly, if not then it's certainly a possibility to convert them to powershell code as you still have access to the same library. Here is a link to the first example of using batch processing that I came across; but there are certainly others in google. I've coped the code on that page below for reference:

    // 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 5

I tested a number of different options I found online including an attempt to do concurrent background processing as well as batch requests and the following worked best for me. I did not want to delete the list itself.

Param(
    $listname,
    $url=$env:SPSITE #an env variable set at system level
)

Add-PSSnapin Microsoft.SharePoint.PowerShell
Start-SPAssignment –Global

$site  = Get-SPSite $url
$web   = $site.RootWeb
$list  = $web.Lists[$listname]
$items = @($list.Items)

while ($items) { 
    $item, $items = $items
    $item.ID
    $item.Delete()
}

Stop-SPAssignment –Global

Answer 6

You can streamline the CAML in PowerShell a little by leaving out the escape characters:

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

becomes:

 $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>'

Just encapsulate the entire string in single quotes rather than double.

Answer 7

Here is the complete working script.

$siteURL = "URL"
$site = new-object Microsoft.SharePoint.SPSite ( $siteURL )
$web = $site.OpenWeb()
$oList = $web.Lists["ListName"];
$collListItems = $oList.Items;
$count = $collListItems.Count -1;
for($intIndex = $count; $intIndex -ge 0; $intIndex--)
{
   $collListItems[$intIndex].Delete();
}

Answer 8

$listname.Items.Clear() empties your list. No need for batch or loop processing.

Answer 9

It's quite easy.

$Web = Get-SPWeb -identity "https://WEB"
$List = $Web.Lists["LIST"]

foreach ($Item in $List.Items)
{
    $List.GetItemById($Item.Id).delete()
}

Answer 10

This one was ideal for me: http://social.technet.microsoft.com/wiki/contents/articles/17895.powershell-script-to-delete-items-from-sharepoint-list.aspx

You just have to modify the CAML-Query.