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


Custom icons for list definition using XML

Recently I had to specify custom icons for the list definition I have created using XML definition in visual studio. Solution is quite easy to implement – just specify your icon path in the XML property of list definition

<Elements xmlns="">

When insert image in a such way you should know that this image will be used on:

  • Site contents page. The size of the icon should be 96×96 px.
  • Content and structure where size of the icon should be 16×16 px.

However, when icon specified in list definition is large one it is also shown as large one in “Content and structure” where it should be 16×16 px.


To solve this you should prepare three sets of images. SharePoint manages itself which image to display. However you should have right naming convention to make it work.

  • Small image (16×16 px) – name should starting with ITImageName
  • Medium image (32×32 px) – name should starting with MGSameImageName
  • Large image (96×96 px) – name should starting with LTSameImageName


  • Name of the icon you try to show should be the same for all three images and only prefix should change.
  • These icons should be deployed in /_layouts/15/images folder (/_layouts/14/images for SP2010). You can have another level Inside this folder to isolate your project’s icons.
  • Name of your icon SHOULD NOT start with on of the following letters: L, M, I because these ones are used for prefix.
  • Icon path specified in the list definition XML file should be small icon path. SharePoint will automatically pick up right size icon when displayed on page.

Here is how it should look like:

Custom icons for list definition

Content and structure

Andrew Adamich