Archive for April, 2011

Simple SQL query to know who created Site Collections in content DB

April 6, 2011 Leave a comment

We have multiple test environments, and one of our requirements is different site templates. So our testers and users in order to test their site templates, they keep creating site collections at an average of 150 per month.  Since our test environment is not designed to scale these levels. One of the impact is search service.

I could have enabled site usage confirmation & deletion, and some times it is annoying as we already have so many site collections in use. So i have used a SQL query against the content database to give me the site names. Then i create a batch file to delete all those sites

Select AA.SiteID, AA.tp_Login,AA.tp_Title,BB.FullUrl,AA.TimeCreated from (SELECT S.Id as SiteID,S.OwnerID,U.tp_Login,S.TimeCreated,U.tp_Title
FROM [content_databasename].[dbo].[Sites] S
inner join [content_databasename].[dbo].[UserInfo] U
on S.Id = u.tp_SiteID and S.OwnerID = u.tp_ID
where S.TimeCreated > ‘2010-07-12 09:38:33.000’) AA
inner join [content_databasename].[dbo].Webs BB on
AA.SiteID = BB.SiteId and BB.ParentWebId Is null
where FullUrl like ‘sites/test%’

This could be handy for some administrators. This gives you the site url, owner name, date when create. Additional where clauses can be included based on the need.

You can use BCS to get this data into a sharepoint list.

Categories: Administration