Renaming the WSS_UsageApplication database

SharePointIn the event that you find yourself needing to rename the WSS_UsageApplication database in your SharePoint 2010 farm, you’re in luck, as this database is one of the few service application databases that can be moved without having to re-provision the service application!  Unlike most of the other databases in the farm, this one isn’t operated on by SharePoint services, but rather by direct calls from SharePoint or via SharePoint timer jobs.  Since there are no services to stop, you can dive right in.

The first challenge is actually re-naming the database, as the service application account will still be connected to the database, preventing SQL Management Studio from obtaining an exclusive lock on the database.  This is handled with a T-SQL statement that switches the database into single-user mode, renames it, and then switches it back to multi-user mode.

USE master;
GO
ALTER DATABASE WSS_UsageApplication SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE WSS_UsageApplication Modify Name = WSS_UsageApplication_NewName ;
GO
ALTER DATABASE WSS_UsageApplication_NewName SET MULTI_USER;
GO

Then, the WSS_UsageApplication service application needs to be updated to use the new database name.  This is accomplished with a single Powershell command.

Set-SPUsageApplication -Identity "WSS_UsageApplication" -DatabaseName "WSS_UsageApplication_NewName" -DatabaseServer "<sql server name>"

And that’s all it takes!  You can also use the Set-SPUsageApplication command to move the database to a different SQL server, but keep in mind that you will need to re-create the SQL login on the new server and fix the orphaned database user.

 

SharePoint Designer fails to connect and error details are blank

SharePointWhen attempting to connect using SharePoint Designer 2010 to a SharePoint 2010 server that is low on available physical RAM, you may receive the error “The server could not complete your request”, despite the fact that the site is still loading via the browser.

The server could not complete your request.

However, the information provided in the “Details” section is anything but useful, as it is completely empty.

Blank error dialog

Using a sniffing program such as Fiddler2 or Wireshark to look at the requests that are being sent to the server reveals that the following POST statement is receiving an HTTP 500 error:

POST http://site.com/_vti_bin/client.svc/ProcessQuery HTTP/1.1
HTTP/1.1 500 System.ServiceModel.ServiceActivationException

Browsing directly to the webservice then reveals the true root of the issue:

Memory gates checking failed because the free memory is less than 5% of total memory.“Memory gates checking failed because the free memory is less than 5% of total memory.  As a result, the service will not be available for incoming requests.  To resolve this, either reduce the load on the machine or adjust the value of minFreeMemoryPercentageToActivateService on the serviceHostingEnvironment config element.”

SharePoint Designer can hit a server quite hard with a large number of very resource-expensive requests, so this memory gate protects an already heavily-loaded server from getting pushed over the edge, causing it to start returning errors in normal user-space.  While it does give a setting to change as a workaround, this is not the correct solution, as it puts the stability of the environment at risk.  Either find out what is burning so much RAM, do what you can to offload heavy applications or services to other machines, or bite the bullet and add more RAM.
Memory gates checking failed because the free memory (539389952 bytes) is less than 5% of total memory.  As a result, the service will not be available for incoming requests.  To resolve this, either reduce the load on the machine or adjust the value of minFreeMemoryPercentageToActivateService on the serviceHostingEnvironment config element.

 

WebDAV 7.5 Breaks SharePoint Library Drive Mapping and Explorer View

SharePointThose familiar with SharePoint will know that it it has its own built-in WebDAV handler that typically interacts with the WebDAV Mini-redirector on the client end. However, some are not aware of this and may install the WebDAV 7.5 for IIS 7.0. and this breaks all kinds of things within SharePoint.

Many administrators, upon seeing that SharePoint is returning errors, will run an IIS reset. This will actually bring SharePoint back to an operational state, but users will find that features such as opening document libraries in Explorer and mapping drive letters to document libraries will no longer work, and they will repeatedly be prompted for credentials but to no avail. Snooping the HTTP traffic will reveal the following headers:

Client ->  PROPFIND /TestLibrary HTTP/1.1\r\n
Server ->  HTTP/1.1 405 Method Not Allowed

This is because the WebDAV 7.5 installer adds the following module to the IIS configuration at the server-level:

Name: WebDAVModule
Code: %SystemRoot%\system32\inetsrv\webdav.dll
Module Type: Native
Entry Type: Local

This module is directing the WebDAV requests over to the WebDAV 7.5 DLL rather than to SharePoint’s own WebDAV handler. This issue can be resolved by either manually removing the module from the list of configured modules in IIS, or the uninstaller for WebDAV 7.5 should remove this entry for you (but you should check to be sure). To remove the mapping manually:

  1. Open Internet Information Services (IIS) Manager
  2. Select the top-level entry in the tree view (should be the server name)
  3. In the center window (Features View), open the Modules item under the IIS section
  4. Remove the mapping named “WebDAVModule”. Take note of the settings in case it needs to be configured elsewhere
  5. Profit! (test it)

If WebDAV is required for other sites running on the server, then configure the mapping directly on the site(s) that require it, rather than at the server level. No IIS reset is required after the module is removed, so you should immediately be able to map drive letters to SharePoint libraries and open them in Explorer again.

SQL backups fail due to offline full-text catalogs

Sometimes, MS SQL backup operations will fail because one or more full-text catalogs are offline.  Typically the error reported in the SQL error log will look like this:

Could not backup database: The backup of full-text catalog 'ix_something' is not permitted
because it is not online. Check errorlog file for the reason that full-text catalog became
offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE
clauses to restrict the selection to include only online data.

If you were backing up a single database, it would be obvious which database contained the offline full-text catalog.  However, if a multi-database backup job fails, SQL does not report which database failed to back up.  To pull a list of all full-text indexes which are offline, as well as their associated databases, use the following query:

DECLARE @command varchar(1000)
SELECT @command = 'USE ?; SELECT "?" AS DatabaseName, Name AS IndexName, State_Desc,
Physical_Name FROM sys.database_files where Type_Desc="FULLTEXT" and State_Desc="Offline"'
EXEC sp_MSforeachdb @command

SharePoint 2010 TaxonomyPicker.ascx bug – &#44;

SharePointNow that SharePoint 2010 is out in the wild, we get to deal with all kinds of new strangeness! The first issue I ran into is that several of the ASCX controls within the this folder

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\
TEMPLATE\CONTROLTEMPLATES

contain lines such as the following:

<%@ Control className="TaxonomyPickerControl" Language="C#"
Inherits="Microsoft.SharePoint.Portal.WebControls.TaxonomyPicker&#44;Microsoft.SharePoint.Portal, Version=14.0.0.0 ...

Those who have done any HTML work will notice the “&#44;” sticking out like a sore thumb. This is the HTML numerical ASCII code for a comma, and has no place in a .ascx file. On the server I was working with, there was also this event in the server’s Application log every time the application pool spooled up:

Source: SharePoint Foundation
Event ID: 7043
Load control template file /_controltemplates/TaxonomyPicker.ascx failed: Could not load
type 'Microsoft.SharePoint.Portal.WebControls.TaxonomyPicker' from assembly
'Microsoft.SharePoint.Portal, Version=14.0.0.0, Culture=neutral,
PublicKeyToken=71e9bce111e9429c'.asdf

The controls I have identified as containing this &#44; character are as follows:

MyLinks.ascx
MySiteLink.ascx
MySiteRedirection.ascx
SocialData.ascx
TaxonomyPicker.ascx

Apparently some people have had success with simply replacing the &#44; with a comma (which will require recreating the web app and/or site collection, since template changes don’t trickle down), but in the case of the server I was working with, that did not resolve the type load error.  Hopefully we will see some information soon about whether this really is a bug or not, and if so, a reliable resolution.