Find out the size of lists and webs for MOSS 2007 and SPS 2003

21/10/2008

There is a good site that gives you a number of ways of getting information directly from SQL server in here:

http://www.codeproject.com/KB/dotnet/QueriesToAnalyzeSPUsage.aspx

BUT, If you ever wanted to find out the size of a web via a SQL command, then try this:

I have based this script on the proc_GetDocLibrarySizes and proc_GetListSizes then combined them in a temp table to them group by url and size.

When you run this, supply the Site ID in the @SiteID variable

This is the MOS 2007 version

DECLARE @SiteId uniqueidentifier

SET @SiteId='d1f6c640-5a81-45ad-8b45-1b15e15699e2'

drop table #tmptblWebSize

CREATE TABLE #tmptblWebSize

(

FullUrl VARCHAR (500),

ListType VARCHAR (20),

TotalSize BIGINT,

VersionSize BIGINT

)

INSERT INTO #tmptblWebSize

(FullUrl, ListType, TotalSize, VersionSize)

SELECT

Webs.FullUrl, 'Doc Libs',

SUM((ISNULL(DocSizes,0) + ISNULL(UserDataSize,0))) As TotalSize,

VersionSize=0

FROM

Webs

INNER JOIN

(SELECT

Lists.tp_ItemCount,

Lists.tp_Title,

Lists.tp_Id,

Lists.tp_WebID,

Lists.tp_Modified,

Lists.tp_ServerTemplate,

Docs.DirName,

Docs.LeafName,

Lists.tp_ImageUrl

FROM

Lists

INNER JOIN

Docs

Lists.tp_RootFolder = Docs.Id AND

Lists.tp_WebId = Docs.WebId

WHERE tp_BaseType <> 1 AND

SiteId = @SiteId) As nLists

Webs.Id = nLists.tp_WebId

LEFT OUTER JOIN

(SELECT

(SUM(CAST((ISNULL(Docs.Size,0)) AS BIGINT))) As DocSizes,

Docs.ListId,

Docs.SiteId

FROM

Docs

WHERE Docs.Type = 0 AND SiteId = @SiteId

GROUP BY Docs.ListId,Docs.SiteId) As DocsInList

DocsInList.ListId = nLists.tp_ID

LEFT OUTER JOIN

(SELECT

(SUM(CAST((ISNULL(tp_Size,0)) AS BIGINT))) As UserDataSize,

tp_ListId

FROM

UserData

GROUP BY

UserData.tp_ListId) AS UserDataInList

UserDataInList.tp_ListId = DocsInList.ListId

GROUP BY Webs.FullUrl

INSERT INTO #tmptblWebSize

(FullUrl, ListType, TotalSize, VersionSize)

SELECT

Webs.FullUrl, 'Doc Libs',

SUM(ISNULL(DocSizes,0) + ISNULL(VerSizes,0) + ISNULL(PersonSizes,0) + ISNULL(WpSizes,0)) As TotalSize,

VersionSize = SUM(ISNULL(VerSizes,0))

FROM

Webs

INNER JOIN

(SELECT

Lists.tp_ItemCount,

Lists.tp_Title,

Lists.tp_Id,

Lists.tp_WebID,

Lists.tp_Modified,

Lists.tp_ServerTemplate,

Docs.DirName,

Docs.LeafName,

Lists.tp_ImageUrl

FROM

Lists

INNER JOIN

Docs

Lists.tp_RootFolder = Docs.Id AND

Lists.tp_WebId = Docs.WebId

WHERE tp_BaseType = 1 AND

SiteId = @SiteId) As nLists

Webs.Id = nLists.tp_WebId

LEFT OUTER JOIN

(SELECT

vq.VerSize As Versizes,

dq.DocSize As DocSizes,

dq.ListId As ListId

FROM

(SELECT

(SUM(CAST((ISNULL(Docs.Size, 0)) AS BIGINT))) AS DocSize,

Docs.ListId AS ListId

FROM

Docs

WHERE

Docs.SiteId = @SiteId AND

Docs.Type = 0

GROUP BY

Docs.ListId

) AS dq

LEFT OUTER JOIN

(SELECT

(SUM(CAST((ISNULL(DocVersions.Size, 0)) AS BIGINT))) AS VerSize,

Docs.ListId AS ListId

FROM

DocVersions

INNER JOIN

Docs

Docs.SiteId = @SiteId AND

Docs.Id = DocVersions.Id AND

DocVersions.SiteId = @SiteId

GROUP BY

Docs.ListId

) AS vq

vq.ListId = dq.ListId

) AS DocsInList

DocsInList.ListId = nLists.tp_ID

LEFT OUTER JOIN

(SELECT

(SUM(CAST((ISNULL(Personalization.tp_Size,0)) AS BIGINT)))

As PersonSizes,

(SUM(CAST((ISNULL(WebParts.tp_Size,0)) AS BIGINT))) As WpSizes,

WebParts.tp_ListId

FROM

WebParts

LEFT OUTER JOIN

Personalization

Personalization.tp_WebPartId = WebParts.tp_ID

GROUP BY WebParts.tp_ListId) As WebPartsInList

DocsInList.ListId = WebPartsInList.tp_ListId

GROUP BY Webs.FullUrl

select fullurl, sum(totalsize) as WebSize, VersionSize = sum(VersionSize)

from #tmptblWebSize

GROUP BY fullurl

This is the 2003 version

