Merging SharePoint ULS Log Files from multiple servers

Troubleshooting a SharePoint issue and tracking a correlation ID becomes all of the more cumbersome when the underlying SharePoint topology is spread across multiple web and application servers.

Running the Powershell command ‘Merge-SPLogFile will indeed merge and wrap the latest ULS logs into a single file, although this can become very large depending on your Farm activity and ultimately, may difficult to open and work with.

Ideally, you should restart the SharePoint Tracing Service, which in turn generates a new log file, and then replicate the issue once more to regenerate a correlation ID. Once merged, these new, smaller log files will then easy to analyse.

The simple Powershell script below should be run from a SharePoint server with your Farm and can be used to restart the Tracing service and consolidate the logs. There is a 2 minute ‘sleep’ cycle following the restart of the Tracing service, and this is when you should replicate the problem. If replicating is convoluted and may exceed 2 minutes, edit the script accordingly.

 

Write-Host “Restarting SharePoint Tracing Service across all Servers”

$targetcomputers = (“SPserver01″, ” SPserver02″, ” SPserver03″, ” SPserver04″)

$targetcomputers | % { Restart-Service -InputObject $(Get-Service -Computer $_ -Name SPTraceV4) }

Write-Host “Replicate issue to generate correlation ID”

Start-Sleep -s 120

Write-Host “Merge Log Files and generate output”

Merge-SPLogFile -Path “e:\merged.log” -overwrite

Remove Web App IIS Application Pool

The Powershell command ‘Get-SPServiceApplication’ will only list IIS Applications related to SharePoint Service Applications across your Farm:

However, there may be a time when you want list the Web Application IIS Pools, and more importantly, delete one (once you’ve transferred any associated services).

There is no way to do this with Central Administration, so apply the following in Powershell:

 

• $contentapppools = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.ApplicationPools
  
• $contentapppools
  

 

Now, find the GUID of the Application Pool and ‘remove’

• $contentapppools.Remove(“GUID”)

Export-SPWeb fails – ‘lack of disk space’

The ‘Export-SPWeb’ cmdlet provides a simple way of moving SharePoint content between sites. This command essentially exports all of your content into a series of .cmp files which can subsequently be re-imported to suit your requirements. If you want to dig deeper, you can even rename the files to .cab to expose the key Manifest.xml.

Last week I was exporting an entire site collection (don’t even ask why!) and the content database was around 85GB in size. The drive I was exporting too was 135GB but during the operation, it failed with Powershell reporting that there was ‘not enough space on disk’.

Having done some digging, I realised that the system drive was out of disk space due to that fact that during the export, all of the content appears to be copied to the ‘%USERPROFILE%\AppData\Local\Temp’ directory – which happened to be my system drive with only 40GB of space.

The fix is to remap the %Temp% variable to a drive with suitable space (restart is required)

And now I was able to successfully export the SharePoint content

 

Gantt Chart Web Part error

I recently came across an issue where I was unable to open ‘Site Actions‘ on one particular SharePoint 2010 page – I was halted by the following error:

‘An error has occurred with the data fetch. Please refresh the page and retry’

The page in question contained a number of Web Parts, and through a method of elimination, I soon discovered that it was the ‘Gantt Chart’ Web Part view which was causing what was seemingly a Javascript error message.

A quick, simple fix was to remove the Gantt Chart Web Part from the page, then re-add and the problem was solved. But what has caused this issue?

The SharePoint 2010 Farm I was working against was fully patched to February 2012 CU, but had recently been updated with the April 2013 SharePoint Foundation security update:

http://support.microsoft.com/kb/2810059

I raised this with MS who were unaware of this issue, but successfully replicated the problem to one of their test environments. (which is always a relief as you’re otherwise left pondering ‘why me’?)

Patching the SharePoint Farm to the full April 2013 CU patch level permanently resolves the problem.

The perils of the SharePoint Cumulative Updates

There has always been trade-off when it comes to applying Cumulative Updates to your SharePoint Farm. You want to keep your Farm stable, secure and up-to-date, but there’s always the concern that an over-aggressive CU may cause some functionality issues for your end user. The recommended stance when it comes to CU’s is to only apply them to your Farm if there’s a compelling reason to do so – it fixes something!

