PnP Batch Add /Delete 300k items from a SharePoint Online list

I was tasked to delete more 300k items from a SharePoint list generated as part of testing. I tried the script https://vladilen.com/office-365/spo/fastest-way-to-delete-all-items-in-a-large-list/

Get-PnPListItem -List $list -Fields "ID" -PageSize 100 -ScriptBlock { Param($items) $items | Sort-Object -Property Id -Descending | ForEach-Object{ $_.DeleteObject() } } 

However the script kept failing at irregular intervals with exception messages like “A task was cancelled”, “The collection has not been initialised” and “operation time out”. It was a long processing task which I need to keep monitoring and resumed the script manually to continue the deletion many times to my dismay. What I thought was a simple task ended being so tedious. I still needed a script which can delete 300k items without stopping each time an error is encountered due to throttling issue, token expiring or network connectivity issues.

I used the PnPBatch to add/delete 1 k items at one time as there is a limit on the number of operations that can be passed as part of PnPBatch

$action = Read-Host "Enter the action you want to perform, e.g. Add or Delete"
$siteUrl = "https://contoso.sharepoint.com/sites/Team1"
$listName = "TestDemo" 
$ErrorActionPreference="Stop"
Connect-PnPOnline –Url $siteUrl -interactive


$Stoploop = $false

[int]$Retrycount = "0"


write-host $("Start time " + (Get-Date))
do {
try {

if($action -eq "Add")
{   $lst = Get-PnPList -Identity $listName
    
    if($lst.ItemCount -lt 300000)
    {
       $startInc = $lst.ItemCount
       while($lst.ItemCount -lt 300000)
       {
      
       $batch = New-PnPBatch
        #perform in increment of 1000 until 300k is reached 
       if($startInc+1000 -gt 300000)
        {
         $endNu = 300000
        } 
        else
        {
        $endNu = $startInc+1000
        }
        for($i=$startInc;$i -lt ($endNu);$i++)
        {
            Add-PnPListItem -List $listName -Values @{"Title"="Test $i"} -Batch $batch
        }
        Invoke-PnPBatch -Batch $batch
         $lst = Get-PnPList -Identity $listName
       }
    }
}

if($action -eq "Delete")
{
 $listItems= Get-PnPListItem -List $listName -Fields "ID" -PageSize 1000  
$itemIds = $lisItems | Foreach {$_.Id}

$itemCount = $listItems.Count
while($itemCount -gt 0)
{
    $batch = New-PnPBatch
    #delete in batches of 1000, if itemcount is less than 1000 , all will be deleted 

    if($itemCount -lt 1000)
    {
     $noDeletions = 0
    }
    else
    {
     $noDeletions = $itemCount -1000
    }

    for($i=$itemCount-1;$i -ge $noDeletions;$i--)
    {
        Remove-PnPListItem -List $listName -Identity $listItems[$i].Id -Batch $batch 
    }
    Invoke-PnPBatch -Batch $batch
    $itemCount = $itemCount-1000
}

}

Write-Host "Job completed"

$Stoploop = $true

}

catch {

if ($Retrycount -gt 3){

Write-Host "Could not send Information after 3 retrys."

$Stoploop = $true

}

else {

  Write-Host "Could not send Information retrying in 30 seconds..."

  Start-Sleep -Seconds 30

  Connect-PnPOnline –Url $siteUrl -interactive

  $Retrycount = $Retrycount + 1

  }
 }
}
While ($Stoploop -eq $false)
write-host $("End time " + (Get-Date))

The script took nearly 4 hours to create 300k items in a SharePoint list with calling retry once.

The script took 7.5 hours to delete 300k items calling the retry twice.

Modern SharePoint Permission Management : update site members to contribute

In SharePoint Online when a team site is automatically created, the Site Members and Site Owners SharePoint groups are created by default with Edit and Full Control permissions. However the permissions levels of Site Members can not be edited from its default Edit value to Contribute for example.

You can argue we can create a custom SharePoint group with contribute permissions to restrict users from being able to manage lists and libraries however users added to custom SharePoint groups won’t benefit from other services in M365 ecosystem like teams, mailbox, etc… The best practice using SharePoint team sites is to use the M365 groups to edit permissions for full collaboration experience using connected services like teams. This add challenges to business who would like to have controlled contribute access to site members.

Though it might not be best practice to edit out of the box permission levels like edit , full control, etc… Permissions levels can be edit on the classic advanced permission settings page. In the example below I have edited the OOB Edit permission level to exclude “Manage Lists” and remove it Edit_ExcludeManageLists.

Prevent members in ‘Modern’ M365 group connected team sites to delete libraries

It is not possible for M365 to change the permissions of members from Edit to Contribute to prevent deletion or uncontrolled creation of lists and libraries. The option to “Edit User Permissions” is greyed out.

One option would be to create a custom SharePoint Group for contributors but anyone added to the custom SharePoint Group would not be part of the M365 group which means they won’t be able to contribute via Teams or any other M365 services.

One undocumented approach is to rename the Edit role to “Edit without manage lists” and amend the permissions to remove the ability to create or delete lists and libraries.

  1. Click on Permissions Levels from the classic advanced permissions page
  2. Click on Edit Permission
  3. Update the Name to “Edit without manage lists” and uncheck “Manage Lists”

All users in the members group will be granted “Edit without manage lists” permissions.

In a non m365 connected team site Members can easily be changed to contributors as per the blog post Prevent document library deletion | CaPa Creative Ltd

List View Threshold 5k in SharePoint Online

The list view threshold is 5000 in SharePoint Online and still can not be changed despite it has been raised in the User Voice for improvements.

I have created more than 5000 files in a library with the use of Power Automate to test what works and does not work.

I was surprised by navigating to the list or library I did not get any errors and everything seems to be working. However if I switch to classic view I get the message

“This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.
To view items, try selecting another view or creating a new view. If you do not have sufficient permissions to create views for this list, ask your administrator to modify the view so that it conforms to the list view threshold.”

Fortunately “exit the classic view” made the error disappear. Views exceeding the list view threshold of 5000 k work in the modern experience. As you filter and sort by a column , indexes are automatically created in the list indexed columns. In the screenshot below the Severity column has been added automatically when I was sorting the library using the column.

However automatic creation of indexes is limited to lists or libraries with less than 20 k items, otherwise you may encounter errors. In modern experience, automatic indexes are created with columns used for filtering and sorting in saved views or when sorting.

Also list view threshold error can happen if filtering or sorting by columns of type lookup, people and managed metadata.

Source: https://support.microsoft.com/en-us/office/manage-large-lists-and-libraries-b8588dae-9387-48c2-9248-c24122f07c59

Note: Displaying 12 or more columns of the following types can cause a list view threshold error: people, lookup, and managed metadata. Displaying columns of other types will not. “

Migrate data from one SQL table to another database table

Sometimes you may have to migrate data from one SQL database to another SQL database on another environment, e.g. Live to test and dev environment and vice versa.

You may argue the easiest solution is a migration of the backup of database. Unfortunately it does not work well in environments where data in Live is classified as sensitive and are not allowed to be copied across into a dev / test machine where the security is not as strict as a live machine. There are some data from live which are not classified as sensitive or restricted and might have to be migrated to Test and DEV for testing or development purposes, for example list of values.

A copy of the data can be exported as a csv or dat file from the SQL Table using “select * from table_value” statement from source database.

The data can be bulk imported into a temp table and MERGE statement can be used to insert missing records and update records. The sample script which you can use is below.

The script can be downloaded from TechNet.

BEGIN TRAN
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tmp_table_value') and xtype='U')
drop table #tmp_table_value
CREATE TABLE #tmp_table_value
([table_id] [int] NOT NULL,
[value_date] [datetime] NOT NULL,
[raw_value] [decimal](12, 6) NULL
)
BULK INSERT #tmp_table_value FROM 'C:\data.csv' WITH ( FIRSTROW = 2 , FIELDTERMINATOR = ',' , rowterminator = '\n',KEEPNULLS)
–table_value is the target table
— the temp table tmp_table_value is the source table
MERGE table_value AS T
USING #tmp_table_value AS S
ON (T.table_id = S. table_id and T.value_date = S.value_date)
WHEN NOT MATCHED
THEN INSERT(table_id, value_date, raw_value )
VALUES
( S.table_id, S.value_date, S.raw_value)
WHEN MATCHED AND (T.raw_value != S.raw_value )
THEN UPDATE SET T.raw_value = S.raw_value ;
select * from table_value order by value_date desc
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tmp_table_value') and xtype='U')
drop table #tmp_table_value
ROLLBACK TRAN;
–COMMIT TRAN;

