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

possible duplicate : sharepoint.stackexchange.com/questions/26542/… — Servy, Jun 8 '12 at 15:09
Works beautifully. Thanks :-) sharepoint.stackexchange.com/a/51669/10214 — Christian Flem, Mar 4 '13 at 8:40

Supriyo SB Chatterjee · Accepted Answer · 2012-06-08 15:18:43Z

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.

Jon Grant · Accepted Answer · 2012-11-15 11:55:41Z

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

Jon Grant's script worked for me. However, I had to open powershell using: powershell -version 2 — user2051770, May 29 '15 at 15:01
You need to call .Dispose(); on your web and site objects. msdn.microsoft.com/en-us/library/… — Robert Kaucher, Dec 17 '15 at 16:17

Angie Heimerlake · Accepted Answer · 2012-06-08 15:09:12Z

6

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.

answered Jun 8 '12 at 15:09

Angie Heimerlake

1786 bronze badges

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

add a comment |

Community · Accepted Answer · 2017-04-13 12:41:30Z

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

Mario · Accepted Answer · 2014-11-18 18:17:02Z

2

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

edited Nov 18 '14 at 18:17

answered Nov 18 '14 at 18:03

Mario

4521 gold badge5 silver badges17 bronze badges

1

This worked quite well. In my case, I am using related lists, so deleting and re-creating a new list was not an option. I needed a script to erase items in related lists without affecting the parent list. Your script has the added benefit of displaying the ID of the item being deleted, which was handy. – Alan M Jun 10 '15 at 23:33

add a comment |

Robert Lindgren · Accepted Answer · 2014-06-26 18:19:58Z

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.

Varun Verma · Accepted Answer · 2014-09-12 12:21:29Z

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

Robert Lindgren · Accepted Answer · 2015-03-17 07:36:08Z

0

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

edited Mar 17 '15 at 7:36

Robert Lindgren

24.1k9 gold badges46 silver badges72 bronze badges

answered Mar 16 '15 at 22:43

David H

1

Method invocation failed because [Microsoft.SharePoint.SPListItem] does not contain a method named 'Clear'. – Nullldata Feb 22 '18 at 16:27

add a comment |

Nullldata · Accepted Answer · 2018-02-22 16:07:03Z

0

It's quite easy.

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

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

answered Feb 22 '18 at 16:07

Nullldata

6975 silver badges21 bronze badges

add a comment |

Kai · Accepted Answer · 2015-03-26 10:57:37Z

-1

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.

answered Mar 26 '15 at 10:57

Kai

1

add a comment |

Stack Exchange Network

current community

your communities

more stack exchange communities

How to delete all items in a List using PowerShell

10 Answers 10

Not the answer you're looking for? Browse other questions tagged list powershell delete or ask your own question.

Linked

Hot Network Questions

How to delete all items in a List using PowerShell

10 Answers 10

Not the answer you're looking for? Browse other questions tagged list powershell delete or ask your own question.

Linked

Related

Hot Network Questions