SharePoint 2010 Performance Dashboard - Farm Content Size Reporting(Post 2/2)

 

Here I have mentioned the code which can be useful to get the Content DB, Site Collection Usage details.

 

 SPFarm farm                 = SPFarm.Local;
 SPWebService service        = farm.Services.GetValue<SPWebService>("");
  
 string webappName       = "";
 SPWeb web               = null;
 SPUserCollection owners = null;
 string OwnerString      = "";
 int count               = 0;
  
 foreach (SPWebApplication webapp in service.WebApplications)
 {
     webappName                                  = webapp.Name.ToString();
     SPContentDatabaseCollection DBCollection    = webapp.ContentDatabases;
  
     foreach (SPContentDatabase contentDB in DBCollection)
     {
         //ContentDB details 
         row = null;
         row = tableDB.NewRow();
  
         row[col1] = webappName;
         row[col2] = contentDB.Name.ToString();
  
         
             //Get the ContentDB size from farm database server
             SqlConnection con   = new SqlConnection(contentDB.DatabaseConnectionString.ToString());
             SqlCommand com      = new SqlCommand("select Sum(size/128) from sys.database_files", con);
             try
             {
                 con.Open();
                 row[col3] = Convert.ToInt64(com.ExecuteScalar());
                 con.Close();
             }
             catch (Exception ex)
             {
                 WritetoEventViewer("Exception(ContentDB size calculation from farm DB) : " + ex.Message.ToString());
             }
             finally
             {
                 con.Dispose();
             }
  
         row[col4] = Convert.ToInt64(contentDB.CurrentSiteCount);
         row[col5] = Environment.MachineName;
         tableDB.Rows.Add(row);
  
         //Site collection details
         foreach (SPSite site in contentDB.Sites)
         {
             try
             {
                 rowSite = null;
                 rowSite = tableSite.NewRow();
  
                 rowSite[Server]         = Environment.MachineName;
                 rowSite[WebApp]         = webappName;
                 rowSite[ContentDBName]  = contentDB.Name.ToString();
                 rowSite[URL]            = site.Url.ToString();
                 rowSite[Size]           = site.Usage.Storage; // in bytes
                 rowSite[Quota]          = site.Quota.StorageMaximumLevel; // in bytes
  
                     //Get the Users from site collection's owner group
                     OwnerString = "";
                     web         = site.RootWeb;
  
                     if (web.AssociatedOwnerGroup != null)
                         owners = web.AssociatedOwnerGroup.Users;
  
                     if ((owners != null) && (owners.Count > 0))
                     {
                         count = 0;
                         foreach (SPUser user in owners)
                         {
                             string loginname = user.LoginName.ToString();
                             
                             //To remove extra characters from claims
                             if(loginname.IndexOf("i:0#.w|") >= 0)
                                 loginname = loginname.Replace("i:0#.w|", "");
  
                             if (count == 0)
                             {
                                 OwnerString = loginname;
                                 count++;
                             }
                             else
                             {
                                 count++;
                                 OwnerString = OwnerString + "; " + loginname;
                             }
                         }
                     }
  
                 rowSite[Owner]          = OwnerString;
                 rowSite[ReadLocked]     = site.ReadLocked;
                 rowSite[ReadOnly]       = site.ReadOnly;
                 rowSite[CurrentResourceUsage] = site.CurrentResourceUsage;
                 rowSite[WriteLocked]    = site.WriteLocked;
                 rowSite[UsageBandwidth] = site.Usage.Bandwidth;
                 rowSite[UsageHits]      = site.Usage.Hits;
                 rowSite[UsageVisits]    = site.Usage.Visits;
  
                 tableSite.Rows.Add(rowSite);
             }
             catch (Exception ex)
             {
                 WritetoEventViewer("Exception(Site Collection details collection) : " + ex.Message.ToString());
             }
             finally
             {
                 owners = null;
  
                 if (web != null)
                     web.Dispose();
  
                 if (site != null)
                     site.Dispose();
             }
         }
     }
 }