SharePoint: Delete / Purge / Remove Orphaned Sites or Site Collections

If you are here just to know how to delete a site or a site collection: Go to here. If you are here to learn how to find and purge the orphan sites or site collections, go ahead and read the below article.

Microsoft makes it tough with simple things like finding and deleting the orhpaned sites/site collections. There seems to be no well documented way to do this task. After googling for many search strings, below are few resources and steps you can take to clean up your content and config databases.

1. Run the following command:

stsadm -o databaserepair -url http://<URL_of_WindowsSharePointServices_Site&gt; -databasename <name of database that contains the orphan item>

2. If the above command reveals any orhpaned sites, then run the above command with -deletecorruption parameter:

stsadm -o databaserepair -url http://<URL_of_WindowsSharePointServices_Site&gt; -databasename <name of database that contains the orphan item that is to be deleted> -deletecorruption

For more details: read this. Bloggers say that these seem to do nothing but might be doing something.

3. If the above stsadm command doesnt reveal any orphan sites, then there is a DB script provided in this blog post, which help you find out the orhpaned sites. Its not a good practice to run queries against the sharepoint database but this really works so who cares.

Drop table orphanlist

CREATE TABLE [dbo].[orphanlist](
[farm] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[databasename] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SiteID] [uniqueidentifier] NULL,
[sitepath] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[type] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

drop table orphan_hopper
@dbname as varchar(250),
@cmdstr as varchar(2000),
@dbid as varchar(250),
@configdb as varchar(250)

/** only change the following line and nothing else, change spskills_config_db to your config db name **/
select @configdb = ‘spskills_config_db’

/** Change nothing below this line **/
select @cmdstr =
‘select distinct as ”databasename”, as ”dbid” into orphan_hopper
[‘ + @configdb + ‘].dbo.sitemap as a inner join
[‘ + @configdb + ‘].dbo.objects as b on inner join
[‘ + @configdb + ‘].dbo.objects as c on inner join
[‘ + @configdb + ‘].dbo.objects as d on inner join
[‘ + @configdb + ‘].dbo.objects as e on ‘
exec (@cmdstr)

Select databasename, dbid
From orphan_hopper

FETCH NEXT FROM DBCursor into @DBName, @dbid

INSERT INTO orphanlist([Type], farm, databasename,[sitepath], SiteID)
select ”Potential ConfigDB orphan:” + ”’+@dbname+”’ as [Type], ”’+@configdb+”’ as [farm], ”’+@dbname+”’ as [databasename],path as [sitepath], id as [SiteID] from [‘+@configdb+’].dbo.sitemap where id not in (select id from [‘+@dbname+’].dbo.sites) and databaseid = ”’+@dbid+”’
select ”Potential ConfigDB orphan:” + ”’+@dbname+”’ as [Type], ”’+@configdb+”’ as [farm], ”’+@dbname+”’ as [databasename],path as [sitepath], id as [SiteID] from [‘+@configdb+’].dbo.sitemap where id not in (select siteid from [‘+@dbname+’].dbo.webs where parentwebid is null) and databaseid = ”’+@dbid+”’
select ”Potential ContentDB orphans:” + ”’+@dbname+”’ as [Type], ”’+@configdb+”’ as [farm], ”’+@dbname+”’ as [databasename],fullurl as [sitepath], siteid as [SiteID] from [‘+@dbname+’].dbo.webs where parentwebid is null and siteid not in (select id from [‘+@configdb+’].dbo.sitemap where databaseid = ”’+@dbid+”’)
select ”Potential ContentDB orphan:” + ”’+@dbname+”’ as [Type], ”’+@configdb+”’ as [farm], ”’+@dbname+”’ as [databasename],fullurl as [sitepath], siteid as [SiteID] from [‘+@dbname+’].dbo.webs where parentwebid is null and siteid not in (select id from [‘+@dbname+’].dbo.sites)
FETCH NEXT FROM DBCursor into @DBName, @dbid

select * from orphanlist

4. If either of the above show that your sharepoint db has orhpaned objects, the recommended practice is to detach and re-attach the content database for that particular web application. The detach and attach process can be done in the central adminsitration. Read this blog post which I think mentions how to detach/re-attach using the SPS 2003 Centram Admin. In SPS 2007, just find out the Manage Content Database section under which you should be able to find the web application and then there is a way to re-attach the content db. If you face any problems re-attaching, try to do the same using stsadm tool.


Tool to purge or delete site collections safely and quickly

I had to develop this tiny utility to delete around 1500 site collections which are autogenerated by some other tool. The autogenerated site collections were created in the format like this:

So this tool of mine identifies the site collections which matches the above format and deletes them. At the same time it keeps log of each and every action taken in the log file provided. If anyone needs to delete site collections in thousands then just modify the code according to your need.

Belowis a screenshot of the tool:

Utility Tool

Solution Files

(since wordpress doesnt allow to upload zip files, I renamed the zip file with a .doc extension.)

Links: Infopath Forms, Workflow (Visual Studio 2005)

Design a form to respond to a workflow status : A very good resource for a starter in InfoPath Forms Development and workflow integration with Sharepoint.

Introducing of InfoPath Forms with Workflow : Explains theoretically what all are the workflows available in SharePoint 2007 and how they can be used with InfoPath Forms 2007. More theory than practical implementation.

Some other links:

Three-state workflow | Few Caveats related to InfoPath and Sharepoint | Steps to add workflow with InfoPath | Custom Workflow Development | 10 Issues& Resolutions in using Workflows with SharePoint

How to develop custom workflow using InfoPath forms for association, initiation and task forms: Blog1 : Blog2

How to develop workflow with InfoPath forms explained from a basic example to a complex one with code: Here u go

Ditto by from MSDN : Click here

Show the InfoPath form information submitted by user in the workflow task form so that the approver can view the form details and decide to either Approve or Reject: Click Here