Edit the SharePoint ‘site use and deletion’ email

The SharePoint 2010 ‘Site Use Confirmation and Deletion’ feature provides a simple way of implementing some governance around the data retention of your Site Collections.

When configured within Central Administration (Application Management > Confirm site use and deletion) you have the option of not only sending automated emails requesting confirmation that a site is ‘still in use’, but also forcing the automatic deletion of a site collection if a confirmation request in continually ignored.

The automated email, which incidentally is sent to all Site Collection Admins when this feature is instantiated, can be edited and branded to reflect your company needs. It is also horribly direct, although perhaps this is the best approach to avoid any ‘SharePoint sprawl’ scenarios.

The file you need to edit can be found in the following folder and should be edited consistently across your Farm.

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\1033\XML\DEADWEB.xml

It’s all good and well turning on the feature and editing the DeadWeb.xml file, but how do you go about testing your new automated mail? Simple, you can trigger the ‘Dead Site Delete’ Timer Job:

So, you’ve edited the site confirmation email to meet your requirements and have performed a test. Now it’s time to put the frighteners on your Site Collection Admins (ideally by setting-up a WebCam to capture the moment the email with the heading ‘ACTION REQUIRED: Your SharePoint site collection is about to expire’ arrives in their Inbox!!)

If you want to discover which Site Collections have had their expiration date updated, the quickest way to do this is the open SQL Server.

Locate the ‘AllSites‘ table within your content database and look at the values associated to the ‘DeadWebNotifyCount‘ column.

If you see a zero (0), you know the expiration date has been extended. In this example, the other ‘DeadWebNotifyCount‘ column total of 17 indicates that the automated email was sent 17 times with no response.


Named SQL Instance stops User Profile Service

Aaaarrrrggghhh! I really did think I’d experienced them all but today I’ve witnessed yet another error which was causing the FIM Synchronization Service to fail.

Having re-provisioned the UPS Service Application to solve another issue, when I clicked on ‘Start’ the status would remain on ‘Starting’ for around 10 minutes before eventually bombing out and displaying ‘stopped’.

Fortunately, the ULS provided me with a starting point – this is what I was witnessing:


ILMPostSetupConfiguration: ILM Configuration: Validating installation of SQL Service.


ERROR  ILMPostSetupConfiguration: ILM Configuration: Validating installation of SQL Service FAILED


After referencing a number of blogs, it became apparent that FIM was failing to recognise that the User Profile SQL databases resided on a Named SQL instance (i.e SQLServer\development), and was trying to refer to the default instance. The workaround was to implement a SQL alias.

Firstly, open DNS and create a new CNAME resolving to the FQDN of your SQL Server:

‘Alias: ‘SharePointSQL‘ (this is your SQL alias which will define the call to SQL)

FQDN: ‘SharePointSQL.mydomain

IP address: ‘SQLServer.mydomain (the DNS A record of your SQL Server)


Then using the CName, create a SQL Alias.

From the Run Command, type ‘Cliconfg’

Choose ‘TCP/IP’

Server alias: ‘SharePointSQL‘ (CName you created earlier)

Server name: SQLServer\development (name of your SQL Server and instance)

With this is place, you should now be able to successfully run an import.

This SharePoint 2010 Farm is patched to Feb 2012 CU

Reporting Services Web Service is ‘too busy’

I took a call from a customer earlier this week complaining that they were unable execute ANY of their SSRS reports. Replicating the issue gave me the ever helpful (not) generic error message below:

Both of the Web Servers running the SSRS Service Application were available, as were the SQL instances where the Content and Report Databases resided. This error was also applicable against multiple reports which were all using different data connections and data sources.

The SSRS environment, configured in SharePoint Integrated mode (SQL 2012), is leveraging Kerberos Constrained Delegation and thus, relies on the ‘Claims to Windows Token’ Service. Again, this service was available, I knew as much as Performance Point (which also uses Kerberos Constrained Delegation) was unaffected

So where was the bottleneck?

I have seen SSRS issues previously where for no apparent reason, the underlying data connection had somehow managed to ‘disable itself. In fact, this is something I have raised with Microsoft via Premier Support, although as it was totally intermittent and we were unable to replicate the issue, scoping the call was pretty much a non-starter.

On this occasion however, when I tried to open any SharePoint SSRS data connection I received the following error:

The correlation ID in question was linked to exception below in the ULS:

Notified the load balancer and raising RecoverableException for exception: System.ServiceModel.ServerTooBusyException: The HTTP service located at http://sharepointserver1:32843/a897fc88a99340e3b177754c4216ea4d/ReportingWebService.svc is too busy. —> System.Net.WebException: The remote server returned an error: (503) Server Unavailable.

This error highlighted that the failing component was directly applicable to the SSRS layer. The culprit was the SSRS application pool(s) which were stopped.

However, upon restarting the app pool, the issue immediately returned once I tried to open any data connection. The problem lay with the identity of the application pool, namely its domain account identity.

A quick dive into Active Directory showed that the password for the SSRS domain/service account had a password which had indeed expired. Doh! Basics! Resetting this via the ‘Managed Accounts’ setting in SharePoint Central Administration meant we was up and running!

Lesson to be learnt: Always ensure your service accounts have the attribute ‘Password Never Expires’ associated to them…..

The joys of troubleshooting the SP2010 User Profile Service….

It is a sad fact of life that the hours we have lost trying to fathom why the ‘User Profile Synchronization Service’ is stuck on starting are lost forever! And with that in mind, I’m going to swerve that subject altogether.

But what tools can you use to troubleshoot your User Profile imports, once you’re smoking a cigar having seen the word ‘started’ against the Synchronization Service?

From my experience, no initial import is the same, particularly if you’re connecting to Active Directory and have created a number of custom filters and exclusions. Just recently, I witnessed two SharePoint 2010 Farms, configured identically, synchronising against the same AD Organizational Unit structure only for the end number of profiles to be different. I’m only talking a handful of users out of several thousand, but still the inconsistency was there. And even after starting with another clean User Profile canvass across both Farms, the initial ‘Full Import’ generated different results.

It has to be said that if you run subsequent ‘Full Imports’ following your initial synch job, eventually you should see the same results, although this can be time consuming…..not to mention the light on your cigar will have gone out by now!

Anyhow, I’ve have found two very useful tools/blogs which go beyond what you can achieve within Central Admin when troubleshooting UPS.

The first allows you to export all of your profiles out of SharePoint and into Excel – which is a great way of determining which profiles have not been grabbed:


The second contains some very useful SQL views which provide a great alternative to the restricted API. Always remember to avoid creating any views against a Production SharePoint content database, otherwise your call for help to Microsoft with fall on deaf ears!


As always, refer to Spencer Harbar’s comprehensive blog (http://harbar.net) for an in-depth look into everything UPS