none
Bulk deletion sharepoint list items

    Question

  • Hi All,

    I have a requirement to delete bulk items from thousands of item. I thought using batch process and caml query to get the date range of items. The code execute fine without any error but when i check the list it is not deleting any items, please see below code and suggest

     using (SPSite _site = new SPSite("http://xyz:31200/"))
                {
                    SPWeb _web = _site.OpenWeb();
                    _web.AllowUnsafeUpdates = true;
                    SPList _list = _web.Lists["PDFDoc1"];
                    SPQuery _qry = new SPQuery();
                    _qry.Query = "<Where><Lt><FieldRef Name='PP_x0020_Date' /><Value IncludeTimeValue='FALSE' Type='DateTime'>2009-01-01</Value></Lt></Where>";
                    SPListItemCollection _listItems = _list.GetItems(_qry);
                    StringBuilder sbDelete = new StringBuilder();
                    sbDelete.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");
                    string command = "<Method><SetList Scope=\"Request\">" + _list.ID + "</SetList><SetVar Name=\"ID\">{0}</SetVar><SetVar Name=\"Cmd\">Delete</SetVar></Method>";
                    foreach (SPListItem item in _listItems)
                    {
                        sbDelete.Append(string.Format(command, item.ID.ToString()));
                    }
                    sbDelete.Append("</Batch>");
                    _site.RootWeb.ProcessBatchData(sbDelete.ToString());
                }


    MercuryMan

    Monday, August 19, 2013 10:25 AM

Answers

  • If you use batch processing then the items will always appear in the recycle bin. Your options are to either disable the recycle bin before hand or empty it out afterwards. Bulk deletion of everything in there is the only viable choice, you might be able to delete the items individually from the recycle bin but doing so will render the process even slower than the other options, don't bother trying.

    If you want to delete the items completely then you cannot use batch processing code. Use paging to step through the relevant items.


    Monday, August 19, 2013 2:47 PM

