Friday, July 12, 2013

SharePoint - List size

It is not recommended (or even allowed) to query SharePoint Content database. Microsoft strongly advises you not to do so. But, this is the easiest and the quickest way to get to information you need.

I've created this simple SQL query which returns size of every list in your SP Content database in MB and the number of items of that list:

NOTE: This SQL query refers to SharePoint 2007 and 2010.

USE [WSS_Content]
      SUM([dbo].[Docs].[Size]/1024 + [dbo].[Docs].[MetaInfoSize]/1024)/1024 AS SizeMB,
      COUNT(*) as NumberOfFiles
FROM [WSS_Content].[dbo].[Docs]
INNER JOIN [WSS_Content].[dbo].[Webs] ON [dbo].[Webs].[Id] = [dbo].[Docs].[WebId]
INNER JOIN [WSS_Content].[dbo].[Lists] ON [dbo].[Lists].[tp_ID] = [dbo].[Docs].[ListId]
WHERE [dbo].[Docs].[Size] > 0   
GROUP BY [dbo].[Lists].[tp_Title]

The results will look something like this: