SharePoint Content Database Sizing

Posted on 11/1/2013 @ 6:36 AM in #SharePoint by | Feedback | 1955 views

SharePoint stores majority of its content in SQL Server databases. Many of these databases are concerned with the overall configuration of the system, or managed services support. However, a majority of these databases are those that accept uploaded content, or collaborative content. These databases need to be sized with various factors in mind, such as,

  • Ability to backup/restore the content quickly, thereby allowing for quicker SLAs and isolation in event of database failure.
  • SharePoint as a system avoids SQL transactions in many instances. It does so to avoid locks, but does so at the cost of resultant orphan data or possible data corruption. Larger databases are known to have more orphan items than smaller ones. Also smaller databases keep the problems isolated.

As a result, it is very important for any project to estimate content database base sizing estimation. This is especially important in collaborative document centric projects. Not doing this upfront planning can lead to significant manageability, performance and reliability issues.

As a rough rule of thumb, you should use the below formula to size a content database,

Database size = ((D × V) × S) + (10 KB × (L + (V × D)))

Where,

- D = expected number of documents,

- V = expected number of versions

- S = average size of the document

- L = number of list items, and 10KB is the expected metadata on each document.

Additionally, it is recommended that no content database be more than 100GB in size (online docs say 200GB which IMO is too big). If your site collections are quite small, it is recommended that you should have no more than 50 site collections per database.

To check the current size allocations if your various databases, you can run the below script.

   1:  ------------------------------Data file size---------------------------- 
   2:  if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%') 
   3:  drop table #dbsize 
   4:  create table #dbsize 
   5:  (Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0)) 
   6:  go 
   7:    
   8:  insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) 
   9:  exec sp_msforeachdb 
  10:  'use [?]; 
  11:    select DB_NAME() AS DbName, 
  12:      CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,  
  13:      CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),  
  14:  sum(size)/128.0 AS File_Size_MB, 
  15:  sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, 
  16:  SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB  
  17:  from sys.database_files  where type=0 group by type'   
  18:  go 
  19:    
  20:  -------------------log size-------------------------------------- 
  21:    if exists (select * from tempdb.sys.all_objects where name like '#logsize%') 
  22:  drop table #logsize 
  23:  create table #logsize 
  24:  (Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0)) 
  25:  go 
  26:    
  27:  insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) 
  28:  exec sp_msforeachdb 
  29:  'use [?]; 
  30:    select DB_NAME() AS DbName, 
  31:  sum(size)/128.0 AS Log_File_Size_MB, 
  32:  sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB, 
  33:  SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB  
  34:  from sys.database_files  where type=1 group by type' 
  35:  go 
  36:  --------------------------------database free size 
  37:    if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%') 
  38:  drop table #dbfreesize 
  39:  create table #dbfreesize 
  40:  (name sysname, 
  41:  database_size varchar(50), 
  42:  Freespace varchar(50)default (0.00)) 
  43:    
  44:  insert into #dbfreesize(name,database_size,Freespace) 
  45:  exec sp_msforeachdb 
  46:  'use [?];SELECT database_name = db_name() 
  47:      ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'') 
  48:      ,''unallocated space'' = ltrim(str(( 
  49:                  CASE  
  50:                      WHEN dbsize >= reservedpages 
  51:                          THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 
  52:                      ELSE 0 
  53:                      END 
  54:                  ), 15, 2) + '' MB'') 
  55:  FROM ( 
  56:      SELECT dbsize = sum(convert(BIGINT, CASE  
  57:                      WHEN type = 0 
  58:                          THEN size 
  59:                      ELSE 0 
  60:                      END)) 
  61:          ,logsize = sum(convert(BIGINT, CASE  
  62:                      WHEN type <> 0 
  63:                          THEN size 
  64:                      ELSE 0 
  65:                      END)) 
  66:      FROM sys.database_files 
  67:  ) AS files 
  68:  ,( 
  69:      SELECT reservedpages = sum(a.total_pages) 
  70:          ,usedpages = sum(a.used_pages) 
  71:          ,pages = sum(CASE  
  72:                  WHEN it.internal_type IN ( 
  73:                          202 
  74:                          ,204 
  75:                          ,211 
  76:                          ,212 
  77:                          ,213 
  78:                          ,214 
  79:                          ,215 
  80:                          ,216 
  81:                          ) 
  82:                      THEN 0 
  83:                  WHEN a.type <> 1 
  84:                      THEN a.used_pages 
  85:                  WHEN p.index_id < 2 
  86:                      THEN a.data_pages 
  87:                  ELSE 0 
  88:                  END) 
  89:      FROM sys.partitions p 
  90:      INNER JOIN sys.allocation_units a 
  91:          ON p.partition_id = a.container_id 
  92:      LEFT JOIN sys.internal_tables it 
  93:          ON p.object_id = it.object_id 
  94:  ) AS partitions' 
  95:  ----------------------------------- 
  96:    
  97:    
  98:    
  99:  if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%') 
 100:  drop table #alldbstate  
 101:  create table #alldbstate  
 102:  (dbname sysname, 
 103:  DBstatus varchar(55), 
 104:  R_model Varchar(30)) 
 105:     
 106:  --select * from sys.master_files 
 107:    
 108:  insert into #alldbstate (dbname,DBstatus,R_model) 
 109:  select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases 
 110:  --select * from #dbsize 
 111:    
 112:  insert into #dbsize(Dbname,dbstatus,Recovery_Model) 
 113:  select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online' 
 114:    
 115:  insert into #logsize(Dbname) 
 116:  select dbname from #alldbstate where DBstatus <> 'online' 
 117:    
 118:  insert into #dbfreesize(name) 
 119:  select dbname from #alldbstate where DBstatus <> 'online' 
 120:    
 121:  select  
 122:  d.Dbname,d.dbstatus,d.Recovery_Model, 
 123:  (file_size_mb + log_file_size_mb) as DBsize, 
 124:  d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, 
 125:  l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace 
 126:  from #dbsize d join #logsize l  
 127:  on d.Dbname=l.Dbname join #dbfreesize fs  
 128:  on d.Dbname=fs.name 
 129:  order by Dbname 

 

(script credit)

Sound off but keep it civil:

Older comments..