Generally, SharePoint Service Packs are released by Microsoft having undergone a far more vigorous testing cycle in comparison to CU’s, and if there’s anyone out there still running SharePoint 2010 RTM and not SP1 I’d ask the question ‘why’?

Patching SharePoint is pretty simple; download the patch, apply it to every SharePoint Server in your Farm (excluding SQL or FAST), and then run PSConfig (starting with the Server(s) which are running Central Administration).

Despite this simplicity, I recently came across a SharePoint Farm where the build version wasn’t what I expected. The Farm had been manually patched to February 2012 CU but the build version was displaying April 2013 CU – the culprit, a SharePoint 2010 Foundation patch had been deployed, mistaken as a Windows Security patch. (http://support.microsoft.com/kb/2810059). PSConfig had also been run as the databases had been showing the ‘Upgrade is required’ warning, and this has caused the Farm build version to iterate.

Unfortunately there is no roll-back option with SharePoint patches and therefore the only valid approach was to install what was essentially a year’s worth of SharePoint Server patches to bring the Farm comprehensively up to April 2013 CU (http://support.microsoft.com/kb/2775353). And this is where the fun (actually pain) begun.

When I attempted to run the April 2013 CU SharePoint Server patch, I was stopped in my tracks by the following message:

SharePoint believed that the patches were already installed, so the way to circumvent this was to change the following registry value (on all the servers) to reflect the former build version (in this instance, Feb 2012 CU)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0

14.0.06117 (Feb CU 2012)

 

Then, if I pass a switch below to the patch updater executable, we bypass the version check which is causing the update to fail:

ubersrv2010-kb2767793-fullfile-x64-glb.exe PACKAGE.BYPASS.DETECTION.CHECK=1

 

 

 

RSS Viewer Web Part failing after installing KB2810059

I was recently troubleshooting against a SharePoint 2010 Farm where users were suddenly reporting that the RSS Web Part was not loading when it was active on a page which also had the Content Editor Web Part deployed. So, what’s changed?

I soon discovered that although the Farm had only been patched from a SharePoint Server perspective to February 2012 CU, the build number was reflecting 14.0.6137.5002 – which is April 2013 CU. Further investigation led to me discovering that a SharePoint Foundation patch (ttp://support.microsoft.com/kb/2810059) had been deployed by mistake as it was perceived to be a Windows Security patch. Subsequently, any SharePoint site which had Publishing turned on was failing to load the RSS Viewer when it shared a page with the CEWP.

Having consulted Microsoft about this issue, I was pleased to hear that this has been recognised as a bug…….although it won’t be fixed until either June 2013 CU, or Service Pack 2 – whichever is released first.

The suggested workaround was to either ‘Disable Web Page Security Validation’ at the Web App level (OK, it works, but it’s not recommended due to the security concern)

Or, apply the following Javascript:

)      Page Level: Adding a Content Web Part to the page that is having the problem, and using a custom script with it.

a)     Save the following JavaScript to a .txt file and upload the file to any document library in the root of the site. 

<Script Type=”text/javascript”> 

function CustomUpdateFormDigest() 

{ 

if(window._spPageContextInfo != null) 

{ 

var $v_2 = window._spPageContextInfo; 

var $v_3 = $v_2.webServerRelativeUrl; 

var $v_4 = window._spFormDigestRefreshInterval; 

UpdateFormDigest($v_3, $v_4); 

} 

} 

CustomUpdateFormDigest(); 

</Script>

b)    Add a “Content Editor” web part from “Media and Content” web parts category to the page not rendering web parts.

c)     Edit the “Content Editor” web part and link its content to the .txt file path we created and uploaded in Step 1 using the “Content Link” field.

d)    Hide the web part by checking “Hidden” under “Layout”.

e)     Save and publish the page.

 

 

 

Excel Services & SharePoint 2010 = Confusion!

Over the past few weeks I have received some calls regarding issues with Excel Services and namely, rendering from the browser. Let’s not forget that Excel Services in SharePoint is a whole lot more than the ability to be able to publish and open a workbook without requiring a native client. Business Intelligence scenario’s which allow complex workbooks to connect with external resources and update dynamically, are now becoming fundamental in the corporate workplace and Microsoft have invested a lot of time and effort ensuring Excel Services is still a major reason for investing in SharePoint. (and there’s more offerings to come in SharePoint 2013)

