SharePoint24x7 It's all about SharePoint.


Tips n Tricks – Reducing the SharePoint Config Log file size

Posted by Joy

Today we came across with a situation where we keep getting a runtime error when we try to perform our normal day-today operations in our SharePoint 2013 based solution. It started throwing a runtime error with a error code - HRESULT: 0x80131904 and it failed to save data into the SharePoint database.

The reason was the SharePoint Configuration database LOG file has grown to 91 GB and there was not enough free space in the physical disk. Because of this, SharePoint was unable to commit database changes and there was a huge performance impact. The solution we found was:

  1. Change the recovery model for the Database from Full to Simple
  2. Shrink the database

I executed the following SQL command and it brought the database log file from 91GB to 1 MB which solved my issues.

USE [master]


USE [SharePoint_Config]

DBCC SHRINKFILE ('SharePoint_Config_Log')


Article – Customizing SharePoint 2013 Search Results with Query Rules – User Intent

Posted by Joy

One of the best new features of SharePoint 2013 Search is the introduction of Query Rules. Query Rules in SharePoint 2013 replaces the Best Bet functionality in SharePoint 2010.

Query Rules allows us to create:

  • Result Blocks
  • Promoted Results
  • Change Ranked Results
  • Act on user “Intent”
  • Publishing

Act on user “Intent”

Act on user intent is all about searching in a natural way. It means mixing our natural vocabulary with search queries.

Most of us refer to PowerPoint presentations as “Deck” and Word documents as “Doc”. In SharePoint 2010 search, when we want to search for any Word or PowerPoint content, you need to first search by giving the search query and then refine using file type.

What if we want to combine our natural vocabulary with search keywords? For example, what if I issue search queries such as “SharePoint deck” and “SharePoint doc” trying to locate Word or PowerPoint content which contains SharePoint keyword? In SharePoint 2010 search, it will search for content which contains “SharePoint deck” and “SharePoint doc” within the content. It doesn’t understand that “deck” and “doc” are two words we use in our day-to-day vocabulary. But in SharePoint 2013, if you search by giving “SharePoint deck” and “SharePoint doc”, it will return Word and PowerPoint content which contains “SharePoint” keyword within the content. SharePoint 2013 understands that “deck” and “doc” are two words which we use commonly in our day-to-day work.

Following is a screen capture of a search result without using any user intent:

SharePoint 2013 Search result

Following is a screen capture of a search result with the use of new SharePoint 2013 Search user intent features:

SharePoint 2013 Search result with user intent

SharePoint 2013 Search result with user intent

“Deck” and “doc” are two built-in user intents provided by SharePoint 2013 Search. We also can introduce custom user intent with the help of new Query Rules concept.

Adding custom user intents

We often refer images as “pics”. It will be very convenient for end-users for being able to search for images by combining “pics” user intent rather than searching and then refining using file type search refinement.

Following is a screen capture of a search result which gives no results when you use “pics” user intent since “pics” is not a built-in search user intent rule and it’s trying to search for content which contains “sharePoint pics” within the content:

SharePoint 2013 Search result

Following steps demonstrate how we can get custom user intents integrated to SharePoint 2013 search.

Task – Creating a custom User Intent Query Rule
  • Navigate to your Search Center. In my case, it’s
  • Click Settings from top-right corner and select Site Settings to navigate to Site Settings page.
  • Select Search Query Rules from the Site Collection Administration section to navigate to Manage Query Rules pageSite Settings
  • Select Local SharePoint Results (System) for For what context do you want to configure rules? parameter and click New Query Rule to open Add Query Rule page.Manage Query Rules page
  • Give a Rule Name, and select Query Contains Action Term for Query Condition. Enter “pics” for the option Action term is one of these phrasesAdd Query Rule page
  • Select Add Result Block under Result Blocks in Actions section to open Add Result Block dialog boxAdd Query Rule page
  • Amend the Block Title and click Launch Query Builder to open Build Your Query window.Add Result Block
  • Select Pictures (System) for Select a query option and click Test query button and observe the SEARCH RESULT PREVIEWBuild Your QueryBuild Your Query
  • Click OK to go back to Add Result Block dialog. Change the Items value depending on how many items you need to show in the search result.Add Result Block
  • Click Settings link to expand Settings section. Select Picture Item for Item Display Template and click OK.Add Result Block
  • Click Save to exit from Add Query Rule page
