Bulk Delete SharePoint Items in a Large List Using PowerShell

  • Article

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

 

For instance, if you have a large list/library, and you run a code as the one below, you will cause a SQL lock for all the users, because you are retrieving all the list/library items.

 $web = get-spweb "https://sp2013"
$list = $web.lists["Document"]
$items = $list.Items

 

For this reason, we released our own best practices for coding, which is also applicable for scripting, (https://msdn.microsoft.com/en-us/library/bb687949(v=office.12).aspx) . Normally, you should stay away from retrieving all the list/library items while coding. For instance and as documented in the same article, use SPList.GetItemById(System.Int32) instead of SPList.Items[System.Int32]

SPList.Items[System.Int32] : Will get all the list/library items, and then gets the desired item with the specified ID.

SPList.GetItemById(System.Int32) : will get only the item with the specified ID 

 

So, what if I want to delete all the items in a certain library/list using PowerShell? - In fact, I had this request as one of our customers had a library with a very huge size (~782 GB). Deleting the whole library moved it to the recycle bin, and deleting it from the recycle bin was taking like forever.

 

In order to achieve this, I found the below PowerShell code (Bad Coded):

 $web = get-spweb "Site URL"
$list = $web.lists["Library Title"]
$items = $list.Items
$itemsCount = $list.Items.Count
for($i= 0;$i -le $itemsCount;$i++)
{
   $items[$i].delete()
}

 

However, this script is not following our recommendations at all... Searching online showed the Patch Deletion Technique, but unfortunately this was moving the items to the recycle bin, and you have then to delete the items from the recycle bin. Hence, I started thinking of another way to achieve this by using our recommendations for the large lists, and wrote the below:

 

 Add-PSSnapin Microsoft.SharePoint.Powershell -ea SilentlyContinue
$web = get-spweb "Site URL"
$list = $web.lists["Library Title"]
$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)

 

N.B., 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

 

Comparing the above code with the bad coded one shows that deleting basic 600 items from a list took 14 seconds; whilst it took 6 minutes and 16 seconds when using the bad coded script.