Delete all list Item and file versions from site and sub sites using CSOM and PowerShell

The versions property is not available from client object model on the ListItem class as with server object model.  I landed on the article SharePoint – Get List Item Versions Using Client Object Model that describes how to get a list item versions property using the method GetFileByServerRelativeUrl by passing the FileUrl property. The trick is to build the list item url as “sites/test/Lists/MyList/30_.000” where 30 is the item id for which the version history needs to be retrieved. Using that information I created a PowerShell to loop through all lists in the site collection and sub sites to delete all version history from lists.

The script below targets a SharePoint tenant environment.

Please note that I have used script Load-CSOMProperties.ps1 from blog post Loading Specific Values Using Lambda Expressions and the SharePoint CSOM API with Windows PowerShell to help with querying object properties like Lambda expressions in C#. The  lines below demonstrate use of the  Load-CSOMProperties.ps1  file which I copied in the same directory where the script DeleteAllVersionsFromListItems.ps1 is.

         Set-Location $PSScriptRoot

        $pLoadCSOMProperties=(get-location).ToString()+”\Load-CSOMProperties.ps1″
       . $pLoadCSOMProperties

  •  Retrieve ServerRelativeURL property from file object

 Load-CSOMProperties -object $file -propertyNames @(“ServerRelativeUrl”); 

You can download the script from technet.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
Set-Location $PSScriptRoot
$pLoadCSOMProperties=(get-location).ToString()+"\Load-CSOMProperties.ps1"
. $pLoadCSOMProperties
Function DeleteVersionsFromLists($web, $versionCount)
{
#Get all lists in web
$ll=$web.Lists
$context.Load($ll)
$context.ExecuteQuery();
foreach($list in $ll)
{
if($list.EnableVersioning)
{
$rootFolder = $list.RootFolder
#Get items from list
$qry = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()
$items = $list.GetItems($qry)
$context.Load($items)
$context.Load($rootFolder);
$context.ExecuteQuery();
foreach($item in $items)
{
if($list.BaseType -eq "DocumentLibrary"){
$file = $item.File;
$context.Load($file);
$context.ExecuteQuery();
LoadCSOMProperties object $file propertyNames @("ServerRelativeUrl");
$context.ExecuteQuery() ;
$itemUrl = $file.ServerRelativeUrl
}
else
{
$itemUrl = $list.RootFolder.ServerRelativeUrl + "/" + $item.Id + "_.000";
}
# "/sites/prasad/teamsite/Lists/MyList/30_.000”
if($itemUrl -ne $null)
{
$versions = $context.Web.GetFileByServerRelativeUrl($itemUrl).Versions;
$context.Load($versions)
$context.ExecuteQuery()
$versionCount = $versions.Count
#adjust counter 0 to more if certain number of versions needs to be retained.
if($versionCount -gt 0)
{
for($i=0;$i -lt $versionCount ; $i++)
{
$versions[0].DeleteObject()
$context.ExecuteQuery()
}
}
}
}
}
}
}
$password = Read-Host Prompt "Enter password" AsSecureString
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials("reshmee@reshmee.onmicrosoft.com", $password)
#$siteUrl = "https://yourtenant.sharepoint.com/sites/yoursitecollection"
$siteUrl = "https://reshmee.sharepoint.com/sites/test1"
$context = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$context.Credentials = $credentials
$rootWeb = $context.Web;
$context.Load($rootWeb);
DeleteVersionsFromLists($rootWeb);
$childWebs = $context.Web.Webs;
$context.Load($childWebs);
$context.ExecuteQuery();
foreach ($_web in $childWebs)
{
DeleteVersionsFromLists($_web);
}

Rebuild and Reorganize indexes on all tables in MS database