Task 2 – Testing the custom User Intent Query Rule
  • Navigate to Search Center.
  • Search using the new User Intent. Following screen capture shows a result of a search query which is combined with “pics” user intent
    SharePoint 2013 Search result with user intent

Article – Customizing SharePoint 2013 Results using Result Sources

Posted by Joy

SharePoint 2013 gives us a very powerful Search platform by integrating the power of FAST Search platform. One of the very useful capabilities of SharePoint Search is the ability to customize to suite to your requirement.

SharePoint 2013 Search allows not only to customize the Content Processing Pipeline but also to customize how search results are queried and displayed in the search result.

In SharePoint 2010 one of the many ways we could customize search result was customizing Search Scopes. A search scope in SharePoint 2010 defines a subset of information in the search index. Users can select a search scope when performing a search to restrict search results to the subset of information that they want. Generally, search scopes consists of specific topics and different content sources that are important and common to users in the organization. Following is a screen capture of how SharePoint 2010 configures it’s default search scopes.

SharePoint 2010 search scopes

In SharePoint 2013 terminology, we don’t have Search Scopes concept anymore and we have Result Sources as a replacement for search scopes. Result source in SharePoint Server 2013 is used to specify a provider to get search results from, and optionally to narrow a search to a subset of those results. Result sources are more powerful than search scopes which we used in SharePoint 2010. Following are the default result sources we get in as part of the default configuration of SharePoint 2013.

SharePoint 2013 Result Sources

SharePoint 2013 default result sources provides:

  • Everything – Local SharePoint results
  • People – Local People results
  • Conversations – Local conversation results
  • Videos – Local Video results

In this article lets explore how we can use Result Sources to customize the search results presentation to provide more user-centric search experience.


When users navigate to out-of-the-box Search Center in SharePoint 2013 and execute a search result, it displays all the items which contains/ match the search keyword. Following is an example of a default search result which shows everything including Sites, Pages, Documents, tasks, Calendar items which match with the search criteria.

SharePoint 2013 built-in search result page

What we want to do is, to customize the default search result presentation to meet the following conditions:

  • Search result will display only the content authored by the user who is executing the search query.
  • Search result will display only the Word Documents and will eliminate Sites, Pages, Calendar items and Tasks even if there are matching items.


We will be customizing SharePoint Search Result Sources to provide the above custom search result presentation experience.

Task 1 – Creating a custom Result Source

  • Navigate to your Search Center. In my case I have a Search Center defined in the URL:
  • Select Settings and Site Settings to navigate to Site Settings pageSite Actions menu
  • Select Search Result Sources from the Site Collection Administration section to navigate to Manage Result Sources pageSite Collection Administration
  • Manage Result Sources page shows all the built-in Result Sources. Click New Result Source to add a custom Result Source.Manage Result Sources
  • Enter a Name for your custom Result Source. Leave Protocol as Local SharePoint. Leave Type and SharePoint Search Result. Click Launch Query Builder button to launch the Build Your Query window.Build Your Query
  • We will be using BASICS tab to build our custom search query. Set Property filter as shown below and click Add property filter button to add it to Query text area. Click Test query button to test the query and observe the SEARCH RESULT PREVIEW.Build Your Query
  • Set Property filter again with the following parameters as shown below and click Add property filter button to add it to Query text area. Click Test query button again to test the query and observe the SEARCH RESULT PREVIEW. Now you can see the effect of our custom search query and how it reduces the result displayed to end-user.Build Your Query
  • Click Save to navigate back to Manage Result Sources page


Task 2 – Associating the new Result Source with the Search Center

  • Navigate to your Search Center. In my case I have a Search Center defined in the URL:
  • Select Settings and click Add a page option. Enter a name for the page and click Create.Add a page
  • New page will be in edit mode and you can customize the web parts available in the new page.
  • Edit Search Results web part to associate our new Result Source.Edit Search Result web part
  • Select Change query button and select the newly created custom Result Source from the Select a query drop down and click OK.Edit Search Result web part

    Change Search query

  • Click OK from from the edit web part property page to exit edit web part property page.
  • Click Check it in and Continue to check in the new page
  • Click Publish it to publish the new page.


Task 3 – Adding the new page as a search vertical

  • Select Settings and Site Settings to navigate to Site Settings page
  • Select Search Settings from Search section
  • Locate Configure Search Navigation section in Search Settings page. Click Add Link … to add a new navigation link.Add a search navigation link
  • Enter a title for the Title: field. For the URL: field, browse for the the newly created page by using Browse… button. Click OK.Add a search navigation link
  • Click OK to exit Search Settings page


Task 4 – Test the new Result Source

  • Navigate to Search Center and perform a search. You will see the new result source being added to search result page.SharePoint 2013 search result with the new result source
  • Select the new result source and see how it applied the conditions we specified to show content authored by the user who is running the search query and to include only Word documents.New result source search result presentation

Article – Enabling Continuous Crawling in SharePoint 2013 Search

Posted by Joy

Crawling is the mechanism which SharePoint Search uses to go through all the items in a given content source and build an Index. All the search queries get executed against the Index to provide the search result. Outcome of the search always depends on the freshness of the Index. In other words, if the Index is very fresh/ recent, we have the ability of pulling fresh/ recent content as part of the search query. If the Index is very old/ outdated, search result will contain outdated search results.

SharePoint used to provide 02 mechanisms to crawl content from a content source – Full crawl and Incremental crawl.

  • Full crawl – this forces SharePoint to go through the entire content source, no matter what is the status of the Index and traverse through all the items from the first item through the last item and re-build the Index. This is what we do as the very first thing after we add a new content source. Time it takes to complete a full crawl is always depends on the volume of the content. If the volume is high, we don’t recommend running frequent full crawls.
  • Incremental crawl – this instructs SharePoint crawler to crawl only the modified content (added/ changed/ deleted) from the last full or incremental crawl. This takes less time to crawl since it doesn’t have to traverse through the entire content source and crawl the entire content source.

Most of the organizations, schedule incremental crawls depending on how frequent their content get updated. Incremental crawl starts at a particular time and repeats regularly at specified times.

SharePoint 2013 introduces an alternate option for incremental crawls – Continuous crawl.

  • Continuous crawl – this is a new option in SharePoint 2013 and it applies only to SharePoint Sites. Like incremental crawls, a continuous crawl crawls content that was added, changed, or deleted since the last crawl. Unlike incremental crawls, continuous crawl automatically starts at predefined time intervals. The default time interval is 15 minutes. This is the ideal option for crawling SharePoint content that is changing quickly/ frequently. With this, we can ensure freshness of Index and search result.
Configuring continuous crawl
  • Open SharePoint 2013 Central Administration web site
  • Select Manage service applications option from Application Management section
  • Select Search Service Application instance
  • Select Content Sources link under Search Administration links in left hand side
  • Select either New Content Source or your existing SharePoint content source
  • Select Enable Continuous Crawls for Crawl Schedules option and click OK.Crawl Schedules

Article – Configuring PerformancePoint Services (PPS) in SharePoint 2013

Posted by Joy

What is PerformancePoint Services (PPS)?

As per TechNet:

PerformancePoint Services in SharePoint Server 2013 is a performance management service that you can use to monitor and analyze your business. By providing flexible, easy-to-use tools for building dashboards, scorecards, and key performance indicators (KPIs), PerformancePoint Services can help individuals across an organization make informed business decisions that align with companywide objectives and strategy.

