SharePoint 2010 database size

Recently had issue with database size increasing very fast. To find out what takes so much place inside SharePoint database do following actions:

  • Inside SQL Server Management Studio find content database
  • Right click on databse -> Reports -> Standard Reports -> Disk Usage by Top Tables

This will report you in desc order which tables takes much space.

In my case I had table AllUserData that took me 4,5GB of data. In this table all list items are stored. To find out by list you should query database.

Note, this is forbinden to edit fields inside SharePoint databse. This is on your own risk.

In our case we will just query without editing fields.

Take all lists using following query:

FROM [WSS_Content_DB].[dbo].[AllLists]
order by [tp_LastSecurityChange] desc

This will return you all list ordered by change last date.

To get number of items for this list use following query (this can be slow depeding on your list size):

FROM [WSS_Content_DB].[dbo].[AllUserData]
where [tp_ListId] > ‘A6C61333-BC89-49B9-A8FA-D5210FDE9FAD’

Where  [tp_ListId] is a [tp_ID] field value of [AllLists] table.

You can do revert operation by getting list ID in items table and find what the list is.

Andrew Adamich