Shrinking SharePoint_Config transactional log (when in emergency)

Some SharePoint critical errors thrown frequently and repeatedly might end up in an unexpected titanic growth of the SharePoint_Config transactional log. It can get seriously big, believe me... and the worst of all, it keeps growing and growing *fast*! 


This will lead us to an unstable working environment where nothing actually works as it should, slow, unresponsive and likely to be hit by collateral damage (i.e. Out of disk space just to name one). Well, my client fully suffer it the other day, the actual log hit the 1 TB mark! Woohoo!


So, no panic, before we get to diagnose and fix the cause of issue, let's quickly shrink the transactional log so we can get back to 'healthier' and quicker environment. 


Executing the following SQL script against the  SharePoint_Config database will compress the size of SharePoint_Config_log regardless of its size.

USE SharePoint_Config
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE SharePoint_Config
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (SharePoint_Config_log, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
-- Reset the database recovery model.
ALTER DATABASE SharePoint_Config
SET RECOVERY FULL;
GO


From this point, the size of the log should be significally smaller. Hope you can can solve the issue/s now. Good luck!

Return only documents in search results

A few days ago my client came up with this new requirement where the SharePoint search should only display nothing but documents. Everything else will go ignored.

To achieve that I would recommend to create a Search Scope and then apply a set of rules that will basically prevent the unwanted items to show up.

The rules are:

*://*allitems.aspx*  Exclude

*://*webfldr.aspx*   Exclude

*://*mod-view.aspx*  Exclude

*://*my-sub.aspx*    Exclude

*://*/lists/*       Exclude

*://*lists*       Exclude

And here is how it should look once you entered them

    

For us, PowerShell entusiasts, here's how it looks  :)

write-host "Enter the Search Service Application Url where you need to apply the crawling rules:"

[string]$SearchServicerUrl = Read-Host #i.e. Enterprise Search Service Application

New-SPEnterpriseSearchCrawlRule -SearchApplication $SearchServicerUrl -Path "*://*webfldr.aspx*" -CrawlAsHttp 1 -Type ExclusionRule

New-SPEnterpriseSearchCrawlRule -SearchApplication $SearchServicerUrl -Path "*://*mod-view.aspx* " -CrawlAsHttp 1 -Type ExclusionRule

New-SPEnterpriseSearchCrawlRule -SearchApplication $SearchServicerUrl -Path "*://*my-sub.aspx*" -CrawlAsHttp 1 -Type ExclusionRule

New-SPEnterpriseSearchCrawlRule -SearchApplication $SearchServicerUrl -Path "*://*allitems.aspx*" -CrawlAsHttp 1 -Type ExclusionRule

New-SPEnterpriseSearchCrawlRule -SearchApplication $SearchServicerUrl -Path "*://*/lists/*" -CrawlAsHttp 1 -Type ExclusionRule

New-SPEnterpriseSearchCrawlRule -SearchApplication $SearchServicerUrl -Path "*://*all forms.aspx*" -CrawlAsHttp 1 -Type ExclusionRule


Happy Powershelling!

SharePoint 2010: Changing Regional Settings and Locale Settings in Powershell

I just had a client scenario where I have the SharePoint implementation has up to 40 different site collections to maintain. Well, I'm London based and so is my client... what about the default regional settings? Eng-US right? Is my client happy with it? Not really: en-GB, please!.
All right, first of all, let's change the Regional Settings at the level of the Web Application. That's the easy one, as it can be leveraged from SP "Central Admin" site. So simply follow the steps below:

• Go to "Central Admin"
• Click "Application management" from the quick launch.
• Click "manage web applications" under "Web application" heading
• Select web application on which you want to change the regional settings.
• From the ribbon click "General setting" and select "General Settings" from the dropdown.
• Setting modal dialog will appear, you can change the "default time zone" and other settings if you want to change, and click OK.
• IISReset

And you are done! Wait! What about the locale settings? Aha, the mighty Powershell is happy [once more] to come to the rescue! The following script will do the dirty job for you. In a nutshell it iterates through each site collection within a given web application updating its locale settings and verifying right after if that has been changed successfully.


[Reflection.Assembly]::LoadWithPartialName("System.Globalization") | out-null
clear-host
$SiteCols = Get-SPWebApplication "<SiteCol URL>" | Get-SPSite -Limit ALL | Select URL
foreach ($siteCol in $SiteCols)
{
write-host "---- Site Collection: " + $siteCol.url + "----"

 
get-spweb -site $siteCol.url -limit all |% {
$_.Locale = [System.Globalization.CultureInfo]::GetCultureInfo("en-GB");
$_.Update()
}
#check if culture has been updated
get-spweb -site $siteCol.url -limit all |% {
$_.Locale
}
}


By the way, make sure you are running the script under a site collection administrator; otherwise you'll get a nasty Access Denied exception.


Get-SPWeb : Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
At line:4 char:12
+ get-spweb <<<< -site "<SiteCol URL>" -limit all |% {
+ CategoryInfo : InvalidData: (Microsoft.Share....SPCmdletGetWeb:SPCmdletGetWeb) [Get-SPWeb], UnauthorizedAccessException
+ FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletGetWeb


Make Powershell your buddy when it comes to pre-deploy | post-deploy tasks. It will save a lot time that's for granted, but also it will save you heaps of headaches when deploying SharePoint application between different environments.

Unable to add a lookup field using AddFieldAsXml –Solved!

I recently had a problem when trying to add a new lookup field programmatically. It turns out that Field ID was actually the culprit. You can't use a fixed field ID when using web.Fields.AddFieldAsXml(sColumnDefinitionXml) which is what the XML for a field is supposed to have when it is used as an element file in a feature, but not when you are adding the field using code.
Then:
Wrong XML definition for sColumnDefinitionXml


 
<Field Type=\"Lookup\" DisplayName=\"Chat Waiting Time\" Required=\"FALSE\" List=\"4c00bad4-e57d-417e-ad6e-d29fcad06c3f\" ShowField=\"AdvisorResultText\" Group=\"ChildLine Site Columns\" ID=\"{D4654C7B-7DDD-4c79-9CB1-7130B567C3BC}\" StaticName=\"ChatWaitingTimeLookUp\" Name=\"ChatWaitingTimeLookUp\" xmlns=\"http://schemas.microsoft.com/sharepoint/\" />"        

 
Right XML definition for sColumnDefinitionXml


 
<Field Type=\"Lookup\" DisplayName=\"Chat Waiting Time\" Required=\"FALSE\" List=\"4c00bad4-e57d-417e-ad6e-d29fcad06c3f\" ShowField=\"AdvisorResultText\" Group=\"ChildLine Site Columns\" StaticName=\"ChatWaitingTimeLookUp\" Name=\"ChatWaitingTimeLookUp\" xmlns=\"http://schemas.microsoft.com/sharepoint/\" />"        


Took it off and it worked like a charm. Now all lookup fields can be safely added by my feature receiver J.