1

I have a list with 28,000 items. I want to delete everything except the latest months worth of data. This will be basically deleting 27,000 items. I cannot view this list in data view as the view exceeds the base 5000 items. How can I delete these items? After it is done I will set up a retention policy.

3

You should use powershell. There you can easily set the filter etc.

To view the list you should either increase the Resource Throttling settings from Central Administration.

[void][System.reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

$site = new-object Microsoft.SharePoint.SPSite("http://sitecollectionURL") 
$web = $site.rootweb 
$list = $web.Lists["Name of the List"]

$caml='<Where>
        <Leq>
            <FieldRef Name="Created" />
            <Value IncludeTimeValue="TRUE" Type="DateTime">2011-12-31T02:32:20Z</Value>
        </Leq>
    </Where>'

$query=new-object Microsoft.SharePoint.SPQuery 
$query.Query=$caml | Write-Output

$items=$list.GetItems($query) 
$listItemsTotal = $items.Count;

for($x=$listItemsTotal-1;$x -ge 0; $x--)
{
        $items[$x].Delete()
}

$web.Dispose() 
$site.Dispose() 

powershell to delete items from a list where created date < "1/1/2011"

Changing Resource Throttling

  1. Login to Central Admin
  2. Go to Application Management -> Manage Web Applications
  3. Pick the Web application for which you want to change the threshold
  4. In the ribbon above, click General Settings. That will bring down a menu, from which you should pick Resource Throttling
  5. Change the value

Managing Large Lists

Video: Techniques for managing large lists

Manage lists and libraries with many items

Designing large lists and maximizing list performance (SharePoint Server 2010)

Retention Policies

http://blogs.msdn.com/b/ecm/archive/2011/10/12/policy-and-retention-in-sharepoint-server-2010.aspx

  • I get this error when I run the script: You cannot call a method on a null-valued expression. At C:\Users\administrator\Desktop\List item Delete filter.ps1:17 char:22 + $items=$list.GetItems <<<< ($query) + CategoryInfo : InvalidOperation: (GetItems:String) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull – JazzyN Dec 9 '14 at 12:42
1

Don't overcomplicate things here... you just need to do it once and there is a simple no-code solution...

Just link the list to an Access database and run a delete query on the list. Access handles large lists quite nicely, plus you don't need to modify your list view threshold, you don't need to write any code, and anyone with write access to the list can do it -- no special privileges required.

If you aren't super comfortable with making a delete query in Access (which is fairly easy I should add), you can just use the table view and delete the individual records you want, similar to using Excel.

You should be aware it will take a while to delete lots and lots of records like that -- Access may look like it's given up on you, but give it a bit of time and it will get things done.

1

Just in case someone finds it useful, you can also disable the list view threshold for a single list using powershell.

$web = Get-SPWeb http://server.com/web

$list = $web.Lists[“BIG_LIST_NAME”]

$list.EnableThrottling = $false

Personally I would delete everything during out of work hours, when the administrator time window that temporarily disables the LVT should also be running. In that case there is no need to disable it by code or using the Central Administration

  • If I run this PS on my list will it allow me to see all 28,000 items in the data view? thanks – JazzyN Dec 9 '14 at 11:45
  • Yes. But disabling the LVT can have serious performance implications. – yandr Dec 10 '14 at 17:13
0

Since the number of items is large, I would suggest using SPWeb.ProcessBatchD. It is an efficient way of deleting large number of items. Here is a sample server object model code:

private static StringBuilder BatchCommand(SPList spList)
{
    StringBuilder deletebuilder = new StringBuilder();
    deletebuilder.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");
    string command = "<Method><SetList Scope=\"Request\">" + spList.ID +
        "</SetList><SetVar Name=\"ID\">{0}</SetVar><SetVar Name=\"Cmd\">Delete</SetVar></Method>";

    foreach (SPListItem item in spList.Items)
    {
        deletebuilder.Append(string.Format(command, item.ID.ToString()));
    }
    deletebuilder.Append("</Batch>");
    return deletebuilder;
}

Usage

using (SPSite spSite = new SPSite(site))
{
    using (SPWeb spWeb = spSite.OpenWeb())
    {
        StringBuilder deletebuilder = BatchCommand(spWeb.Lists[list]);

        spSite.RootWeb.ProcessBatchData(deletebuilder.ToString());
    }
}

Ofcourse you will need to set SPQuery object to restrict the number of items in your case.

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.