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
No comments:
Post a Comment