As part of database maintenance, indexes on databases have to be rebuilt or reorganised depending on how fragmented the indexes are. From the article Reorganize and Rebuild Indexes, the advice is to reorganise index if avg_fragmentation_in_percent value is between 5 and 30 and to rebuild index if it is more than 30%.

The script below queries all fragmented indexes more than 5 percent and using a cursor a loop is performed on the results to rebuild or reorganise  indexes depending on the percentage of fragmentation using dynamic SQL, i.e.

The script can be downloaded from technet gallery  , i.e. if avg_fragmentation_in_percent value is between 5 and 30 then reorganise else rebuild.


declare @tableName nvarchar(500)
declare @indexName nvarchar(500)
declare @indexType nvarchar(55)
declare @percentFragment decimal(11,2)


declare FragmentedTableList cursor for
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE
-- indexstats.avg_fragmentation_in_percent , e.g. >30, you can specify any number in percent
indexstats.avg_fragmentation_in_percent > 5
AND ind.Name is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESC


OPEN FragmentedTableList
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment


WHILE @@FETCH_STATUS = 0
BEGIN
print 'Processing ' + @indexName + 'on table ' + @tableName + ' which is ' + cast(@percentFragment as nvarchar(50)) + ' fragmented'
if(@percentFragment<= 30)
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REBUILD; ')
print 'Finished reorganizing ' + @indexName + 'on table ' + @tableName
END
ELSE
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REORGANIZE;')
print 'Finished rebuilding ' + @indexName + 'on table ' + @tableName
END
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment
END
CLOSE FragmentedTableList
DEALLOCATE FragmentedTableList

SQL script batch execution using sqlcmd in PowerShell

There is often a mismatch between needs of the development team (multiple discreet T-SQL files for separate concerns) and the release team (the requirement  for one step automated deployment) . The script bridges the requirement by using sqlcmd.exe to run a batch of SQL scripts.

A text file is used listing all sql files that need to run in a particular order to avoid errors which may occur if there are dependencies between the scripts. Instead of using a text file a number can be prefixed to the scripts based on the order they need to run.

The script expects two parameters –

  • Path of folder containing the set of T-SQL files (and the manifest file, see below)
  • Connection string

The script can be downloaded from technet gallery.

## Provide the path name of the SQL scripts folder and connnection string
##.\SQLBatchProcessing.ps1 -SQLScriptsFolderPath "C:\Sql Batch Processing\SQLScripts" -ConnectionString "DEV-DB-01"
Param(
[Parameter(Mandatory=$true)][String]$ConnectionString ,
[Parameter(Mandatory=$true)][String]$SQLScriptsFolderPath
)
Set-ExecutionPolicy -ExecutionPolicy:Bypass -Force -Confirm:$false -Scope CurrentUser
Clear-Host
#check whether the SQL Script Path exists
$SQLScriptsPath = Resolve-Path $SQLScriptsFolderPath -ErrorAction Stop
#a manifest file will exisit in the SQL scripts folder detailing the order the scripts need to run.
$SQLScriptsManifestPath = $SQLScriptsFolderPath + "\Manifest.txt"
#Find out whether the manifest file exists in the the SQL Scripts folder
$SQLScriptsManifestPath = Resolve-Path $SQLScriptsManifestPath -ErrorAction Stop


#if manifest file found iterate through each line , validate if corresponding SQL script exists in file before running each of them
Get-Content $SQLScriptsManifestPath | ForEach-Object {
$SQLScriptsPath = $SQLScriptsFolderPath + "\" + $_.ToString()
Resolve-Path $SQLScriptsPath -ErrorAction Stop
}
$SQLScriptsLogPath = $SQLScriptsFolderPath + "\" + "SQLLog.txt"
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value "Started processing at [$([DateTime]::Now)]."
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value ""
Get-Content $SQLScriptsManifestPath | ForEach-Object {
$SQLScriptsPath = $SQLScriptsFolderPath + "\" + $_.ToString()
$text = "Running script " + $_.ToString();
Add-Content -Path $SQLScriptsLogPath -Value $text
sqlcmd -S "DEV-DB-01" -i $SQLScriptsPath | Out-File -Append -filepath $SQLScriptsLogPath
}
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value "End processing at [$([DateTime]::Now)]."
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value ""