All replies

  • The documentation on MSDN for SP 2010 doesn't mention deletion. For SP2013 it does with the comment;

    "To use this method to delete a document in a Document Library, pass the file path to the owsfileref variable in the Method elements."

    Assuming this would work for 2010, I'm not seeing the file path in your method elements.


    w: http://www.the-north.com/sharepoint | t: @JMcAllisterCH | c: http://www.b-i.com

    Monday, August 19, 2013 12:14 PM
  • You can do this but it's badly documented and it took me ages to work out how to get it working.

    I'll try to dig my notes out but as far as i saw it doesn't have a substantial improvement in performance over getting the items by CAML, then deleting the items using the GUIDs. It also leaves the items littering the recycle bin.

    From memory, try executing with an account that has been set permission to run as a system account.

    Monday, August 19, 2013 12:25 PM
  • Even after adding the owsfileref varible in the Method element, it didn't work

    string command = "<Method><SetList Scope=\"Request\">" + _list.ID + "</SetList><SetVar Name=\"ID\">{0}</SetVar><SetVar Name=\"Cmd\">Delete</SetVar><SetVar Name='owsfileref'>item.File.ServerRelativeUrl</SetVar></Method>";


    MercuryMan

    Monday, August 19, 2013 1:25 PM
  • I am running with "Administrator" account

    MercuryMan

    Monday, August 19, 2013 1:26 PM
  • I tend to do this like;

    using (SPSite _site = new SPSite("http://xyz:31200/"))
                {
                    SPWeb _web = _site.OpenWeb();
                    _web.AllowUnsafeUpdates = true;
                    SPList _list = _web.Lists["PDFDoc1"];
                    SPQuery _qry = new SPQuery();
                    _qry.Query = "<Where>Get all the stuff</Where>";
                    SPListItemCollection _listItems = _list.GetItems(_qry);
                    ArrayList sbDelete = new ArrayList();
                    
                    foreach (SPListItem item in _listItems)
                    {
                        sbDelete.AddItem(item.ID);
                    }
                    
                    foreach (Guid id insbDelete)
                    {
    			_list.Items[id].Delete();
                    }
    
                }



    w: http://www.the-north.com/sharepoint | t: @JMcAllisterCH | c: http://www.b-i.com

    Monday, August 19, 2013 1:49 PM
  • I agree, we can do in that way too, but what if, we have lakhs of records?. I have done in that way using powershell, it is working fine if the amount of deleting records is less <2000-3000 but, if it is >7000 taking too much time to delete. Any ways, I am able to delete the items using below code.

     using (SPSite _site = new SPSite("http://xyz:31200/"))
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
                        SPWeb _web = _site.OpenWeb();
                        _web.AllowUnsafeUpdates = true;
                        SPList _list = _web.Lists["PDFDoc1"];
                        SPQuery _qry = new SPQuery();
                        _qry.Query = "<Where><Lt><FieldRef Name='PP_x0020_Date' /><Value IncludeTimeValue='FALSE' Type='DateTime'>2009-01-01</Value></Lt></Where>";
                        SPListItemCollection _listItems = _list.GetItems(_qry);
    
                        StringBuilder sbDelete = new StringBuilder();
                        sbDelete.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");
                        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);
                        foreach (SPListItem item in _listItems)
                        {
                            //sbDelete.Append(string.Format(command, item.ID.ToString()));
                            sbDelete.Append(string.Format(command, item.ID.ToString(), item.File.ServerRelativeUrl));
                        }
                        sbDelete.Append("</Batch>");
                        _site.RootWeb.ProcessBatchData(sbDelete.ToString());
                        _web.AllowUnsafeUpdates = false;
                        _web.Close();  
    
                    });
                }

    Question :

    When i am deleting the items, it is sitting under "RecycleBin". how can i delete the items permanently? other than below code

    _web.RecycleBin.DeleteAll();


    MercuryMan


    • Edited by MercuryMan Monday, August 19, 2013 2:43 PM
    Monday, August 19, 2013 2:42 PM
  • That would work for small sets of data, if you're going over the list view threshold you'd need paging or to run the query several times. Also you don't need that second loop, you'd normally use that approach if you were itterating through the .Items collection but since your _listitems object is in essence just a hash table of data you can just step through.

    #Set up the query object for future use
    $query= new-object -TypeName "Microsoft.SharePoint.SPQuery"
    $query.Query = $caml
    $query.RowLimit = $rowLimit 
    $col = $null
    
        #Use the SP query object to loop through matching items and delete them.
        do{    
            $col=$splist.GetItems($query)
           
                $col | % {   
                    #Line that specifies how the whatif statement works  
                    Write-Output ("  Deleting file named = {0}" -f $_.Name)   
                    if ($pscmdlet.ShouldProcess($_.Name, ("Delete file in {0}" -f $folder.name)))
                    {
                       $splist.GetItemByUniqueId($_.UniqueId).Delete()
                    }                 
                }
                #Set the start point for the next query
                $query.ListItemCollectionPosition = $col.ListItemCollectionPosition
            }
        while ($col.ListItemCollectionPosition -ne $null)

    That's a snippet of a larger script I wrote to clean out a large number of items in a list, the ShouldProcess cmdlet is so that you can use the -WhatIf flag, the caml gets defined elsewhere as does the list item.

    From memory it got through something like 10 items per second, i looked at batch processing but it didn't seem to improve the rate much more and it was much more difficult. We didn't need any more speed but breaking the code above up into multiple threads looked like a better option for faster processing.


    Monday, August 19, 2013 2:42 PM
  • If you use batch processing then the items will always appear in the recycle bin. Your options are to either disable the recycle bin before hand or empty it out afterwards. Bulk deletion of everything in there is the only viable choice, you might be able to delete the items individually from the recycle bin but doing so will render the process even slower than the other options, don't bother trying.

    If you want to delete the items completely then you cannot use batch processing code. Use paging to step through the relevant items.


    Monday, August 19, 2013 2:47 PM
  • "Any ways, I am able to delete the items using below code"

    Setting the value of owsfileref did the trick after all huh? :)

    Your options for Recycle Bin are very limited. You can DeleteAll or turn it off before your operation. Both risk deleting items other than the ones you're operating on. 


    w: http://www.the-north.com/sharepoint | t: @JMcAllisterCH | c: http://www.b-i.com

    Monday, August 19, 2013 3:37 PM
  • Hi Alex,

    Could you please help me in getting the above complete powershell script with paging to delete all the items.


    MercuryMan


    • Edited by MercuryMan Tuesday, August 20, 2013 7:09 AM
    Tuesday, August 20, 2013 7:09 AM