0

I found the following script for deleting items from a large list, which works perfectly. However, I'd like to update so that rather than deleting all items, it only deletes everything that is older than 90 days. Can someone point me towards what needs to be modified in order to make that happen?

Add-PSSnapin Microsoft.SharePoint.Powershell -ea SilentlyContinue
$web = get-spweb "http://mywebapp"
$list = $web.lists["ReminderEmailsLog"]
$query = New-Object Microsoft.SharePoint.SPQuery
$query.ViewAttributes = "Scope='Recursive'"
$query.RowLimit = 1000
$query.ViewFields = "<FieldRef Name='ID'/>"
$query.ViewFieldsOnly = $true
do
{
$listItems = $list.GetItems($query)
$query.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
foreach($item in $listItems)
{
 Write-Host "Deleting Item - $($item.Id)"
 $list.GetItemById($item.Id).delete()
 }
 }
 while ($query.ListItemCollectionPosition -ne $null) 
2

You'll need to add onto the CAML query to only bring back documents older than 90 days. Here's a sample query:

    <Where>
        <Leq>
            <FieldRef Name="Created" />
            <Value Type="DateTime">
                <Today OffsetDays="-90" />
            </Value>
        </Leq> 
    </Where>

This CAML query looks at the "Created On" column and filters out any whose creation date is older than 90 days from the day you execute the script. If you want to filter on a different column, replace that column name with the internal name of your column.

Here's some lines of code to fit it into your PowerShell script. Please note that I haven't personally tested this script in particular, so please test it before blindly deleting documents. :)

Add-PSSnapin Microsoft.SharePoint.Powershell -ea SilentlyContinue
$web = get-spweb "http://mywebapp"
$list = $web.lists["ReminderEmailsLog"]
$query = New-Object Microsoft.SharePoint.SPQuery

# Added Code Start
$createdOnInternalFieldName = 'Created'
$caml = '<Where><Leq><FieldRef Name="{0}" /><Value Type="DateTime"><Today OffsetDays="-90" /></Value></Leq></Where>'-f $createdOnInternalFieldName
$query.Query = $caml
# Added Code End

$query.ViewAttributes = "Scope='Recursive'"
$query.RowLimit = 1000
$query.ViewFields = "<FieldRef Name='ID'/>"
$query.ViewFieldsOnly = $true
do
{
    $listItems = $list.GetItems($query)
    $query.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
    foreach($item in $listItems)
    {
        Write-Host "Deleting Item - $($item.Id)"
        $list.GetItemById($item.Id).delete()
    }
}
while ($query.ListItemCollectionPosition -ne $null) 

Edit: corrected the CAML query.

  • Thanks! I actually ran it (in my test environment) but rather than removing only items with a created date older than 90 days, it still deleted everything. I'll play around with it some more. – THTX Oct 16 '18 at 16:26
  • The CAML query might be a little wonky. I think I need to remove the <Query> tags from $caml. Can you try that and let me know if it works for you? – Jeff Freeman Oct 16 '18 at 17:43
0

Simple but slow way

$w = Get-SPWeb http://devvm/sites/tests 
$l = $w.Lists["Large List"]
$l.Items|?{$_["Created"] -lt [DateTime]::Now.AddDays(-90)}|%{$_.ID}|%{$l.GetItemById($_).Delete()}

If you want to delete a lot of items from a large list, you should use batch delete. The batch delete will work much faster than deleting item by item. Code below (the CAML query stolen from Jeff Freeman answer):

$w = Get-SPWeb http://devvm/sites/tests 
$list = $w.Lists["Large List"]
$query = New-Object Microsoft.SharePoint.SPQuery;
$query.ViewAttributes = "Scope='Recursive'";
$query.RowLimit = 1000;

$createdOnInternalFieldName = 'Created'
$caml = '<Where><Leq><FieldRef Name="{0}" /><Value Type="DateTime"><Today OffsetDays="-90" /></Value></Leq></Where>'-f $createdOnInternalFieldName
$query.Query = $caml

$cmdFormat = "  <Method ID=`"Del{0}`">
    <SetList Scope=`"Request`">"  + $list.ID + "</SetList>`
    <SetVar Name=`"Cmd`">Delete</SetVar>`
    <SetVar Name=`"ID`">{0}</SetVar>`
  </Method>`r`n"
$len = 0;
do {
    $listItems = $list.GetItems($query)
    $len = $listItems.Count
    if($len -gt 0){
        $cmdString = "<?xml version=`"1.0`" encoding=`"UTF-8`"?>`r`n<Batch OnError=`"Continue`">`r`n"

        $listItems|%{ $cmdString += [string]::Format($cmdFormat, $_.ID) }
        $cmdString += "</Batch>`r`n"

        [Void]$w.ProcessBatchData($cmdString)
        Write-Host "Deleted $($len) items"
    }
} while($len -gt 0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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