April 16

Code of the Day : Cleaning invalid SPConnectedServiceApplication objects

Last modified by sbrickey on 4/16/2013 3:06 PM
Following on the tails of a previous post [Code of the Day : SharePoint Service App cleanup], I found that I had to do some cleanup to the remnants left behind from prior deployments.

The basic background is:
The SharePoint Service Application Architecture is based heavily on the use of WCF services for communication between the requesting computer (often a Web Front End) and the servicing computer (often an app tier server). The topology service (in conjunction with the internal load balancer) is used to help track endpoints, and route them from the requesting code to the servicing code.

When developing a new custom service application, it is understandable that you'd be creating the service apps in the environment to test.
If/when a problem is identified, the code is removed from SP, changes are made, and the new code is redeployed.

Except that service applications tend to leave things behind, unless you are careful about cleaning up after yourself (see previous article).

Here's a SQL query for the SharePoint_Config database that identifies any SPConnectedServiceApplication objects that don't belong to anything.

SELECT Objects_ConnectedServiceApps.Id

     , Objects_ConnectedServiceApps.objType

     , Objects_ConnectedServiceApps.Properties

     , dbo.Objects.Name

     , dbo.Objects.Properties

  FROM (

         SELECT *

           FROM (

                  select CAST(dbo.Objects.Properties AS xml).value('(/object/sFld[@name="m_ApplicationId"])[1]', 'nvarchar(MAX)') AS appId

                       , CAST(dbo.Objects.Properties AS xml).value('(/object/@type)[1]', 'nvarchar(MAX)') AS objType

                       , *

                    from dbo.Objects

                ) Objs

          WHERE Objs.objType = 'Microsoft.SharePoint.SPConnectedServiceApplication, Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'

       ) Objects_ConnectedServiceApps


  LEFT JOIN dbo.Objects
    ON dbo.Objects.Id = Objects_ConnectedServiceApps.appId

 WHERE dbo.Objects.Name IS NULL

NOTE: Microsoft will tell you REPEATEDLY that you are NEVER to touch the databases directly. That said, this is a SELECT statement, so no changes will ever be made. Also, this should ONLY be used in DEVELOPMENT environments, NEVER in production.


This can be used to serve two purposes.
1. you can use the IDs with the SharePoint API to clean up the remnants (note that the actual changes/fixing occurs with the API... DO NOT CHANGE THE DATABASE).
2. you can use this to confirm that your custom solutions won't leave the farm in a bad situation.


Cleaning up your orphaned SPConnectedServiceApplication objects

# create an array with all of the GUIDs that you want to clean up

$ids
= @("32D1BD40-56E2-43FC-8B1E-26E3AABA244A","74CA196B-A9AA-4BDD-B463-99375EF0D46E")

# for each GUID, convert into a Guid object, call GetObject (load object from database), then Delete

$ids | % { New-Object System.Guid($_) } | % { [Microsoft.SharePoint.Administration.SPFarm]::Local.GetObject($_) } | % { $_.Delete() }


NOTE: this code MAKES CHANGES. IRREVERSIBLE CHANGES. If you are not comfortable running this code, DON'T.





comments powered by Disqus