Compare files with SharePoint library after file server to SharePoint migration

#This script compares the folder contents with SharePoint site. This can be used to find out the files that are not migrated to SharePoint from File server
# Input 1 variable $siteURL : Provide the SharePoint Site URL; Format: "https://sharepoint.domain.com/HEM"
# Input 2 variable $filePath : Shared Drive path where the contents need to be compared with; Format: K:\HES\MAIN RECORDS
# Input 3 variable $docLibURL : SharePoint Document library URL where the contents to be compared with; Format: https://sharepoint.domain.com/HEM/Docs
# Input 4 variable $siteName: Used to suffix the export file name

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA silentlycontinue
$siteURL = "https://sharepoint.domain.com/HEM"
$filePath = "K:\HES\MAIN RECORDS"
$docLibURL = "https://sharepoint.domain.com/HEM/DOCS"
$siteName = "HEM_DOCS"

#Get all files from shared drive
$sFiles = get-childitem $filePath -Recurse | where {!$_.PSIsContainer} | select-object Name, FullName, LastWriteTime, Length

#Get SharePoint site object
$myweb =  Get-SPSite $siteURL

#Create a CSV file to export compare results
Add-Content -Path .\CompareResults_$siteName.csv  -Value 'FileName,Source,Destination'

Write-Host Comparing files, please wait...

$TotalFiles = $sFiles.Count;
$counter = 0 

#Get all files under the path specified
ForEach ($file in  $sFiles)
{

    #Prepare the file path for SharePoint based on the source file
    $spFilePath = $fileFullName.replace($filePath, $docLibURL ).replace('\','/')


    #Get file from SharePoint
    $f = $myweb.RootWeb.GetFile($spFilePath);

    #Check if file exists
    If (!($f.Exists)){
        $counter++
            #Export the file information to excel
            $Data = [PSCustomObject]@{
            'FileName' = $file.Name
            'Source'   = $file.FullName
            'Destination' = $spFilePath}
            $Data | Export-Csv -Path .\CompareResults_$siteName.csv -NoTypeInformation -append
        }
}

Write-Host Total files are $TotalFiles and missing Files $counter
   

Add-DatabaseToAvailabilityGroup : Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally.

Environment: SharePoint 2016 OnPremise Farm

Issue: Error when adding content database to SQL Always on Availability group using the following PowerShell command

Add-DatabaseToAvailabilityGroup -AGName AGSP1 -DatabaseName SPContentDB -FileShare \\UTI01\AlwaysOnShare

Error: Add-DatabaseToAvailabilityGroup : Exclusive access could not be obtained because the database is in use.
RESTORE DATABASE is terminating abnormally.


Fix: Make sure the content database in the secondary database has been removed before executing the above PowerShell command. 

Remove user permission from all documents using powershell

Environment: SharePoint Server 2016 On Premise

Requirement: To remove specific user permission from all folder, documents from a document library.

Code:

$myweb =  Get-SPWeb "https://sharepoint.domain.com/SM"
$mylist = $myweb.Lists | Where-Object { $_.Title -eq 'Documents' }
#foreach ($item in $mylist.Items ){

$account = $myweb.EnsureUser("c:0+.w|s-1-5-21-790525478-117609710-839522115-1145")
$Folder = $mylist.RootFolder

 foreach ($SubFolder in $mylist.Folders)
        {
                             
            if ($SubFolder.HasUniqueRoleAssignments)
            {
                if($SubFolder.Name -ne "Forms")
                { 
                    write-host "Removing account" $user "from FOLDER " $SubFolder.Name
                    $SubFolder.RoleAssignments.RemoveById($account.ID)

                            $query = New-Object -Type 'Microsoft.SharePoint.SPQuery'
                        $query.Folder = $SubFolder.Folder
                        $folderItems = $mylist.GetItems($query)
                        foreach($item in $folderItems)
                        {
                            if ($item.HasUniqueRoleAssignments)
                            {
                               write-host "Removing account" $user "from FILE" $item.Url
                              $item.RoleAssignments.RemoveById($account.ID)
                          }
                        }
                }
               
            }
        }

"#######################################################"
#}

File not found when sharing document in SharePoint

Environment: SharePoint Server 2016 On Premise Farm

Issue: Users are getting file not found error when they try to share a document in a document library. The Share URL is similar to below

• This issue is only for some of the documents in the library.

• Multiple site collections having this issue

• Recently we changed the web application site URL

• ULS log with error as "Website with name http://oldhostname.domain.com/SC1 is not existing"

x

https://newhostname.domain.com/http://oldhostname.domain.com/SC1/_layouts/15/DocIdRedir.aspx?ID=VQ47HSUWTZ35-2043059275-2304

Error message: something when wrong file not found

Troubleshoot: 

Fix:

Since the host name was recently renamed, the document properties were still referencing to the old URL, (not sure why). Following PowerShell code is to update affected documents with new host name.

$spSite = Get-SPSite -Identity  https://newhostname.domain.com/LIT

$website = $spSite.OpenWeb()

$list = $website.Lists["Documents"]

$items = $list.GetItems()

foreach($item in $items)

{

    if($item["_dlc_DocIdUrl"].Contains('oldhostname.domain.com') )

    {

        $item.File.CheckOut()

        $item["_dlc_DocIdUrl"] = $item["_dlc_DocIdUrl"].Replace('http://oldhostname.domain.com','https://newhostname.domain.com')

        $item.update()

        $item.File.CheckIn("DocIdUrl updated")

        write-host $item.Name  " updated"

    }

}

SharePoint site slow

Using SharePoint 2016 on premise;

Issue: SharePoint sites does not load and stuck on loading.

Error in event viewer of DB server:
SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again.

Troubleshooting:

• Noticed that the CPU is stuck at 99% in SharePoint database server. 
• Increased  the CPU in database server since the utilization was more.
• The CPU utilization still fluctuates even after we increased the CPU. 
• The CPU utilization decreases when we tried killing the SQL Services.

Root Cause:

• On further analysis, noticed that the database server CPU usage was consumed by search service and crawling activity of a Portal site (1 TB).
• The incremental search service that was scheduled on weekend did not complete because of the huge database size of  the portal. 

Fix:

• Stopped the incremental crawl. Stopping the search reduced the CPU consumption is normal and the sites are back
• The search performance level was set to maximum by default. To avoid maximum CPU consumption by Search service, the performance level value has been set to Reduced.

Get all web applications and its databases using PowerShell script

Environment: SharePoint 2010/2013

Requirement: To get the list of all WebApplication, Site Collection and its Content databases details

Solution:

Using PowerShell Script

$Today = [string]::Format( "{0:dd-MM-yyyy}", [datetime]::Now.Date ) 
$TranscriptFileName = "C:\\iLink\\AllWeb_ContentDB_$Today.txt" 
Start-Transcript -path $TranscriptFileName
 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") > $null 
$OFS = "`r`n" 
$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local 
$websvcs = $farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.Administration.SPWebService]} 
$webapps = @() 

 
 
foreach ($websvc in $websvcs) { 
        #loop through each webapplication 
  foreach ($webapp in $websvc.WebApplications) { 
  #loop through each site collection 
$sites=get-spsite -limit all -WebApplication $webapp.URL 

    foreach ($site in $webapp.Sites) { 
          $ContentDB = Get-SPContentDatabase -site $site.url 
$webappurl = $webapp.URL; 
  Write-Host "Web Application URL:" $webappurl ""$OFS 
$webappname=$webapp.DisplayName 
 
               Write-Host "Web Application Name" -value $webappname  ""$OFS 
               Write-Host "Site URL" -value $site.url     ""$OFS 
               Write-Host "Database Name" -value $ContentDB.Name   ""$OFS 
               Write-Host "Database Size" -value $($ContentDB.DiskSizeRequired /1024/1024)  ""$OFS 
               Write-Host "Date Created" -value $site.RootWeb.Created    ""$OFS 
               Write-Host "OWner" -value $site.Owner        ""$OFS  ""$OFS   ""$OFS 
       } 
      write-host ""$OFS 
     } 
      write-host ""$OFS 
} 
write-host "Request Completed"  ""$OFS
 
Stop-Transcript

Could not start the SharePoint Server Search 16 service

Environment: SharePoint 2016 Enterprise Farm

Issue: Could not start the SharePoint Server Search 16 service in new SharePoint 2016 farm environment. The logon credentials was a search service account (spsearchserv)

Error: Windows could not start the SharePoint Server Search 16 service on Local Computer. Error 5: Access is denied.

Resolution: The issue is because the service was not having enough permission to the local computer. Adding the service account (spsearchserv) to the local Administrators group resolved the issue.