A useful script for running on your dev\uat environments to free valuable disk space. The crawl store database can grow quite significantly over time.
First of all, the PowerShell script:
# Load the SharePoint assembly - change the version to 22.214.171.124 for SharePoint 2013
Add-Type -AssemblyName "Microsoft.SharePoint, Version=126.96.36.199, Culture=neutral, PublicKeyToken=71e9bce111e9429c"
# Reset the content index
# Get the database server for the default content service
$dbServer = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.DefaultDatabaseInstance.NormalizedDataSource
# Get the SQL Management Object server for the content service retrieved above
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $dbServer
# Get the search service application instance
$searchServiceApp = (Get-SPEnterpriseSearchServiceApplication)
# Iterate the crawl stores and for each database, shrink it in size by 20%
foreach($crawlStore in $searchServiceApp.CrawlStores)
$dbName = $crawlStore.Name
$db = $SMOserver.Databases[$dbName]
if ($db -ne $null)
Write-Host "$dbName has been shrunk"
Write-Host "$dbName does not exist"
Comments in the script should make this one clear to understand, happy coding!
I recently came across the following whitepaper for configuring SQL Server to get the best performance from it when used in a SharePoint environment.
Download it from here
Or view it from here
Lots of good information in it and well worth keeping to refer to when building SharePoint environments 🙂
When working with a LINQ to SQL model (dbml) and you try to add a custom stored procedure that uses dynamic SQL, you will likely see an error similar to the following:
The return type for the following stored procedure could not be detected. Set the return type for each stored procedure in the Properties window.
Visual Studio will detect dynamic SQL as the return type of a stored procedure when you return columns from temporary tables, for example.
How to resolve? Relatively easy actually. At the start of your stored procedure, add the following line:
SET FMTONLY OFF
Update the procedure to include the above command and back in Visual Studio, refresh your database connection before trying to add the procedure to the model. This time, Visual Studio is able to work out the return columns and their type. Once you’ve added it to the model, remove the command from the stored procedure in SQL.
What does setting FMTONLY to off do? Well, while this set to off, running the procedure only returns the metadata, no rows are returned. By doing this Visual Studio is able to determine the columns. Simple! 🙂
Before attempting to move the databases associated with your SharePoint instance, make sure you backup your farm before continuing. For example, the following PowerShell script performs a full backup of the farm:
Backup-SPFarm -Directory c:\backuppath -BackupMethod Full
Depending on the size of the databases, this may take some time to complete.
One thing to check before starting the move is that the version of SQL is identical for both the source and target servers.
Once your happy that SharePoint has been backed up, the first thing to do is stop all SharePoint and Internet Information Services (IIS) services on the server that is running the Central Administration Web site. Do this by opening the Services snap-in from the Microsoft Management Console.