Archive for SQL

Shrink CrawlStore Database

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 for SharePoint 2013
Add-Type -AssemblyName "Microsoft.SharePoint, Version=, 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)  
		$db.Shrink(20, [Microsoft.SqlServer.Management.Smo.ShrinkMethod]'TruncateOnly')
		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!

Configuring SQL Server Whitepaper

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 🙂

Creating Entities from Dynamic SQL

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:


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! 🙂


Get every new post delivered to your Inbox

Join other followers: