Remove a lot of elements from very large SharePoint Lists

Lists in SharePoint is only look like database tables but not they are. For some things they are even better than databases – for common users for example, but for developers and administrators they are often not so good and friendly.

Let’s look for such a common thing in databases like "Truncate". To clear all the rows in a database table you need only to write "Truncate TABLE table_name" and execute it.

With SharePoint Lists it’s not so easy.

Some times earlier I had to delete data from list, but it was a list with small quantity of rows, about 5 or 10 thousands. But removing items from that list lasted about 10-15 minutes. It’s too long.

Recently I had to remove items from a list with 100k rows. I couldn’t even think of waiting for so long and started to look for solution.

I found a way of removing SharePoint list items using Batch.

But my task was some more difficult – I had to remove only first 90k rows of 100k. Below I write a powershell script for this action.

But it only moves items to SharePoint Recycle Bin. I will write how to clean deleted items from SharePoint Recycle Bin soon.

#Script to remove items from huge lists
Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue   
cls

$web = get-spweb "https://spdev/MySite/"

$listname = "myFriends"
$list = $web.Lists[$listname]
$listItems = $list.ItemCount
Write-Host $listItems

$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewAttributes = "Scope='Recursive'";
#$perPage  - variable to manage  volume of deletable items per cycle
$perPage = 100
$spQuery.RowLimit = $perPage

#Count Items in SPList
$totalListItems = $list.ItemCount
Write-host "Total elements: "  $listItems 

#$minCount - The quantity you want to stay in the SPList
$minCount = 10000

#$cycles - how many SPListItems you need to remove
$cycles = $totalListItems - $minCount

#It's a query to remove from first iserted item, first in first out
$caml = '<OrderBy Override="TRUE"><FieldRef Name="ID"  Ascending="True" /></OrderBy>'

$q = 0
while($q -lt $cycles)  
{
    
#Marker to see that script works, mostly for debugging
Write-Host "-----------------------"
Get-Date

	$listItems = $list.GetItems($spQuery)	
	$spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
	$batch = "<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>"
	$j = 0
	for ($j = $perPage-1; $j -gt 0; $j--)
	{
		$item = $listItems[$j]
		$batch += "<Method><SetList Scope=`"Request`">$($list.ID)</SetList>
				<SetVar Name=`"ID`">$($item.ID)</SetVar>
				<SetVar Name=`"Cmd`">Delete</SetVar>
				</Method>"        
	    $q++
		if ($j -ge $count) { break } 
	}
		$batch += "</Batch>"
	
		write-host "Sending batch... " 
		$result = $web.ProcessBatchData($batch)
		
		write-host "Emptying Recycle Bin..."
        #If you want to remove all items from RecycleBin after removing from SPList, uncomment the string below.
        #But I don't recomment you to clean Recycle because it can contain data from other users and may be they will want to restore them
	#	$web.RecycleBin.DeleteAll()

}
    
	$web.Dispose()

#Script to remove items from huge lists

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue

cls

$web = get-spweb "https://spdev/MySite/"

$listname = "myFriends"

$list = $web.Lists[$listname]

$listItems = $list.ItemCount

Write-Host $listItems

$spQuery = New-Object Microsoft.SharePoint.SPQuery

$spQuery.ViewAttributes = "Scope='Recursive'";

#$perPage - variable to manage volume of deletable items per cycle

$perPage = 100

$spQuery.RowLimit = $perPage

#Count Items in SPList

$totalListItems = $list.ItemCount

Write-host "Total elements: " $listItems

#$minCount - The quantity you want to stay in the SPList

$minCount = 10000

#$cycles - how many SPListItems you need to remove

$cycles = $totalListItems - $minCount

#It's a query to remove from first iserted item, first in first out

$caml = '<OrderBy Override="TRUE"><FieldRef Name="ID" Ascending="True" /></OrderBy>'

$q = 0

while($q -lt $cycles)

{

#Marker to see that script works, mostly for debugging

Write-Host "-----------------------"

Get-Date

$listItems = $list.GetItems($spQuery)

$spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition

$batch = "<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>"

$j = 0

for ($j = $perPage-1; $j -gt 0; $j--)

{

$item = $listItems[$j]

$batch += "<Method><SetList Scope=`"Request`">$($list.ID)</SetList>

<SetVar Name=`"Cmd`">Delete</SetVar>

</Method>"

$q++

if ($j -ge $count) { break }

}

$batch += "</Batch>"

write-host "Sending batch... "

$result = $web.ProcessBatchData($batch)

write-host "Emptying Recycle Bin..."

#If you want to remove all items from RecycleBin after removing from SPList, uncomment the string below.

#But I don't recomment you to clean Recycle because it can contain data from other users and may be they will want to restore them

# $web.RecycleBin.DeleteAll()

}

$web.Dispose()

25 August 2014, admin
SharePoint
Tags: PowerShell, SPList, SPListItem
Comments (2)

[…] Remove a lot of elements from very large SharePoint Lists […]

Pingback by Remove Items from SharePoint Recycle filtered by URL « MarkiMarta.com — August 25, 2014 @ 4:01 pm

If your list is very large, do not perform a $list.ItemCount, as it will take a very long time.

Comment by Dave — October 2, 2019 @ 8:09 pm

Remove a lot of elements from very large SharePoint Lists

2 Comments »

Leave a comment

New posts