PerformancePoint Services lets you create rich dashboards, aggregating content from multiple sources and displaying it in a web browser in an understandable and collaborative environment. The rich interactivity of the dashboards lets you to analyze the latest information and work with the data quickly and easily to identify key opportunities and trends. PerformancePoint Services and its authoring tool Dashboard Designer let you display key performance indicators (KPIs) and data visualizations in the form of scorecards, analytic reports, and filters. You can integrate with SQL Server Reporting Services (SSRS)and Excel Services in addition to create scorecards that bring together data from multiple data sources to track and monitor key drivers of your business.

Configuring PerformancePoint Services is very easy if you have experience already configuring services such as Excel Services, Secure Store Services, PowerPivot, etc…

In this article I’m trying to help you with the various information which are required for configuring PerformancePoint Services service application.

Prerequisites for configuring PPS:

  • SharePoint Server 2013 Enterprise – before we get started with the configuration of PPS, we need to make sure that we have SharePoint Server 2013 Enterprise edition in order to use PPS. PPS is available only with the Enterprise edition of SharePoint Server.
  • ADOMD.NET – SharePoint 2013 Prerequisites Installer installs ADOMD.NET 9.0 automatically in to your environment. However, PPS requires ADOMD.NET 10 and ADOMD.NET 11 to be installed in your environment.
Task 1 – Verify what version of ADOMD.NET is installed in your environment
  • Navigate to C:\Program Files\Microsoft.NET\ADOMD.NET\
  • If you have ADOMD.NET 9, ADOMD.NET 10 and ADOMD.NET 11 installed, you will see something similar to the followingADOMD.NET install folder
  • If you don’t have either 100 or 110 folders, which means you don’t have either ADOMD.NET 10 or ADOMD.NET 11 installed in your environment. Depending on the version you need to install, you can use either of the following links to download and install ADOMD.NET:
Task 2 – Grant Application Pool Service Account access to SharePoint Content Database
  • Open SharePoint 2013 Management Shell. Make sure to Run as Administrator.
  • Grant application pool service account access to the content database using following cmdlets
    $webApp = Get-SPWebApplication


Task 3 – Create PerformancePoint Service Application instance
  • Open SharePoint 2013 Central Administration web site
  • Navigate to Manage service applications page from Application Management section
  • Select New from the Create group in the ribbon and select PerformancePoint Service ApplicationNew service application
  • Enter a Name, Database Server, Database Name and Application Pool details and click Create.Capture2
  • Click OK in the service application instance creation conformation page
Task 4 – Start PerformancePoint Services service
  • Open SharePoint 2013 Central Administration web site
  • Navigate to Manage services on server from System Settings section
  • Check the status of the Performance Point Service service. Start it if it’s not started already.

Article – SharePoint Search Capabilities Comparison

Posted by Joy

Search is one of the important workloads of SharePoint Products and Technologies. Microsoft’s Enterprise Search offerings have evolved very fast over the last few years and have gone through number of changes.

SharePoint 2010 release introduced the most powerful Search platform by combing the power of FAST ESP with SharePoint platform. However, customers were confused when selecting the right Search platform since there were so many Search offerings.

Following are the different Search offerings we had around SharePoint 2010:

  • SharePoint Foundation 2010
  • SharePoint Server 2010
  • SharePoint Server 2010 for Internet Sites
  • Search Server 2010 Express
  • Search Server 2010
  • FAST Search Server 2010 for SharePoint
  • FAST Search Server 2010 for SharePoint Internet Sites
  • FAST Search Server 2010 for Internet Sites
  • FAST Search Server 2010 Internal Applications

Wow, big list of Search offerings…

With the release of SharePoint 2013, Microsoft made it very simple and easy to pick the right Search offering. We now don’t have FAST product line and it’s only SharePoint Search. However, SharePoint 2013 has many forms including both on-premise and cloud and Search will vary based on the selection of the SharePoint model.

