How To Get SQL Server Database Size In C#

Introduction

This article shows how we get the size of a database using C#. We will also learn how to get the size of all database tables using a single SQL command. To explain this, the article is divided into three sections,

1. Getting the Size of one Table in the Database using single SQL Command
2. Getting the Size of each Table in the Database using single SQL Command
3. Getting the Size of the entire Database using C#

Getting the Size of one Table in the Database using single SQL Command

To get the table size in SQL Server, we need to use the system-stored procedure sp_spaceused. Suppose we use Table Name as an argument. In that case, it gives the disk space used by the Table and some other information like the number of rows existing in the Table, the Total amount of reserved space for the Table, the Total amount of space reserved for the Table but not yet used and Total amount of space used by indexes in Table.

Example 

For the ADDRESS table in our Database, if we run,

sp_spaceused 'TADDRS'

It will give the following result,

Getting the size of each Table in the Database using single SQL Command

We have seen how we can determine the size of one Table. Suppose we want to choose the size of each Table in the entire Database. We could find any table size using this command by changing the Table name in the parameter. Is that right? But would it not be much better if we have a one-line SQL command that gives the size of each Table? Right?

Fortunately, SQL Server provides a way to do this. A stored procedure "sp_MSforeachtable" could do easily for us!

The sp_MSforeachtable stored procedure is one of many undocumented stored procedures tucked away in the depths of SQL Server. 

sp_MSforeachtable is an undocumented stored procedure that is not listed in MSDN Books Online and can be used to run a query against each Table in the Database. In short, you can use this as,

EXEC sp_MSforeachtable @command1="command to run."

In the "command to run," put a "?" where you want the table name inserted. For example, to run the sp_spaceused stored procedure for each Table in the Database, we'd use,

It will give the size of each Table (including other details) like,

Picture 1 - Query Result

Getting the Size of the entire Database

We have seen in the previous section how we can get the size of each Table in the Database. Now we want to get the total size a database uses, i.e., the additional space used by each Table in the Database. Here is a sample code in C# that can calculate the entire database's size.

In this, we are executing the same command we discussed in the above section and are querying the Database using simple ADO.Net. We get the result in a DataSet and then iterate each Table to get its size. The table size is stored in the "data" column of each Table (see the above Picture-1). We add each Table's "data" column value to get the entire Database's size. The sample code is also attached to this article.

class MemorySizeCalculator  
{  
  public void GetDbSize()  
  {  
    int sum = 0;  
   
 // Database Connection String   
 string sConnectionString = "Server = .; Integrated Security = true; database = HKS";  
   
  // SQL Command [Same command discussed in section-B of this article]  
  string sSqlquery = "EXEC sp_MSforeachtable @command1=\"EXEC sp_spaceused '?'\" ";  
   
  DataSet oDataSet = new DataSet();  
   
  // Executing SQL Command using ADO.Net  
  using (SqlConnection oConn = new SqlConnection(sConnectionString))  
  {  
         oConn.Open();  
         using (SqlCommand oCmdGetData = new SqlCommand(sSqlquery, oConn))  
         {  
              oCmdGetData.ExecuteNonQuery();  
              SqlDataAdapter executeAdapter = new SqlDataAdapter(oCmdGetData);  
              executeAdapter.Fill(oDataSet);  
         }  
         oConn.Close();  
  }  
  // Iterating each table  
  for (int i = 0; i < oDataSet.Tables.Count; i++)  
  {  
    // We want to add only "data" column value of each table  
    sum = sum + Convert.ToInt32(oDataSet.Tables[i].Rows[0]["data"].ToString().Replace    
    ("KB", "").Trim());  
  }  
    Console.WriteLine("Total size of the database is : " + sum + " KB");  
 }  
}  

Conclusion

This article shows how we can determine the size of an entire database using C# and the size of every table in the database using a single SQL command.