DECLARE @SiteId uniqueidentifier
SET @SiteId='E337D8F3-ED87-4CE5-A3CD-37287C4BB342'

drop table #tmptblWebSize

CREATE TABLE #tmptblWebSize
(
FullUrl VARCHAR (500),
ListType VARCHAR (20),
TotalSize BIGINT,
VersionSize BIGINT
)

INSERT INTO #tmptblWebSize
(FullUrl, ListType, TotalSize, VersionSize)
SELECT Webs.FullUrl, 'Lists',
SUM((ISNULL(DocSizes,0) + ISNULL(UserDataSize,0))) As TotalSize, 0
FROM
Webs
INNER JOIN
    (SELECT
    Lists.tp_ItemCount,
    Lists.tp_Title,
    Lists.tp_Id,
    Lists.tp_WebID,
    Lists.tp_Modified,
    Lists.tp_ServerTemplate,
    Docs.DirName,
    Docs.LeafName,
    Lists.tp_ImageUrl
    FROM
    Lists
    INNER JOIN
    Docs
    ON
    Lists.tp_RootFolder = Docs.Id AND
    Lists.tp_WebId = Docs.WebId
    WHERE tp_BaseType <> 1 AND
          SiteId = @SiteId) As nLists
    ON
    Webs.Id = nLists.tp_WebId
    LEFT OUTER JOIN
        (SELECT
        (SUM(CAST((ISNULL(Docs.Size,0)) AS BIGINT))) As DocSizes,
        Docs.ListId,
        Docs.SiteId
        FROM
        Docs
        WHERE Docs.Type = 0 AND SiteId = @SiteId
        GROUP BY Docs.ListId,Docs.SiteId) As DocsInList
        ON
        DocsInList.ListId = nLists.tp_ID
        LEFT OUTER JOIN
            (SELECT
            (SUM(CAST((ISNULL(tp_Size,0)) AS BIGINT))) As UserDataSize,
            tp_ListId
            FROM
            UserData
            GROUP BY
            UserData.tp_ListId) AS UserDataInList
            ON
            UserDataInList.tp_ListId = DocsInList.ListId
GROUP BY Webs.FullUrl

INSERT INTO #tmptblWebSize
(FullUrl, ListType, TotalSize, VersionSize)
SELECT Webs.FullUrl, 'Doc Libs',
SUM(ISNULL(DocSizes,0) + ISNULL(VerSizes,0) + ISNULL(PersonSizes,0) + ISNULL(WpSizes,0)) As TotalSize,
VersionSize = SUM(ISNULL(VerSizes,0))
FROM
Webs
INNER JOIN
    (SELECT
    Lists.tp_ItemCount,
    Lists.tp_Title,
    Lists.tp_Id,
    Lists.tp_WebID,
    Lists.tp_Modified,
    Lists.tp_ServerTemplate,
    Docs.DirName,
    Docs.LeafName,
    Lists.tp_ImageUrl
    FROM
    Lists
    INNER JOIN
        Docs
        ON
        Lists.tp_RootFolder = Docs.Id AND
        Lists.tp_WebId = Docs.WebId
    WHERE   tp_BaseType = 1 AND
        SiteId = @SiteId) As nLists
    ON
    Webs.Id = nLists.tp_WebId
    LEFT OUTER JOIN
        (SELECT
            vq.VerSize As Versizes,
            dq.DocSize As DocSizes,
            dq.ListId As ListId
        FROM
            (SELECT
                (SUM(CAST((ISNULL(Docs.Size, 0)) AS BIGINT))) AS DocSize,
                Docs.ListId AS ListId
            FROM
                Docs
            WHERE
                Docs.SiteId = @SiteId AND
                Docs.Type = 0
            GROUP BY
                Docs.ListId
            ) AS dq
        LEFT OUTER JOIN
            (SELECT
                (SUM(CAST((ISNULL(DocVersions.Size, 0)) AS BIGINT))) AS VerSize,
                Docs.ListId AS ListId
            FROM
                DocVersions
            INNER JOIN
                Docs
            ON
                Docs.SiteId = @SiteId AND
                Docs.Id = DocVersions.Id AND
                DocVersions.SiteId = @SiteId
            GROUP BY
                Docs.ListId
            ) AS vq
        ON
            vq.ListId = dq.ListId
        ) AS DocsInList
        ON
        DocsInList.ListId = nLists.tp_ID
        LEFT OUTER JOIN
            (SELECT
            (SUM(CAST((ISNULL(Personalization.tp_Size,0)) AS BIGINT)))
                As PersonSizes,
            (SUM(CAST((ISNULL(WebParts.tp_Size,0)) AS BIGINT))) As WpSizes,
            WebParts.tp_ListId
            FROM
            WebParts
            LEFT OUTER JOIN
                Personalization
                ON
                Personalization.tp_WebPartId = WebParts.tp_ID
            GROUP BY WebParts.tp_ListId) As WebPartsInList
            ON
            DocsInList.ListId = WebPartsInList.tp_ListId
GROUP BY Webs.FullUrl

select fullurl, sum(totalsize) as WebSize, VersionSize = sum(VersionSize)
from #tmptblWebSize
GROUP BY fullurl

----

This is very valuable for migrations and monitoring.

Posted at 2:32 PM by Sergio Otoya | Permalink | Email this Post | Comments (0)

Comments

There are no comments yet for this post.

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.

Title

Body *

CommentUrl

Attachments