Different Methods To Do Bulk Delete Of SharePoint List items

Recently, I faced an issue in my Team site where the workflow history list consumed 96% of the space, and due to insufficient space, my Team site's performance became very poor.

When I tried to open the list, I got the following error. 

“This view cannot be displayed because it exceeds the list view threshold (100000 items) enforced by the administrator”.

Due to the exceeded threshold limit, I couldn’t check how many items were there to delete. I even tried to create a new view but couldn't see more than 100,000 items as enforced by the administrator.

Then, I decided to use the REST API to get to know the total number of items, so I used the below-mentioned syntax on the browser.

https://sharepoint.com/_api/webs/lists/GetByTitle(“Workflow History”)/itemcount

Finally, I found there are more than 55 lakh items that need to be deleted. It’s a very huge count really but the problem is I don’t have access to the application server to perform PowerShell script.

So as my first attempt, I planned to create a JavaScript button event to remove the list items in a single click but unfortunately, it deleted only a few items at a time.

<input type="button" id="btnSubmit" value="Delete All Items"/>
<br/>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script>
    $(function() {
        bindButtonClick();
    });
    function bindButtonClick() {
        $("#btnSubmit").on("click", function() {
            deleteAllItemsFromList();
        });
    }
    var clientContext;
    var website;
    var oList;
    var cnt = 0;
    function deleteAllItemsFromList() {
        clientContext = SP.ClientContext.get_current();
        website = clientContext.get_web();
        oList = website.get_lists().getByTitle('ListName');
        var camlQuery = new SP.CamlQuery();
        camlQuery.set_viewXml('<View><RowLimit>3000</RowLimit></View>');
        this.collListItem = oList.getItems(camlQuery);
        clientContext.load(website);
        clientContext.load(collListItem, 'Include(Id)');
        clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
    }
    function onQuerySucceeded(sender, args) {
        var listItemInfo = '';
        var listItemEnumerator = collListItem.getEnumerator();
        while (listItemEnumerator.moveNext()) {
            var oListItem = listItemEnumerator.get_current();
            var ID = oListItem.get_id();
            var oListItemDel = oList.getItemById(ID);
            oListItemDel.deleteObject();
            clientContext.executeQueryAsync(Function.createDelegate(this, this.onDeleteSucceeded), Function.createDelegate(this, this.onDeleteFailed));
        }
    }
    function onQueryFailed(sender, args) {
        alert('Failed');
    }
    function onDeleteFailed(sender, args) {
        alert('Failed');
    }
    function onDeleteSucceeded(sender, args) {
        cnt = cnt + 1;
        var waitDialog = SP.UI.ModalDialog.showWaitScreenWithNoClose('Loading...', '', 100, 330);
        setTimeout("location.reload(true);", 25000);
    }
</script>

Then, as a second attempt, I loaded all items to MS Access and performed a delete query from there; but it moved all the deleted items to the recycle bin.

The following steps helped me to load the SharePoint list items to MS Access.

Step 1. Choose a blank desktop database.

Step 2. Select external data from the ribbon, click "More", and choose SharePoint list,

Step 3. Enter your site collection URL and choose the link to the data source by creating a linked table.

Step 4. It will check for the credentials and then it will populate all the list and library structure on the next pop-up window.

Step 5. Select the respective list and click OK.

Step 6. Once the items are loaded, click "Create" from the ribbon select Query Design then choose the List.

Step 7. Click view and change the view to SQL Query, then type the query given below and click F5 to execute the query,

 

Delete from [SharePoint List] where ID between 1 and 100000  

The above-given query will delete the items from ID 1 to 100000, like this you can change the query based on your requirement. As I said earlier this method will move all items to your recycle bin so it will make it more complex to delete your recycle bin.

Then as my final attempt, I planned to create one PowerShell script using the CSOM method, since I don’t have access to the application server. Due to low system configuration, I could not delete all 55+ lakhs items at a time so I changed the view to 5000 items using CAML Query and loaded that PowerShell script in my local computer Task scheduler, it will execute the PowerShell script every 15 minutes to delete the mentioned 5000 items, this method took nearly 2 days to delete all 55+ Lakhs items from my team site.

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext "https://sharepoint.com/sites/subsite"
$listname = "Workflow History"
Do {
    $web = $clientContext.Site.RootWeb
    $clientContext.Load($web)
    $clientContext.ExecuteQuery()
    $list = $clientContext.Web.Lists.GetByTitle($listname)
    $clientContext.Load($list)
    $clientContext.ExecuteQuery()
    $query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $query.ViewXml = "<View><RowLimit>5000</RowLimit></View>"
    $items = $list.GetItems($query)
    $clientContext.Load($items)
    $clientContext.ExecuteQuery()
    if ($items.Count -gt 0) {
        for ($i = $items.Count - 1; $i -ge 0; $i--) {
            $items[$i].DeleteObject()
            Write-Host "$($items[$i]) deleted + $i"
        }
        $clientContext.ExecuteQuery()
        Write-Host "Operations Completed"
        Clear-Hosta
    }
} While ($items.Count -eq 0)
Write-Host "All Items are deleted in the List" -ForegroundColor Green