A way to determine the size of subsites?

There's a tool on Codeplex that will give you this information: http://www.codeplex.com/SPUsedSpaceInfo .

Bob Klass also has a tip in his blog of another way to try to get that info:http://bobklass.blogspot.com/2009/01/easy-way-how-to-find-sharepoint-subsite.html .

Easy Way How to Find SharePoint Subsite Size

I was looking for a GOOD way to find out the size of subsites. It seems a lot of people are struggling with this. You could try SharePoint Designer. That's fun. When you ask it site properties, it will give you a different answer depending on what document libraries or lists you touched. Obviously that is not going to be reliable.

I'll cut to the chase (although I was trying to be as wordy as possible). This isn't a GOOD way, but it is a very simple way:

• open the site in Windows Explorer
• navigate to the root of the site or subsite in question
• right click, properties

That may be a little resource intensive - it takes a while to open. I think it may have to open a lot more than it appears just to get you that number, but there it is in the properties window. Piece of cake.

 

Post Script: http://www.codeplex.com/SPUsedSpaceInfo is a console app to get a CSV listing of sites and their size. Beware that the setup is pretty heavy handed - a Dotnet 3.5 SP1 installation starts right up if you aren't there already.

 

 

If you only have a couple of sites you want to take a look at and don't want to install a tool, you might try it. I would caution you to take his approach with a grain of salt, I am a lot more confident in the accuracy of a solution that uses the SharePoint object model to retrieve this information than his "hack" through the Windows Explorer GUI. I just would expect the object model to be able to present a much more true picture of how much storage a site is using than the UI would.

Some queries with Share[point content DB

Some queries with these tables:

-- Query to get all the top level site collections

SELECT SiteId AS Siteid, Id AS Webid, FullUrl AS FURL, Title, Author, TimeCreated FROM dbo.Webs WHERE (ParentWebId IS NULL)

 

-- Query to get all the child sites in a site collection

SELECT SiteId AS Siteid, Id AS Webid, FullUrl AS FURl, Title, Author, TimeCreated FROM dbo.Webs WHERE (NOT (ParentWebId IS NULL))

 

-- Query to get all the SharePoint groups in a site collection

SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.Groups.ID AS Expr1, dbo.Groups.Title AS Expr2, dbo.Groups.Description FROM dbo.Groups INNER JOIN dbo.Webs ON dbo.Groups.SiteId = dbo.Webs.SiteId

 

-- Query to get all the users in a site collection

SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.UserInfo.tp_ID, dbo.UserInfo.tp_DomainGroup, dbo.UserInfo.tp_SiteAdmin, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Email FROM dbo.UserInfo INNER JOIN dbo.Webs ON dbo.UserInfo.tp_SiteID = dbo.Webs.SiteId

 

-- Query to get all the members of the SharePoint Groups

SELECT dbo.Groups.ID, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login FROM dbo.GroupMembership INNER JOIN dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId INNER JOINdbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID

 

-- Query to get all the sites where a specific feature is activated

SELECT dbo.Webs.Id AS WebGuid, dbo.Webs.Title AS WebTitle, dbo.Webs.FullUrl AS WebUrl, dbo.Features.FeatureId, dbo.Features.TimeActivatedFROM dbo.Features INNER JOIN dbo.Webs ON dbo.Features.SiteId = dbo.Webs.SiteId AND dbo.Features.WebId = dbo.Webs.Id WHERE (dbo.Features.FeatureId = '00AFDA71-D2CE-42fg-9C63-A44004CE0104')

 

-- Query to get all the users assigned to roles

SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login FROM dbo.RoleAssignment INNER JOIN dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN dbo.UserInfo ON dbo.RoleAssignment.PrincipalId = dbo.UserInfo.tp_ID

 

-- Query to get all the SharePoint groups assigned to roles

SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle, dbo.Groups.Title AS GroupName FROM dbo.RoleAssignment INNER JOINdbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN dbo.Groups ON dbo.RoleAssignment.SiteId = dbo.Groups.SiteId AND dbo.RoleAssignment.PrincipalId = dbo.Groups.ID

 

You can also find interesting to read Different Database created with SharePoint

 

Copy From http://manish-sharepoint.blogspot.com/2008/06/some-important-tables-with-sharepoint.html

How to deal with orphaned SharePoint sites

Copy from http://www.cjvandyk.com/blog/Lists/Posts/Post.aspx?ID=299

 

“in my case do all the step below cannot resolved , once do the step detach and attach db problem resolved” > keep track the nice step from

Cornelius J. van Dyk

 

Whatever causes it, orphaned sites in SharePoint can be a very painful issue to deal with.  One way in which an orphaned site can occur is when a STSADM command it terminated during execution because of a drop of your remote desktop connection.

If you are working with STSADM, be sure to configure your Remote Desktop settings in order to keep disconnected sessions alive.  Failure to do so, WILL cause problems for you in the future.

The toughest orphaned site I've ever had to deal with occurred during such a case.  The site was being restored from a STSADM backup.  The server's Remote Desktop settings were not set to keep the session alive and when a blip in the wireless network cause the connection to be broken, Windows simply killed the session causing the site collection to become orphaned.  This was evident when the restore command was re-attempted:

stsadm -o restore -url http://server/site -filename site.stsadm.bak -overwrite

Which returned this puzzling message:

Another site already exists at http://server/site. Delete this site before attempting to create a new site with the same URL, choose a new URL, or create a new inclusion at the path you originally specified.

This is a puzzling message because... didn't we specify the "-overwrite" switch?  Why yes we did!  So what is going on here?
OK, OK.  So the site is there and the "-overwrite" switch isn't working.  Let's just delete the site.  The following command is issued:

stsadm -o deletesite -url http://server/site

Only thing is, the site collection delete statement failed with the following error:

The system cannot find the path specified. (Exception from HRESULT: 0x80070003)

No problem you say.  Just use the -force switch with the deletesite command.  Doing that, the following command is issued:

stsadm -o deletesite -force -siteid 2a9fbc10-4a60-427b-8e6e-e1565d7e5796 -databaseserver sql -databasename Site_Content

Unfortunately, the response is not what you expected.  This is the error returned:

Database "Site_Content" is not associated with this Web application

Of course, the message would make sense if the database was not associated with the web application, but it was.  It is time for the good old orphan identification and handling swich... databaserepair
We begin by identifying the orphaned content.  We issue the following command:

stsadm -o databaserepair -url http://server/site -databasename Site_Content

As expected, the command dumps a bunch of orphaned content.  Time to delete the corrupted content so we can restore the site again.  Simply add the -deletecorruption switch thus:

stsadm -o databaserepair -url http://server/site -databasename Site_Content -deletecorruption

Once the corrupted content has been deleted, rerunning the command without the -deletecorruption switch yields a clean bill of health for our database thus:

<OrphanedObjects Count="0" />

Unfortunately, retrying the restore still fails by with the same "site exists" error.  This is where I had the idea to detach and re-attach the database to try and clear up the issue.  Two quick STSADM commands later thus:

stsadm -o deletecontentdb -url http://server/site -databasename Site_Content

and

stsadm -o addcontentdb -url http://server/site -databasename Site_Content

yielded:

Operation completed successfully.

Now retrying the restore thus:

stsadm -o restore -url http://server/site -filename site.stsadm.bak -overwrite

finally yielded the result we were looking for:

Operation completed successfully.

Hopefully this will help you get sticky orphaned sites handled quickly so you can move on to more important things!

Later
C

Installing Microsoft SQL Server 2008 Reporting Services Add-On On a MOSS 2007 server

 

How to publish the SSRS to MOSS 2007 ? will it impact the MOSS 2007 Database?  So we get the advice from some expert as below :

 

By default, it will be pretty safe for us to integrate reporting service with MOSS. It will add some application pages to MOSS instance, kind of like add a feature to the farm. As I said in the phone, the impact it involve is it will install some application pages to the SharePoint site and report file (.RDL) will be saved in ContentDBs (The version of the backend database is not a matter). However, we only support part of combination of the version of reporting service, reporting service add-in and SharePoint.

 

Report Server	Add-in	SharePoint	Supported
SQL Server 2008 R2	SQL Server 2008 R2	SharePoint 2010 Products	Y
SQL Server 2008 R2	SQL Server 2008 SP2 (*)	SharePoint 2007 Products	Y
SQL Server 2008 SP1 Cumulative Update #8	SQL Server 2008 R2	SharePoint 2010 Products	Y
SQL Server 2008	SQL Server 2008	SharePoint 2010 Products	N
SQL Server 2008	SQL Server 2008	SharePoint 2007 Products	Y
SQL Server 2005 SP2	SQL Server 2005	SharePoint 2007 Products	Y

 

(*) It actually has been released and you could download it through http://www.microsoft.com/downloads/en/details.aspx?FamilyID=35c3a13f-4b5e-4d4a-86e4-07ee307b9638

 

As you could see, if your reporting service is SQL Server 2008 R2 and your SharePoint is 2007, there is no combination supported by us. More information about reporting service integration overview, you could refer to the following document.

 

Overview of Reporting Services and SharePoint Technology Integration

http://msdn.microsoft.com/en-us/library/bb326358(SQL.105).aspx

After installing Reporting Services Add-in, you could see the following feature in Central Admin in ‘Application Management’ page.

With these links you could configure where the report server is, what kind of authentication mode you want to use, etc.

You could also see the reporting in Site collection features page.