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

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);
} 
share|improve this question
    
possible duplicate : sharepoint.stackexchange.com/questions/26542/… –  Servy Jun 8 '12 at 15:09
    
Works beautifully. Thanks :-) sharepoint.stackexchange.com/a/51669/10214 –  user1545314 Mar 4 '13 at 8:40

7 Answers 7

up vote 7 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
1  
This shouldn't have any significant impact. Did you measure it? If so, what was the difference? In any case, batch deletion or recreating the list will both be much quicker than deleting items one at a time. –  Servy Jun 8 '12 at 15:10
    
$collListItems[$intIndex].Delete() does seems comparatively fast. –  Firoz Ansari Jun 8 '12 at 15:17

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();
   }
  }
share|improve this answer

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."
share|improve this answer

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.

share|improve this answer

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();
}
share|improve this answer

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