Friday, April 26, 2013

SharePoint 2013/2016: SQL Partition Exceeded Max Bytes

We noticed on one of our new application servers that the event log had multiple errors stating:

Table SqlIoData_Partition18 has 442900480 bytes that has exceeded the max bytes 442857142

From the Task Category, notice that this is coming from the Usage Infrastructure. The root cause was that we were collecting too much usage data and most of it was probably unneccessary.

To throttle the usage collection, from Central Administration click on Monitoring from the quick launch:

On the Monitoring page, click on Configure usage and health data collection:

Notice the important info here..."..but also uses system resourcs and can impact performance and disk usage". Crap. I don't need all that. Simply uncheck items you don't care about and only have several items checked:

If you are like me you probably don't know what half these things are anyway. Here are the events that I kept checked:  (click on the image for a larger view)

The text on the page gives us a little helpful hint. Enable the events you need to investigate issues and then disable them after the investigation is complete.

No more max bytes errors in the event log anymore!

UPDATE 5/1/2013: I spoke too soon. Apparently leaving the SQL IO Usage produces a large amount of usage data. I had more max bytes errors this morning. This is with just a handful of us on the new production servers. I unchecked SQL IO Usage and SQL Latency Usage. Eventually I'll probably be down to just Page Requests being checked.

UPDATE 9/18/2013: After applying the SharePoint 2013 updates and enabling Usage for Page Requests, I still encountered this error. Seems like the root cause and potential workarounds are explained here:

Thursday, April 25, 2013

SharePoint 2013 Search: Removing the Junk from your Search Trunk

Have you ever noticed how much nonsense or irrelevant results come back from various search queries? It's nobody's fault. The content is out there and happens to match the terms we are searching. But do we really need all of this stuff if it's not useful?

I have been recently fine-tuning what comes back in search results. People want content. They don't want containers of content, lists of content, links to content - just plain results they can use. Therefore, I have tweaked my main results to perform the following:

  1. Remove excess external content type links and nonsense
  2. Remove Folders from search results
  3. Remove overall list results (AllItems.aspx)
  4. Remove Link List Items
  5. Remove Link Lists
  6. Remove Explorer Views (web folder results)

I only performed these tweaks on my main results page (a.k.a. Everything) by modifying the query on the Search Results web part:

External Content Type Nonsense
Even though I created custom Result Sources and Result Types for some of my external content data (aka BDC/BCS stuff), I noticed I would still get crazy bdc3:// results back. This was the first thing I removed:
I noticed folders being returned, which may be correct, but we want content - not the containers of content. I opened one of my folder results and it brought me to a list of --- more folders!! Ahh. We don't need that. Therefore I added another property filter as follows:


Full List Results (AllItems.aspx)
How many search results show the AllItems.aspx view?? In some queries it's every result. We just need the list item itself, not the full list of irrelevant items. Therefore I decided to filter that out too:

Links List Items
We happen to use many lists of links to display items on various department, office, or home pages. They are convienent and provide an easy way to add a new link to a page. However, for search results, we want links to the main content, not links to links to content. For this I needed to add a filter using the contentclass property:

Links Lists
Removing the link items was good but now the entire links list is being returned in results. Might as well remove that too:

Explorer View
Finally I noticed web folders correlating to Explorer Views of lists being returned. There is no need to gunk up the results with various views. Therefore I filtered these out as well using the secret STS_List_850 contentclass:

My final query text?

Here is what I have so far to get all of the junk out of my search results:

I am getting cleaner results with mostly documents, posts, relevant list items, calendar items, etc. I am starting to feel much better with the improvement on our relevancy.
Search on!!!



Monday, April 22, 2013

Stemming in SharePoint 2013 Search

Stemming essentially performs searches for words that share the same stem. Stemming in SharePoint 2013 is enabled by default and although it can be disabled, there is no UI option in the Search Results Web Part anymore. In addition, the EnableStemming property in the object model is also deprecated.

To disable stemming you need to:
  1. Export a Search Results Web Part
  2. Open the file in a text editor
  3. Set the EnableStemming property to false ("EnableStemming":false)
  4. Save the file
  5. Import the web part back onto the search results page
  6. Remove the existing web part from the search results page

While classic examples of stemming are explained as “run” and “ran” as well as “writes” and “wrote”, the results I am seeing are not so. The behavior I am experiencing in SharePoint 2013 is mostly plural and singular versions of words. For example “party” also presents results for “parties” (and vice-versa). “Search” is providing hits on “search” and “searches” but not “searcher”. I am not seeing "wrote" hits when I search for "write" nor do I see "ran" results when I search for "run".
Since there is no longer support for third-party stemming tools (and thus the previous registry entries are no longer used), the workaround to all of this is to include any additional stemming as synoynyms in a thesaurus file which may be loaded into SharePoint via PowerShell commands.

I have crawled 100GB of content so I should have a great sampling of words. Has anyone seen anything different with stemming?

Tuesday, April 9, 2013

SharePoint 2013: Dashboard Designer Not Showing Databases from Analysis Services - Resolution

Currently only 1 WFE
PPS Service running on 1 Application Server
Unattended Service Account entered via the PPS Service Application
Same service account belongs to a Role in SSAS (2012) with Read permissions on a cube
BI Center Site Collection created

Launch Dashboard Designer
Create New Data Source from Analysis Services
Enter Analysis Services Server
Database drop-down shows Loading... but then nothing appears in drop-down list.


  1. Install  Microsoft® SQL Server® 2012 ADOMD.NET and Microsoft® Analysis Services OLE DB Provider for Microsoft® SQL Server® 2012 from the Microsoft® SQL Server® 2012 Feature Pack on the WFE and the server running the PPS Service
  2. Install  Microsoft® SQL Server® 2008 R2 ADOMD.NET and Microsoft® Analysis Services OLE DB Provider for Microsoft® SQL Server® 2008 R2 from the Microsoft® SQL Server® 2008 R2 Feature Pack on the WFE and the server running the PPS Service
  3. IISRESET on the WFE and the server running PPS.
  4. Refresh the BI Center Site Collection
  5. Launch Dashboard Designer again from BI Center Site Collection
  6. Enter SSAS Server Name
  7. Database drop-down loads with the databases now!

I wouldn't be suprised if I need to install ADOMD.NET on any new WFEs that I build out but I will update this post when that time comes.

UPDATE 5/28/2013: I built out a new WFE and suprisingly I did not have to install ADOMD.NET. Everything appears to be working correctly.


Friday, April 5, 2013

SharePoint 2013: PDF Support and Behaviors - The Whole Story

Check out the whole SharePoint 2013 Solution Series
New Titles Added Weekly!


SharePoint 2013 supports PDF documents out-of-the-box. Initially, web applications do not allow opening PDFs in the browser, however, by adding PDF as an allowed MIME type, browser rendering via Adobe is achieved.

Office Web Apps server provides Office document previews and rendering in Search results without the need for client applications installed (e.g. Word, Excel, etc.). However, once SharePoint is bound to Office Web Apps, PDF documents no longer open in the browser.
There are two workarounds –

1)      Configure PDF items to render as Word Items which allows PDFs to open and preview in Search within Office Web Apps
2)      Modify the PDF Item display template which allows PDFs to render in the browser via Adobe. Modify the PDF hover template to display previews.
These workarounds take care of Search, but PDFs will still open in the client application (e.g. Adobe) from Document Libraries. The solution here is an update to Office Web Apps. The February/March 2013 Update to Office Web Apps server supports opening PDFs from document libraries within Office Web Apps.

The following table summarizes the various PDF rendering and preview behaviors:


Search PDF Preview

Search Open (clicking on result)

Document Library Open (clicking on Document)

Out of the Box (Strict Web App)


Available by modifying the Display Template

Opens in Adobe or associated client application

Opens in Adobe or associated client application

Out of the Box

(Permissive Web App or Allowed Mime Type of PDF)

Available by modifying the Display Template

Opens in web browser and search term is passed into Adobe

Web Browser

Office Web Apps Server

(October 2012 Release)

Two options:

1.       Display template (shows in Adobe web)

2.       Modify Result Type to use Word Item (shows in Word App Web)

Opens in Adobe or associated client application.

Opens in Browser with modification of display template

Opens in Adobe or associated client application.


Office Web Apps Server (Feb/Mar 2013 Update)

Two options:

1.       Display template (shows in Adobe web)

2.       Modify Result Type to use Word Item (shows in Word App Web)

Opens in browser using Word Web App


Can use templates to display in Adobe Web.

Opens in browser using Word Web App


If not bound to WordPDF – Opens in Adobe or associated client application.


It is also worth mentioning that if Office Web Apps is not used for Search results of PDFs, the opening of PDFs in the browser passes the search terms into Adobe and thus finds the occurrences within the document. An example of this “search term pass-through” is displayed below:

Based on my investigations and modifications, when using Office Web Apps server with SharePoint, there are two overall options when handling PDFs. One provides a more consistent user experience and the other provides the most functionality. These options are outline below:
Most Consistent User Experience
The most consistent user experience would be to use Office Web Apps server (with the update) to enable opening of PDFs from libraries in the browser and to modify the search result type to render PDFs as Word Items which enables both preview and opening of the documents from Search results within Office Web Apps.
Most Functionality
The option that provides the most functionality is to use Office Web Apps for document libraries such that PDFs are opened within the browser but then use customized search templates to preview and open PDFs from Search results thus providing the search term pass-through functionality as described above.  For the most consistent preview, use a customized copy of the Word item hover panel template (explained in the last section of this post).
The rest of this post steps through the details and explains how to accomplish the various options and behaviors.
OUT-OF-THE-BOX BEHAVIOR (without Office Web Apps)
Web Applications are created with the Browser File Handling option set to Strict. This means that only the default allowed MIME types (correlates to document types such Word, PDF, etc.) can open and display within the browser without prompting the user to Open or Save the document. PDF is not one of those default MIME types and thus, the user is prompted when attempting to open a PDF document:

The recommended way to enable PDFs to be opened in the browser is to add the MIME type to the allowed list of types by using PowerShell commands:

$webApplication = Get-SPWebApplication "http:/yourwebapplicationurl"

The other easy option, which is not recommended, is to modify your web application (via Central Admin) and change the Browser File Handling property to Permissive:

Either method will allow PDF files to be opened in the browser. A neat experience in search results is that the search term is passed into Adobe and the terms are highlighted in the document:


To get a PDF preview in the search results, you may follow my original idea and post.
OFFICE WEB APPS (October 2012 Release Version)

Once Office Web Apps is installed and configured, by surprise, PDF documents are no longer opening in the browser. Neither from document libraries nor from the search results. So there two options at this point (at least for the search results).

1. Follow Wictor Wilen's post to use the Word Item template for PDFs. This method both shows a preview and opens up PDF search results in the Office Web App's Word App Viewer.

2. Use my original method for creating a PDF Preview to generate the preview.
Follow these steps to allow opening of the PDF document in the browser via Adobe (maintaining the search term pass-through functionality:

Modify the Item_PDF.html in the display templates folder.
Replace this line:
            ctx.CurrentItem.csr_OpenControl = "PdfFile.OpenDocuments";

With this one:
     ctx.CurrentItem.csr_OpenApp = "word";

Problems solved, right? At this point the search is fixed but PDFs don't open from document libraries in the browser. That's where the Office Web Apps Update comes in to play!


There was a cumulative and public update released in early March 2013 that adds additional support for PDFs in SharePoint 2013 using Office Web Apps server. What does this do for us??

The update adds a new application type named WordPDF. It allows PDFs to be opened from document libraries in the browser using the Word App Viewer. What about search?

For search, there is no change. You either need to copy the PDF Result Type and configure it to use the Word Item or modify the search display templates. (Same options as above).

However, I have come up with a hybrid approach that provides a consistent preview using the Word App Viewer but also provides the rendering of PDFs in the browser through Adobe with the search term pass-through!

Open SharePoint Designer and navigate to the search display templates (similar to the steps here).
Find Item_PDF.html. Right-click and select Copy:

Right-click again and select Paste:

This process creates a copy of the file which appears at the bottom of the list. Find the copy and rename to something different (such as Item_PDFCustom.html):

 Right-click the new file and select Edit File in Advanced Mode:

Rename the title:

Change the hoverURL:

Replace this line:
ctx.CurrentItem.csr_OpenControl = "PdfFile.OpenDocuments";

With this one:
ctx.CurrentItem.csr_OpenApp = "word";

Save the file. This handles the opening of the PDF document in the browser. Now for the preview.

Locate Item_Word_HoverPanel.html. Right-click and select copy:

Right-click and select Paste:

Rename the copied file (should be the same name you used for the hoverUrl value):

Right-click the new file and select Edit File in Advanced Mode:

Change the title:

Save the changes! Onto the Search Center!

In your Search Center, select Site Settings from the Settings menu (gear).
Under Site Collection Administration, click on Search Result Types:


Scroll down and find the PDF entry. Select Copy from the drop-down menu:


Give the type a unique name and select the PDF Customized Item as the display template:

 Click Save.

Now, the search results display previews using Office Web Apps:

And the documents open in Adobe with the search term pass-through:

There are various options and behaviors when handling PDFs in SharePoint 2013. Using Office Web Apps server somewhat forces you down a customized search experience path. The easiest and most consistent option is to have Office Web Apps handle all PDF interactions via the Word App Viewer. Providing the search term pass-through is another option but requires display template modification as explained in this post. I hope this post provided insight into this topic.

Matched Content