Published: 17.Jul 2020
Categories:

Bulk Delete SharePoint Items in a Large List Using PowerShell

While dealing with SharePoint lists/libraries which have huge number of items (more than 5,000 items), you have to be very cautious. Whereas, any bad coded script may cause a performance issue for your SharePoint sites. Briefly, to minimize database contention, SQL Server uses row-level locking as a strategy to ensure accurate updates without adversely affecting users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at the same time, then it is more efficient for SQL Server to escalate the lock to the entire table until the database operation is completed. When this lock escalation occurs, it prevents other users from accessing the table (Performance Issue)

Reference:
https://technet.microsoft.com/en-us/library/cc262813(v=office.14).aspx

Add-PSSnapin Microsoft.SharePoint.Powershell -ea SilentlyContinue

##  SP URL
Write-Host "Provide SharePoint URL:" -ForegroundColor Yellow
$webURL = Read-Host
$web = Get-SPweb $webURL

## LIST NAME
Write-Host "Enter name of the list:" -ForegroundColor Yellow
$listName = Read-Host
$list = $web.lists[$listName]

## SET QUERY
$query = New-Object Microsoft.SharePoint.SPQuery
$query.ViewAttributes = "Scope='Recursive'"
$query.RowLimit = 1000
$query.ViewFields = "<FieldRef Name='ID'/>"
$query.ViewFieldsOnly = $true


## EXECUTE
do
{
    $listItems = $list.GetItems($query)
    $query.ListItemCollectionPosition = $listItems.ListItemCollectionPosition

    foreach ($item in $listItems)
    {
        Write-Host "Deleting Item - $($item.Id)" -ForegroundColor Yellow
        $list.GetItemById($item.Id).delete()
    }
}
while ($null -eq $query.ListItemCollectionPosition)

Write-Host "Script finished." -ForegroundColor Green

The above script will delete all the items in the list/library permanently (no recycle bin); so, ensure that you are deleting the items from the right library on the correct site.

Reference:
https://docs.microsoft.com/en-us/archive/blogs/ahmedamin/bulk-delete-sharepoint-items-in-a-large-list-using-powershell

5 3 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
PowerAddict.net - Lubomir Goban  ---  Find me on - 
LinkedIn
0
Would love your thoughts, please comment.x
()
x