Enumerating items counts in lists

1. Recently, we found ourselves in a situation where we suspected that the customer had exceeded our capacity planning guidelines for the number of items in a document library; however, how do we go about verifying that? Well, the steps below will walk you through how to enumerate the counts for the different folders. You have the ability to set a threshold value and report on everything that exceeds that threshold.

2. Enjoy!

3. Connect to the SQL server via Microsoft SQL Server Management Studio (SQL 2005) or Query Analyzer (SQL 2000)

   

4. Once connected, open a query window

   

5. Once you have a query window open, make sure the master database is selected:

   

6. Once you have a query window open, paste the following SQL query:

      1: create table ##Largelists
   

     2: (
   

     3:     [listID] uniqueidentifier NULL,
   

     4:     [siteID] uniqueidentifier null,
   

     5:     [webid] uniqueidentifier null,
   

     6:     [counts] bigint NULL,
   

     7:     [fullurl] nvarchar(255) NULL,
   

     8:     [dirname] nvarchar(255) null,
   

     9:     [tp_title] nvarchar(255) NULL,
   

    10:     [servername] nvarchar(100) null,
   

    11:     [content_DB] nvarchar(100) null
   

    12: )
   

    13: 
   

    14: go
   

    15: CREATE TABLE #DBNamesLL
   

    16: (
   

    17:     DatabaseName VARCHAR(800),
   

    18:     RecStatus INT Default 0
   

    19: )
   

    20: 
   

    21: DECLARE @cmdStr NVARCHAR(2000)
   

    22: DECLARE @dbName VARCHAR(500)
   

    23: INSERT INTO #DBNamesll (DatabaseName)
   

    24: SELECT 
   

    25:     [Name] 
   

    26: FROM 
   

    27:     sys.databases 
   

    28: WHERE 
   

    29:     state_desc = 'online'
   

    30: ORDER BY 
   

    31:     [Name] ASC
   

    32: 
   

    33: WHILE EXISTS 
   

    34:     (SELECT 
   

    35:         * 
   

    36:      FROM 
   

    37:         #DBNamesLL 
   

    38:      WHERE 
   

    39:         RecStatus=0
   

    40:     )
   

    41: 
   

    42: BEGIN
   

    43:     SELECT TOP 1 
   

    44:         @DbName = DatabaseName
   

    45:     FROM 
   

    46:         #DBNamesLL
   

    47:     WHERE 
   

    48:         RecStatus = 0
   

    49:     
   

    50:     SELECT @cmdStr = N'USE ' + quotename(@dbName, '[') + N';'
   

    51:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES with (NOLOCK) WHERE TABLE_NAME = ''namevaluepair'')'
   

    52:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'BEGIN'
   

    53:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'INSERT INTO ##largelists'
   

    54:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'select a.siteid, a.webid, a.listid, count(a.id) [counts], c.fullurl, a.dirname, b.tp_title,'
   

    55:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + '(@@servername) [servername], (select db_name(dbid) from master..sysprocesses with (NOLOCK) where spid=@@SPID) [Content_DB]'
   

    56:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'from alldocs as a with (nolock) inner join'
   

    57:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'alllists as b with (nolock) on a.listid=b.tp_id inner join'
   

    58:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'webs as c with (nolock) on b.tp_webid=c.id'
   

    59:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'group by a.listid, c.fullurl, b.tp_title, a.siteid, a.webid, a.dirname'
   

    60:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'HAVING count(a.id) > ''1500'''
   

    61:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'END'
   

    62:     EXEC sp_executesql @Cmdstr
   

    63: 
   

    64:     UPDATE 
   

    65:         #DBNamesLL
   

    66:     SET 
   

    67:         RecStatus = 1
   

    68:     WHERE 
   

    69:         RecStatus = 0 AND 
   

    70:         DatabaseName = @DbName
   

    71: END
   

    72: 
   

    73: SELECT 
   

    74:     WebID, 
   

    75:     SiteID, 
   

    76:     ListID, 
   

    77:     tp_Title, 
   

    78:     DirName, 
   

    79:     Counts, 
   

    80:     content_DB, 
   

    81:     servername 
   

    82: FROM 
   

    83:     ##largelists 
   

    84: ORDER BY 
   

    85:     counts DESC
   

    86: 
   

    87: DROP TABLE #DBNamesLL
   

    88: DROP TABLE ##largelists
   

7. In my OOB configuration, I changed the 1500 value to 300 in order to get some results. Here is what I get:

   

   This indicates that I have 831 list items in the HelpFold/MetaData/1033/MS_OSS folder. It has an associated WebID, SiteID, and ListID shown. There is additional information to the right that identifies the server and the content database where the list is located.

8. After we have verified that data will actually be returned, we need to save the results to a file in order to get them back to Microsoft. The easiest way to do that is to send the query results to a file.

   

9. Now, execute the query again by clicking on the Execute button

   

10. When you execute the query, a Save Results dialog will pop up requesting a location for you to save the query results. Specify one that is easy for you to remember:

    

11. Now, send the results file (SingleServerLargestFileQuery.rpt) to Microsoft for analysis.