Workaround to remove orphan components when site owners delete site templates or sandbox solutions from solution gallery without deactivating them

If you are using sandbox solution or saved site template on SharePoint 2010, you will noticed those solutions will be uploaded into solution gallery. If you have not upgraded to 2011 December CU and you are on load balanced multiple tier environment (example is two WFS and one application server), site owners are able to delete site templates or sandbox solutions from solution gallery without deactivating them through edit page. Those site templates or sandbox solutions will become orphan and users will still be able to see them in site creation template. Of cause, users will get error if they want to create ant site from these orphan templates. You could refer my previous blog for details.

In this blog, I will use save site template as example to explain how we could workaround and clean up those orphan sandbox solutions. Before we explain the clean up procedure, let us identify what will be added into content database after we activate the sandbox solution and what would be cleaned up automatically if we deactivate and then delete the solution. I’m using two saved templates ITDebug.wsp that saved form out of box team site for the testing.

First, we will identify what happened inside content database if we upload, activate, deactivate, and delete the solution ITDebug.wsp in single SharePoint server farm.

• Upload the ITDebug.wsp, there is no entry in dbo.Feature or dbc.Solutions tables.
• Activate the ITDebug.wsp, you will see ONE additional feature entry in dbo.Feature and ONE additional entry in dbc.Solutions table
• Deactivate ITDebug.wsp, there is no entry in dbo.Feature or dbc.Solutions tables for this saved template. Both are cleaned up
• Remove ITDebug.wsp, the file is removed from the solution gallery

 

Here is the solution gallery screen shot after ITDebug.wsp solution activated.

Here is the one entry inside table dbc.Solutions with name ITDebug.wsp.

Here is the screen shot for entry inside table dbo.Feature for ITDebug.wsp solution.

Here is the SQL script to get the entry. Please note the content database for testing site http://sbx08:3797/sites/Orphan is WSS_Content_TEST_ORPHAN. You could identify the database name from central admin when you view the site or from ‘stsadm -o enumsites’ command.

SELECT [WSS_Content_TEST_ORPHAN].[dbo].[Features].[SiteId]

      ,[WSS_Content_TEST_ORPHAN].[dbo].[Features].[FeatureId]

      ,[WSS_Content_TEST_ORPHAN].[dbo].[Features].[SolutionId]

      ,[WSS_Content_TEST_ORPHAN].[dbo].[Solutions].[Name]

  FROM [WSS_Content_TEST_ORPHAN].[dbo].[Features] INNER JOIN [WSS_Content_TEST_ORPHAN].[dbo].[Solutions]

  ON [WSS_Content_TEST_ORPHAN].[dbo].[Features].[SolutionId] = [WSS_Content_TEST_ORPHAN].[dbo].[Solutions].[SolutionId]

 

Second, we will identify what happened inside content database if we upload, activate, delete without deactivating the solution ITDebug.wsp in multiple SharePoint server farm.

• Upload the ITDebug.wsp, there is no entry in dbo.Feature or dbc.Solutions tables. This is same as before.
• Activate the ITDebug.wsp, you will see ONE additional feature entry in dbo.Feature and ONE additional entry in dbc.Solutions table. This is same as before.
• Delete ITDebug.wsp without deactivating it,  both entries inside dbo.Feature and dbc.Solutions table are still there!!!  These are orphan entries.

You could use the following SQL script to identify the orphan solutions. Please replace the content database name with yours.

SELECT [Content_DBNAME].[dbo].[Solutions].[Name]

  FROM [Content_DBNAME].[dbo].[Solutions] INNER JOIN [Content_DBNAME].[dbo].[Features]

  ON [Content_DBNAME].[dbo].[Features].[SolutionId] != [Content_DBNAME].[dbo].[Solutions].[SolutionId]

Now it's time to describe the workaround to cleanup those orphan components. As you see we need to clean up two entries, one inside feature table and another inside Solutions table. Here is the procedures.

1. Use powershell or stsadm command to disable to feature for the sanbox solution. You need to pass the feature ID you get from previous queries.

  

     Disable-SPFeature –Identity DA9016BA-93F2-44AD-99B0-1F80B968E7E7 –url http://projectsdev/sites/Harry/ –Confirm:$false -Force:$true

      2. Use database delete to remove the orphan entry inside Solution table for the solution. STOP! This is not supported by Microsoft. If you update the SharePoint tables, your farm will not be supported by Microsoft any more!

Well, I have decided to leave the solution entry at this time and will work with Microsoft to see anyway we could clean it up using Powershell or OM API. I have tried the following commands but there are not able to remove the solution.

 

    Uninstall-SPUserSolution –Identity emsdev.wsp  -Site http://yoursharepoint/sites/Harry/ -Confirm:$false –Force:$true

    Remove-SPUserSolution –Identity emsdev.wsp  -Site http://yoursharepoint/sites/Harry/ -Confirm:$false –Force:$true

If you have a better way to clean up the solution table without deleting the entry from the database table, please let me know. We are also in the process to automate process to identify orphan solutions and deactivate the feature.