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.

Use MSDB
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
declare
@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 b.name as ”databasename”, b.id as ”dbid” into orphan_hopper
from
[‘ + @configdb + ‘].dbo.sitemap as a inner join
[‘ + @configdb + ‘].dbo.objects as b on a.databaseid=b.id inner join
[‘ + @configdb + ‘].dbo.objects as c on c.id=a.applicationid inner join
[‘ + @configdb + ‘].dbo.objects as d on b.parentid=d.id inner join
[‘ + @configdb + ‘].dbo.objects as e on d.parentid=e.id ‘
exec (@cmdstr)

DECLARE DBCursor CURSOR For
Select databasename, dbid
From orphan_hopper

OPEN DBCursor
FETCH NEXT FROM DBCursor into @DBName, @dbid

WHILE @@FETCH_STATUS =0
BEGIN
INSERT INTO orphanlist([Type], farm, databasename,[sitepath], SiteID)
EXEC
(‘
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+”’
union
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+”’
union
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+”’)
union
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
END
CLOSE DBCursor
DEALLOCATE DBCursor

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.

27 Responses

  1. […] Deleting / Purging / Removing Orphaned Sites or Site Collections […]

  2. Hi! I was surfing and found your blog post… nice! I love your blog. 🙂 Cheers! Sandra. R.

  3. Sign: gfndc Hello!!! hwfmy and 3924eebtmcsbxy and 3461 : I love your site. 🙂 Love design!!! I just came across your blog and wanted to say that Ive really enjoyed browsing your blog posts.

  4. […] SharePoint: Delete / Purge / Remove Orphaned Sites or Site Collections November 2007 3 comments […]

  5. How about supplying an actual example of what the parameters are suppose to hold instead of just quoting Microsoft’s web site!

  6. How about Site Templates in SharePoint 2010??? I opened our Solutions Gallery and deleted sites without the new 2010 prerequisite of deactivate ing the site templates first. Now they appear grayed under the Custom tab under “Select a template:” and also under the new Icon based QUI in 2010 sites. Are these considered orphaned sites and be dealt with similarly as mentioned above?

  7. Hi i would like to thank you for such a fantastic blog. I was trying to find solution for orphan sites issue and your commands really helped me. Thanks once again and keep posting such a useful blog posts.

  8. What is it about this product that people go mad for?

  9. You actually make it seem so easy with your presentation but I find this
    topic to be really something which I think I would never understand.

    It seems too complex and very broad for me. I am
    looking forward for your next post, I will try to get
    the hang of it!

  10. I need to to thank you for this fantastic read!! I absolutely enjoyed every bit of it.
    I have you book-marked to check out new stuff you post…

  11. Thank you for the auspicious writeup. It if truth be told was once a amusement account it.
    Glance advanced to far added agreeable from you! However,
    how can we communicate?

  12. Your way of telling everything in this piece of writing is
    genuinely fastidious, all be able to without difficulty be aware of it, Thanks a lot.

  13. I like the helpful info you provide in your
    articles. I’ll bookmark your blog and check again here regularly. I’m quite sure I’ll learn many new stuff right here! Best of luck for the next!

  14. If you want to get much from this article then you have to apply these methods to
    your won webpage.

  15. Hey there! I just wanted to ask if you ever have any
    issues with hackers? My last blog (wordpress) was hacked and I ended up losing a few
    months of hard work due to no backup. Do you have any methods to protect against
    hackers?

  16. I’m amazed, I have to admit. Seldom do I come across a blog that’s both educative and interesting, and
    let me tell you, you’ve hit the nail on the head. The problem is something which not enough folks are speaking intelligently about. I’m very happy that I found this during my
    hunt for something regarding this.

  17. Of course of instruction, in the hyper-accelerated smartphone
    market where for a degraded cash advance, payday loan
    is the answer. payday loans no faxing For illustration, you can sideload former keyboard apps without job but, misrepresenting
    the cards and the development act of companies promoting them.

  18. Rates on these types of big financial loans could peradventure be rigid
    demand to fax sullen documents and paperwork for the like. Read More Here An unexpected aliveness twist
    – Unanticipated things can leave a co signer for the loans.

  19. tów przy
    ręką, rzuciła w niego obwarzankiem. błyskawiczny
    Oraz dodatkowo naturalnie byczy, pomyślał, iż nie wykonywa akurat kramu rybnego.
    Przyjemność udała się
    przeszło wszelkie wymogi, ledwie dwie przecznice cieniej widzi.

  20. Unquestionably imagine that that you said. Your favourite
    reason seemed to be on the web the easiest thing to have in mind
    of. I say to you, I definitely get annoyed even as people think about worries that they just do not recognize about.

    You managed to hit the nail upon the highest and outlined out the whole thing with no need
    side-effects , folks could take a signal.
    Will probably be again to get more. Thank you

  21. An outstanding share! I have just forwarded this onto a co-worker who has been
    conducting a little research on this. And he actually ordered me lunch because I discovered it for him.
    .. lol. So let me reword this…. Thanks for the meal!! But yeah,
    thanx for spending some time to talk about this issue here on
    your blog.

  22. Thanks designed for sharing such a pleasant idea, article is nice,
    thats why i have read it completely

  23. Remember that dogs are great conversation topics here.
    She will not want to be noticed with someone what individual often appears to be like the same on
    each and every day. Hope this gives you an idea on how to get a girlfriend.

  24. I am regular reader, how are you everybody? This post posted at this website is in fact nice.

  25. Right now, this very second, you may be wondering, “What do I say to get my girlfriend back. For some reason it seems to be a common misconception that taxicab drivers will sleep with their clientele when they get the chance. Did you know that 99% of men get turned down by women everyday.

  26. I am extremely impressed with your writing skills as well as with the layout on your blog.
    Is this a paid theme or did you modify it yourself?
    Anyway keep up the excellent quality writing, it is rare to see a
    nice blog like this one nowadays.

  27. I enjoy looking through an article that will make people think.

    Also, many thanks for allowing for me to comment!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: