We help IT Professionals succeed at work.

SQL Query for SharePoint

send2mark
send2mark asked
on
I'm looking for a query that will show my Site Collections with size. I tried select * from Sites and that will give me the size without the URL.  select * from webs where ParentWebID is NULL will show me the sub sites which would be fine too, but it doesn't show the size.
Comment
Watch Question

Jamie McAllisterCloud Expert
Most Valuable Expert 2013
Top Expert 2014

Commented:
It is not supported to directly query the Databases. Even a read query can introduce a lock that effects operation of the product.

Consider using this space monitoring tool instead:

http://www.thesug.org/blogs/lsuslinky/SSM/Pages/default.aspx

Commented:
it may probably be easier if you would run from command prompt on the SP sherver:
stsadm -o enumsites -url http://YOUR_ROOT_SITE_URL
This command will display a list of all the site collections in your environment, including the sizes.

Author

Commented:
irinuc, its a good idea, but I need to know which database they're in. That is why I am doing it in SQL. We have 14 Content databases and I am moving site collections because some of them are getting too big.

Commented:
What version of Share Point do you have? I think in MOSS, this command lists also the database where the site collection is located.
Let me double check that, to make sure; maybe you need to add another parameter to the command...
Commented:
Yep, this command should also display the content databases.

However, here is also the SQL query (remember it is not supported by MS):
select b.fullurl, a.diskused, a.secondstagediskused from sites a, webs b where a.id=b.siteid and a.rootwebid=b.id
(i never knew what secondstagediskused exactly means, but i believe it related to the site collection's second level recycle bin; just guessing...)