Problem
Very large SharePoint WSS_Search database
TroubleShooting
I started drilling down into the tables and found the MSSCrawlURLLog was over 79GB.!
The Crawl Log Cleanup for Search Application job runs daily and removes anything older than 90 days by default. This was not happening correctly. So I tried changing the clean up job to delete logs that were older than 30 days. See Powershell scripts below.
Run the following to get a list of the search service application Ids.
Get-SPServiceApplication | Where {$_.TypeName -eq "Search Service Application"}
2. Run the following command to get the Search Service Application Object. Replace the Id with the Id of your search application that was output in the previous PowerShell command.
$searchApp = Get-SPServiceApplication | Where {$_.Id -eq "<your content service application id>"}
3. Set CrawlLogCleanUpIntervalInDays to 30
$searchApp.CrawlLogCleanUpIntervalInDays = 30 $searchApp.Update()
This had little to no effect. The next step was to try an complete index reset and run a full crawl. Again this had no effect.
I decided that this info was NOT needed – it’s just logs and that a purge would be my best bet. After all I wantded that 80GB back Plus I was having to back that DB up every night.
$ssa = Get-SPEnterpriseSearchServiceApplication "Enterprise Search Service" $logViewer = New-Object Microsoft.Office.Server.Search.Administration.Logviewer $ssa $logViewer.PurgeCrawlLog()