The Excel Services SharePoint 2010 Service Application in its most basic, simplistic and perhaps convention role, allows users to open a workbook in a browser – no great shakes there. However, it doesn’t work OOTB, and therefore in this blog I will show you what to look for if you receive errors when you choose the ‘open in browser’ option.

• Create ‘Excel Services’ Service Application (ESA)
  

This can be achieved via Central Admin or Powershell. Remember to start the service once created. If you don’t have an ESA provisioned, you will receive the following error when you attempt to render from a browser:

 

And if you haven’t started the ‘Excel Calculation Services’ from within ‘Manage Services on Server’, you receive the following:

 

• Grant IIS App Identity SQL access to Content Database
  

As a best practice, the IIS application pool identity of your Excel Services Application should be a domain/service account. If this is the case (and it should be), this service account needs to be granted SQL ‘dbo’ permissions against the Content Database of your Site Collection(s). This setting is not applied automatically even when you provision Excel Services and determine which proxy group it becomes a member of. If you miss this step, you will see the following error:

Which corresponds to the following in the ULS logs:

Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSessionException: The workbook cannot be opened. at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethodCore(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)     at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethod(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)     

Excel Services should now be your default option for open Excel workbooks, although this can be changed on a per Document Library basis if there is a requirement to use a local Excel client instead.

Document Library > Library Settings > Advanced Settings > Default open behaviour for browser-enabled documents: – ‘Open in the client application’

The screenshot above is the default OOTB option, although there is a SharePoint Feature which changes the third option ‘Use the server default (Open in the browser), to ‘Use the server default (Open in the client application) Once activated, all Document Libraries will open in the Client unless directed otherwise

• And don’t get confused with the Office Web Apps suite!

Remember, if you want to use the Office ribbon to compliment browser rendering, you will have to purchase an ‘Office Web Apps’ license and provision as a convention Service Application:

 

 

 

 

 

Unknown SQL Exception 297 occurred

It’s never a pretty sight when you open the Windows Application Log and discover that dreaded ‘sea of red’. Well, that is what I was witnessing yesterday across a SharePoint Farm I was troubleshooting. The content databases for this Farm environment are located on a SQL 2005 cluster, while the SSRS Reporting Services databases (which is the database in question), reside on a separate SQL 2008 R2 instance.

The Windows Event log was the following:

 

And this could be traced back to the following in the SharePoint ULS:

Unknown SQL Exception 297 occurred. Additional error information from SQL Server is included below. The user does not have permission to perform this action.    ffcbd7d9-9e18-4488-be91-331d3ab8c302 03/18/2013 12:33:45.24     OWSTIMER.EXE (0x0D40)     0x0A68    SharePoint Foundation     Database     tzku    High     ConnectionString: ‘Data Source=SPSSRS;Initial Catalog=SP2010_Int_ReportServer_SQL2012;Integrated Security=True;Enlist=False;Asynchronous Processing=False;Connect Timeout=15′ ConnectionState: Closed ConnectionTimeout: 15    ffcbd7d9-9e18-4488-be91-331d3ab8c302

Fortunately, the fix is quite simple. Your SharePoint Farm account requires the ‘View server state’ Server permission, which, funnily enough, is applied at the Server Level J

 

The SharePoint Farm account already has this setting applied to the SQL Server where all of the content databases reside, although this error was for the ‘SSRS Reporting Server Alerting DB, which is located on a SQL 2008 R2 instance. So, it seems that when the ‘SSRS 2012 Service Application’ was instantiated, the installation failed to apply this setting.

 

 

EXECUTE permission denied on SharePoint Config DB

Whilst troubleshooting a SharePoint 2010 throttling issue earlier this week, I stumbled across some totally unrelated SQL exceptions in the Windows Event log:

Insufficient SQL database permissions for user ‘Name: mydomain\domain_user SID: S-1-5-21-348440682-330175067-1304115618-117920 ImpersonationLevel: Impersonation’ in database ‘Config_DB_SQL_SharePoint’on SQL Server instance ‘SQL_SharePoint’. Additional error information from SQL Server is included below.

The EXECUTE permission was denied on the object ‘proc_GetProductVersions’, database ‘Config_DB_SQL_SharePoint’, schema ‘dbo’

It seems that this exception is generated when you click on the ‘Create Site’ option and Silverlight kicks-in (even though it doesn’t appear to be denying me access to anything)

 

The Farm I was troubleshooting against is patched to ‘February 2012 CU’, so I cannot confirm at this stage whether this has been fixed in a more recent update. If you are precious about what is appearing in your Windows Event Logs and want to mitigate against the ‘red-herrings’, you can apply the following SQL fix (but be extra careful as this is against the ‘Config_DB’)

• Expand your SharePoint Configuration database and navigate to ‘proc_GetProductVersions’ under Stored Procedures
• Right-click proc_GetProductVersions’ and choose Permissions
• Select the Search button and browse for ‘WSS_Content_Application_Pools’
• Provide ‘Execute’ permissions for ‘WSS_Content_Application_Pools’
• Click OK

   

  

 

 

Issues when deploying Access Services in SharePoint 2010

Access Services in SharePoint 2010 provides a neat mechanism for editing, updating and publishing a Microsoft Access databases in the browser. And there is no requirement for full blown copy of the Access client on your desktop unless you want to make any database changes.

This blog article is not going to provide a functionality overview of Access in SharePoint, or a break-down of basic the installation steps (which are pretty straightforward), but I wanted to highlight a few of the little gotchas which can cause grief once this Service Application has been instantiated.

The troubleshooting tips I’ve made assume the following:

• Your SharePoint 2010 Farm is running with an Enterprise license and is patched to SP1 or above
• Access Services is configured as a Service Application within your Farm and the service itself is set to ‘Running‘
• Either the ‘SQL Server Reporting Service 2012’ Service Application, or the ‘Reporting Services –Add-in’ for SharePoint Integrated mode has been applied
• The SharePoint ‘State Service’ has been configured (this is a ‘Powershell Only’ Service Application in terms of provisioning) http://technet.microsoft.com/en-us/library/ee704548(v=office.14).aspx
• You have created either a Web, Contacts or Project database using the standard SharePoint template

Issue 1

Once you’ve created your desired Access Services database and attempt to open it for the first time, you may find the ‘ever-so-helpful’ (not) failure notification: ‘An error has occurred. Click here to try again’

So where is my correlation ID to help me track the problem? In this instance, the root of the problem is loud and clear if you open the Windows Event Log.

Access Services runs with the context of it’s IIS Application Pool identity, and thus, this account requires ‘dbo’ access to the SharePoint content database. This can be achieved by locating the domain account (this should not be running under Local System by the way) in SQL Management Studio and editing its designated permissions (below shows an example of ‘db_owner’ granted database owne’ rights to the ‘Projects_DB’

 
 

 
 

Issue 2

When you try and run a report from within the Access Services ‘Report Centre’, you may witness the following error even if you have enabled Session State within your Farm environment: ‘This report failed to load because session state is not confirmed on’.

To confirm Session State is enabled, run ‘Get-SPServiceApplication’ from within Powershell and confirm it’s provisioned. One point to remember though, an IIS reset is required after provisioning Session State.

If ‘State Service’ is listed, the issue is down to Reporting Services not being configured correctly. And this is likely to be the cause of Issue 3.

Issue 3

With previous version of SQL Server Reporting Services (i.e SQL 2008 R2) there was a requirement to modify the ‘rsreportserver.config’ file to enable Access Services to leverage SSRS (and this was a rather painful exercise). However, the introduction of SSRS 2012 as a conventional Service App mitigates this manual step, although you still need to run some Powershell for a fully functional reporting experience.

$apps = Get-SPRSServiceApplication

foreach ($app in $apps)

{

New-SPRSExtension -identity $app -ExtensionType “Data” -name “ADS” –TypeName “Microsoft.Office.Access.Reports.DataProcessing.AdsConnection,

Microsoft.Office.Access.Server.DataServer, Version=14.0.0.0, Culture=Neutral,

PublicKeyToken=71e9bce111e9429c”

}

The first screenshot below displays the error you will witness if you don’t run the Powershell fix-up script

 
 

 
 

 
 

And now you have a working Access Reporting service…..