Well, we all know SharePoint imposes limits on the list view threshold. If any list exceeds the limit, it can be a cause for performance degradation due to long running queries at the back-end [SQL].

Therefore, it’s fair to get the report, which gives us information about the lists, which are exceeding the limit of 5000 items.

The script given below provides the report.

  1. Clear-Host
  2. Write-Host -ForegroundColor Cyan "============================================================="
  3. Write-Host -ForegroundColor Magenta " SharePoint List Threshold Script "
  4. Write-Host -ForegroundColor Cyan "============================================================="
  5. Function PromptForScopeSelections
  6. {
  7. $promptTitle = "Scope selection"
  8. $promptMessage = "Please select the option to set scope for generating threshold report"
  10. $optionFarm = New-Object System.Management.Automation.Host.ChoiceDescription "&Farm", `
  11. "Generate list view threshold report for entire farm"
  12. $optionFarm.HelpMessage = "This option will generate list threshold report for entire farm"
  14. $optionWebApp = New-Object System.Management.Automation.Host.ChoiceDescription "&Web Application", `
  15. "Generate list view threshold report for single web application"
  16. $optionWebApp.HelpMessage = "This option will generate list threshold report for single web application"
  18. $optionSiteCollection = New-Object System.Management.Automation.Host.ChoiceDescription "&Site Collection", `
  19. "Generate list view threshold report for single site collection"
  20. $optionSiteCollection.HelpMessage = "This option will generate list threshold report for single site collection"
  22. $promptOptions = [System.Management.Automation.Host.ChoiceDescription[]]($optionFarm, $optionWebApp, $optionSiteCollection)
  24. $promptResult = $host.ui.PromptForChoice($promptTitle, $promptMessage, $promptOptions, 0)
  25. Return $promptResult
  26. }
  28. #Load the SharePoint snap-in in PowerShell if it is not loaded already
  29. Write-Host -ForegroundColor Yellow "Please wait while SharePoint snap-in is being loaded..."
  30. Add-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue
  31. if((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -ne $null)
  32. {
  33. Write-Host -ForegroundColor Green "SharePoint snap-in has been loaded successfully..."
  35. #Call the function to prompt for scope selection.
  36. $UserOption = 3
  37. $UserOptionAccepted = $false
  38. While($UserOptionAccepted -ne $true)
  39. {
  40. $UserOption = PromptForScopeSelections
  41. if($UserOption -ge 0 -and $UserOption -le 3)
  42. {
  43. Write-Host -ForegroundColor Green "Input has been accepted."
  44. switch ($UserOption)
  45. {
  46. 0 {Write-Host -ForegroundColor DarkCyan "You selected farm."}
  47. 1 {Write-Host -ForegroundColor DarkCyan "You selected web application."}
  48. 2 {Write-Host -ForegroundColor DarkCyan "You selected site collection."}
  49. }
  50. $UserOptionAccepted = $true
  51. }
  52. elseif($UserOption -lt 0 -or $UserOption -ge 3 -or $UserOption -eq "" -or $UserOption -eq $null)
  53. {
  54. Write-Host -ForegroundColor Red "Invalid input..."
  55. }
  56. }
  57. if($UserOptionAccepted -eq $true)
  58. {
  59. $ScriptPath = Split-Path $MyInvocation.MyCommand.Path
  60. $ScriptDate = Get-Date -Format "dd-MMM-yyyy"
  61. $ScriptTime = Get-Date -Format "hh-mm-ss"
  62. if($UserOption -eq 0)
  63. {
  64. $ThresholdReportPath = $ScriptPath + "\" + "SharePointFarm_LVT_" + $ScriptDate + "_" + $ScriptTime + ".csv"
  65. }
  66. elseif($UserOption -eq 1)
  67. {
  68. $WebAppAccepted = $false
  69. while($WebAppAccepted -ne $true)
  70. {
  71. $WebAppURL = Read-Host -Prompt "Please Enter Web Application URL"
  72. if(($WebApplication = Get-SPWebApplication $WebAppURL -ErrorAction SilentlyContinue) -ne $null)
  73. {
  74. Write-Host -ForegroundColor Green "Web Application URL has been accepted..."
  75. $WebAppTitle = $WebApplication.Name.ToString().Replace(" ","")
  76. $WebAppAccepted = $true
  77. }
  78. else
  79. {
  80. Write-Host -ForegroundColor Red "Invalid input, please provide valid Web Application URL"
  81. }
  82. }
  83. $ThresholdReportPath = $ScriptPath + "\" + "WebApplication_" + $WebAppTitle + "_LVT_" + $ScriptDate + "_" + $ScriptTime + ".csv"
  84. }
  85. elseif($UserOption -eq 2)
  86. {
  87. $SiteAccepted = $false
  88. while($SiteAccepted -ne $true)
  89. {
  90. $SiteCollURL = Read-Host -Prompt "Please Enter Site Collection URL"
  91. if(($SiteCollection = Get-SPSite $SiteCollURL -ErrorAction SilentlyContinue) -ne $null)
  92. {
  93. Write-Host -ForegroundColor Green "Site Collection URL has been accepted..."
  94. $SiteTitle = $SiteCollection.RootWeb.Name.ToString().Replace(" ","")
  95. $SiteAccepted = $true
  96. }
  97. else
  98. {
  99. Write-Host -ForegroundColor Red "Invalid input, please provide valid Site Collection URL"
  100. }
  101. }
  102. $ThresholdReportPath = $ScriptPath + "\" + "SiteCollection_" + $SiteTitle + "_LVT_" + $ScriptDate + "_" + $ScriptTime + ".csv"
  103. }
  104. #Create Output File Headers
  105. "Site URL`tList Title`tItem Count" | Out-File $ThresholdReportPath
  106. $ListCounter = 0
  107. if($UserOption -eq 0)
  108. {
  109. $WebApps = Get-SPWebApplication
  110. ForEach($WebApp in $WebApps)
  111. {
  112. $Sites = Get-SPSite -Limit All -WebApplication $WebApp.URL
  113. ForEach($Site in $Sites)
  114. {
  115. ForEach($Web in $Site.AllWebs)
  116. {
  117. ForEach($List in $Web.Lists)
  118. {
  119. $Items = 0
  120. $Items = $List.ItemCount
  121. if($Items -ge 5000)
  122. {
  123. $ListCounter++
  124. Write-Host -BackgroundColor DarkCyan "No. of lists having more than 5K items: $ListCounter `r" -NoNewline
  125. $Web.URL + "`t" + $List.Title + "`t" + $Items | Out-File $ThresholdReportPath -Append
  126. }
  127. }
  128. }
  129. }
  130. }
  131. }
  132. elseif($UserOption -eq 1)
  133. {
  134. $Sites = Get-SPSite -Limit All -WebApplication $WebApplication.URL
  135. ForEach($Site in $Sites)
  136. {
  137. ForEach($Web in $Site.AllWebs)
  138. {
  139. ForEach($List in $Web.Lists)
  140. {
  141. $Items = $List.ItemCount
  142. if($Items -ge 5000)
  143. {
  144. $ListCounter++
  145. Write-Host -BackgroundColor DarkCyan "No. of lists having more than 5K items: $ListCounter `r" -NoNewline
  146. $Web.URL + "`t" + $List.Title + "`t" + $Items.TOString() | Out-File $ThresholdReportPath -Append
  147. }
  148. }
  149. }
  150. }
  151. }
  152. elseif($UserOption -eq 2)
  153. {
  154. ForEach($Web in $SiteCollection.AllWebs)
  155. {
  156. ForEach($List in $Web.Lists)
  157. {
  158. $Items = $List.ItemCount
  159. if($Items -ge 5000)
  160. {
  161. $ListCounter++
  162. Write-Host -BackgroundColor DarkCyan "No. of lists having more than 5K items: $ListCounter `r" -NoNewline
  163. $Web.URL + "`t" + $List.Title + "`t" + $Items | Out-File $ThresholdReportPath -Append
  164. }
  165. }
  166. }
  167. }
  168. Write-Host " "
  169. Write-Host -ForegroundColor Cyan "Script Execution Completed..."
  170. Write-Host -ForegroundColor DarkMagenta "Output File Path is: " -NoNewline
  171. Write-Host -ForegroundColor Gray $ThresholdReportPath
  172. }
  173. else
  174. {
  175. Write-Host -ForegroundColor Red "Scope selection was not valid, terminating the exectuion..."
  176. Return
  177. }
  178. }
  179. else
  180. {
  181. Write-Host -ForegroundColor Red "SharePoint snap-in could not be loaded, terminating the execution..."
  182. }

Inputs

  1. Scope Selection: It provides you three options, as mentioned below. If you want a report for the entire Farm, press “F”. Similarly “W” or “S” for Web Application and Site Collection respectively.

    • Farm
    • Web Application
    • Site Collection

  2. If you select Web Application, then the next input you should provide is Web Application URL.
  3. If you select Site Collection, then the next input you should provide is Site Collection URL.

Once the required inputs are provided, it will go through each list in the sites and check, if it exceeds the limit of 5000 items. If yes, it will add that list to the report.

After the execution is completed, it will provide you the location, where the report is generated.

You can have a look at the report and identify the large lists. Later, remediate the lists, as per Microsoft’s recommended methods to ensure the list. The views don’t retrieve more than 5000 items at a time.

Hence, you can contribute to smooth performance for SharePoint Farm.

I hope this helps. Thanks for reading and using the script.