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