Some useful SharePoint 2010 Stats for Capacity Planning

SQL statements below can be used to gather the trending on the space usage by SharePoint sites.

SELECT distinct
CONVERT(CHAR(4), [TimeCreated], 120) As [Year],
DATEPART(month,[TimeCreated]) As [Month],
CONVERT(CHAR(4), [TimeCreated], 100) + CONVERT(CHAR(4), [TimeCreated], 120) as [CreatedMonth],
COUNT(*) as [DocumentedCreated],
SUM(isnull(cast(Size as bigint),0))/1024/1024 As TotalMegaBytes
from <content db name>.dbo.AllDocs D With (NOLOCK)
where [TimeCreated] > '2010-1-1'
group by CONVERT(CHAR(4), [TimeCreated], 120), DATEPART(month,[TimeCreated]),CONVERT(CHAR(4), [TimeCreated], 100) + CONVERT(CHAR(4), [TimeCreated], 120)
order by [Year], [MONTH],[CreatedMonth] asc

SELECT distinct
CONVERT(CHAR(4), [TimeCreated], 120) As [Year],
DATEPART(month,[TimeCreated]) As [Month],
CONVERT(CHAR(4), [TimeCreated], 100) + CONVERT(CHAR(4), [TimeCreated], 120) as [CreatedMonth],
COUNT(*) as [SitesCreated],
SUM(isnull(cast(DiskUsed as bigint),0))/1024/1024 As TotalMegaBytes
from <conten db name>.dbo.Sites D With (NOLOCK)
where [TimeCreated] > '2010-1-1'
group by CONVERT(CHAR(4), [TimeCreated], 120), DATEPART(month,[TimeCreated]),CONVERT(CHAR(4), [TimeCreated], 100) + CONVERT(CHAR(4), [TimeCreated], 120)
order by [Year], [MONTH],[CreatedMonth] asc

 

SharePoint 2010 SQL Queries To Get Largest Documents

Below are queries that we can use to list down the top 100 largest documents in a SharePoint 2010 content DB

1. Query to list 100 largest document

Select top 100 D.DirName, D.LeafName, D.Size
From <content db name>.dbo.AllDocs D With (NOLOCK)
Order by D.Size desc

2. If we need to filter based on a specific site collections, we can use query below

Select top 100 D.DirName, D.LeafName, D.Size
from <content db name>.dbo.AllDocs D With (NOLOCK)
join <content db name>.dbo.Webs W With (NOLOCK)
on D.SiteId=W.SiteId
where W.FullUrl='<url>'
order by D.Size Desc

3. To further filter in a specific file extension (in this case its avi), we can use the query below

select top 100 D.DirName, D.LeafName, D.Size
from <content db name>.dbo.AllDocs D With (NOLOCK)
join <content db name>.dbo.Webs W With (NOLOCK)
on D.SiteId=W.SiteId
where W.FullUrl='<url>'
and D.Extension = 'avi'
order by D.Size Desc

 

4. Total document size. You need to cast as bigint , otherwise most likely to get  arithmetic overflow

select D.Extension, SUM(CAST(D.Size as BIGINT)) AS [Total Size]
<content db name>.dbo.AllDocs D With (NOLOCK)
group by D.Extension
order by [Total Size] desc