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

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

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
  1. No comments yet.
  1. No trackbacks yet.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: