SharePoint – BLOB Statistics

Ever wanted to have a quick statistics on how many document types or size per document type you have in SharePoint.

Each Site collection storage has some default SQL Views and 1 of them is a view called Docs.

image

So we can use this to get insight into the BLOB storage statistics.

To get the Total Storage in a document library.

  
  select  Round(Sum(Cast(Size AS numeric )/(1024*1024)),2) as Size_GB
  
  from  [WSS_Content_Innovation].[dbo].[Docs]
  
  where ListId= '63B9C392-89E5-47CA-AEB7-1F0C4909A392' and IsCurrentVersion=1 and Type = 0

Or the number of extensions in a Site collection.

select ExtensionForFile as Extension,COUNT(*) as Count_Files
  
from  [WSS_Content_Innovation].[dbo].[Docs]
  
  where ListId= '63B9C392-89E5-47CA-AEB7-1F0C4909A392'
  and IsCurrentVersion=1 and TYPE = 0
  
  group by ExtensionForFile
  order by COUNT(*) desc

image

Or the total size per extension

select ExtensionForFile as Extension,COUNT(*) as Count_Files, CAST(Sum(Size)/1024 AS VARCHAR) + ' Kb' as Sum_FilesSize

from  [WSS_Content_Innovation].[dbo].[Docs]

  where ListId= '63B9C392-89E5-47CA-AEB7-1F0C4909A392'
  and IsCurrentVersion=1 and TYPE = 0

  group by ExtensionForFile
  order by  Sum(Size) desc

image

Advertisements

One Response to SharePoint – BLOB Statistics

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: