Query Sharepoint Content DB for Doc Library Sizes and Number of versions


SELECT v.[id],

       d.dirname,

       Count(v.[version]) MaxNumberOfVersions,

       Sum(v.[size])      TotalDocLibSize,

       Max(v.[size])      LargestDocSize

FROM   [dbo].[alldocversions] v

       JOIN [dbo].alldocs d

         ON v.id = d.id

GROUP  BY v.id,

          d.dirname

ORDER  BY 4 DESC 

SQL Server Tables ordered by Size


SELECT o.name,

       reservedpages = Sum(a.total_pages),

       usedpages = Sum(a.used_pages),

       reservedMB = ( Sum(a.total_pages) * 8 / 1024 ),

       usedMB = ( Sum(a.used_pages) * 8 / 1024 ),

       pages = Sum(CASE

                     WHEN a.type <> 1 THEN a.used_pages

                     WHEN p.index_id < 2 THEN a.data_pages

                     ELSE 0

                   END),

       rows = Sum(CASE

                    WHEN ( p.index_id < 2 )

                         AND ( a.type = 1 ) THEN p.rows

                    ELSE 0

                  END)

FROM   sys.objects o

       JOIN sys.partitions p

         ON p.object_id = o.object_id

       JOIN sys.allocation_units a

         ON p.partition_id = a.container_id

WHERE  o.type = ‘U’

GROUP  BY o.name

ORDER  BY 3 DESC