Following is a very high-level feature comparison of Search based on the SharePoint deployment model:

Capability Foundation Standard Enterprise Online
Advanced Content Processing Yes Yes Yes No
Content Processing Enrichment No No Yes No
Content Search Web Part No No Yes No
Continues crawl Yes Yes Yes No
Custom entity extraction No No Yes No
Deep links No Yes Yes Yes
Event-based relevancy No Yes Yes Yes
Expertise Search Yes Yes Yes Yes
Graphical refiners No Yes Yes Yes
Hybrid search Yes Yes Yes Yes
Managed navigation No Yes Yes Yes
Phonetic name matching Yes Yes Yes Yes
Query rules – add promoted results No Yes Yes Yes
Query rules – advanced actions No No Yes Yes
Query spelling correction Yes Yes Yes Yes
Query suggestions No Yes Yes Yes
Query throttling No Yes Yes Yes
Quick preview Yes Yes Yes Yes
Recommendations No Yes Yes Yes
Refiners Yes Yes Yes No
Query Object Model/ RESTful Query API Yes Yes Yes Yes
Result sources Yes Yes Yes Yes
Search connector framework No No Yes No
Search results sorting Yes Yes Yes Yes
Search vertical – Conversations No Yes Yes Yes
Search vertical – People No Yes Yes Yes
Search vertical – Video No No Yes Yes
Tunable relevancy No No Yes No

Article – Fixing PowerPivot Management Dashboard Errors

Posted by Joy

We installed SQL Server PowerPivot for SharePoint and we also executed PowerPivot for SharePoint 2013 Configuration tool and configured PowerPivot for SharePoint.

Now we need to make sure that PowerPivot Service Application instance is configured correctly for us to move forward. Unlike any other service application settings/ configuration pages, PowerPivot Service Application instance settings page is considered as the Dashboard for PowerPivot Service Application instance.

Let’s go and explore PowerPivot Service Application instance and it’s configuration.

  • Open SharePoint 2013 Central Administration web site. Click Manage service applications from Application Management section.
  • Click PowerPivot Service Application instance name to navigate to PowerPivot Management Dashboard.PowerPivot Service Application instance
  • Most of the time, you will end up getting following screen. You will see Excel Services error in the Infrastructure – Server health web part and another error in Workbook Activity – Chart web part. The reason for this error is, App Pool Service Account doesn’t have access to Central Administration content database.PowerPivot Management Dashboard
  • Open SQL Server Management Studio and connect to Database Engine.
  • Expand Databases folder and locate the SharePoint Central Administration content database. Expand the Security folder and then expand the Users folder.Granting membership in SQL Server
  • Select the App Pool Service Account, right click the user account and select Properties. Select Membership page from the Select a page list. Select SPDataAccess database role membership.Granting membership in SQL Server
  • Click OK and close SQL Server Management Studio.

Now you can go and verify PowerPivot Management Dashboard to see that both Red X are gone.


Article – Configuring PowerPivot for SharePoint 2013

Posted by Joy

In the previous articles, we discussed installing SQL Server PowerPivot for SharePoint 2013 and associating PowerPivot SQL Server Analysis Services server instance with Excel Services. Before we get started with PowerPivot, we need to perform initial configuration for PowerPivot.

This article guides you through the steps required for the initial configuration of PowerPivot.

Before we get started, it’s important to point out that there will be 2 configuration options for PowerPivot:

  • PowerPivot Configuration Tool
  • PowerPivot for SharePoint 2013 Configuration

PowerPivot Configuration Tools

You need to use PowerPivot for SharePoint 2013 Configuration to configure PowerPivot for SharePoint 2013.

  • Right-click PowerPivot for SharePoint 2013 Configuration, and select Run as administrator to launch PowerPivot for SharePoint 2013 Configuration wizard.PowerPivot for SharePoint Configuration Tool
  • Select Configure or Repair PowerPivot for SharePoint, and click OK.PowerPivot for SharePoint Configuration Tool
  • Wizard will run set of validation rules, and wait until it finishes the validation process.PowerPivot for SharePoint Configuration Tool
  • After the validation process, PowerPivot Configuration Tool will open up. This wizard has 2 panes. Left hand pane shows all the configuration tasks which needs to be addressed. Right hand side pane shows Parameters, Script and Output for the selected task in left hand pane. We need to make sure that all the tasks in the left hand side pane indicates a green flag to move forward.Configuration Tasks
  • Select the root task – Configure or Repair PowerPivot for SharePoint 2013, and provide parameter values for Default Account Username, Default Account Password and Database Server. Important: make sure to give the database server name for the Database Server parameter even if you install PowerPivot in a SharePoint Server.
    Setting the database server
  • Select the task Create PowerPivot Service Application from left hand side pane. This task make sure it automatically create the Service Application instance for PowerPivot. Provide meaningful parameter values to Service Application Name, Database Server and Database Name from the right hand pane.Creating PowerPivot Service Application
  • Select the task Deploy Web Application Solution from left hand side. Select the Web Application which you want to deploy the PowerPivot solution from the URL drop down. Through this wizard you can deploy PowerPivot solution only to a single Web Application. However, you can use SharePoint 2013 Central Administration web site if you want to deploy PowerPivot solution to any other Web Application.
    Setting the Web Application
  • Select the task Activate PowerPivot Feature in a Site Collection. Select the Site Collection in which you want to activate the PowerPivot Feature using Site URL drop down.
    Setting the Site Collection
  • Select the task Create Unattended Account for DataRefresh. This will create and configure a Target Application in Secure Store Service for PowerPivot data refresh. Enter meaningful parameter values for Target Application ID, Friendly Name for Target Application, Unattended Account User Name, Unattended Account Password and Site URL.
    Setting the Unattended Service Account
  • Click Validate to start validating the configuration details.
    Validating configuration settings
  • If there are no errors in the configuration parameters which we provided, validation success message will show up. Make sure all the configuration tasks in the left hand side pane are flagged with a green flag.
    Validating configuration settings
  • Click Run to start the configuration process.
  • Click Yes in the Warning.
    Running the configuration
  • It runs a set of 15 configuration steps and when it’s done it shows success message. Click OK and then Exit to quit the configuration wizard.Running the configuration
  • Open SharePoint 2013 Central Administration web site. Click Manage service applications link from Application Management section. Verify the creation of PowerPivot service application instance.
    PowerPivot Service Application instance

Let’s conclude this article now and look at the rest of the configuration tasks in upcoming articles.


Article – Associating PowerPivot SQL Server Analysis Services server instance with Excel Services

Posted by Joy

In my previous article, I demonstrated how to install SQL Server PowerPivot for SharePoint. After installing PowerPivot for SharePoint, you need to configure it before start using it. One of the configurations is associating PowerPivot SQL Server Analysis Services server instance with the Excel Services.

This article guides you through the process of associating PowerPivot SQL Server Analysis Services server instance with Excel Services.

  • Open SharePoint 2013 Central Administration web site. Select the option Manage service applications from the Application Management section.Application Management
  • Click Excel Services service application name to navigate to Manage Excel Services Application page.Manage service applications
  • Select Data Model Settings option from the Manage Excel Services Application page to navigate to Excel Services Application data Model Settings page.Manage Excel Services
  • Click Add Server from Excel Services Application Data Model Settings page to associate PowerPivot SQL Server Analysis Services instance with Excel Services.Excel Services Data Model Settings
  • Enter the name of the server in the Server Name field including PowerPivot SQL Server Analysis Services instance name and click OK.Add Server
  • Now you can see PowerPivot SQL Server Analysis Services server is associated with Excel Services.
    Add Server

Now you can move on to other configuration steps Smile