Freigeben über

Enumerating items counts in lists

  • Artikel

  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)

    clip_image001

  4. Once connected, open a query window

    clip_image002

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

    clip_image003

  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:

    clip_image004

    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.

    clip_image005

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

    clip_image006

  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:

    clip_image007

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

Comments