Determine space used for a table using SQL Server Management Studio

By:   |   Comments   |   Related: > SQL Server Management Studio


Recently I had to restore a copy of a production database to a test server in order for some, well, testing.  I had warned the Application Analyst Team for months that we were going to be in a space crunch because while the production database had been growing consistently over time, the test server that they had dedicated for this process was sized based upon an outdated estimate for growth.  In short, they had run out their options for using this test instance without first purging unnecessary data and then releasing space from the database back to the file system.  The Analyst provided me with the sole table that could be truncated and wanted to know if enough space would be regained to allow the database restore to continue as requested.  The following shows a quick way to identify the space usage for a particular table.


There are several options for providing this information quite easily using both T-SQL commands or via the Microsoft SQL Server Management Studio (SSMS) GUI.  In a previous tip, Determining space used for each table in a SQL Server database a method was presented for returning this information via T-SQL.  This option works quite well when you are:

  • In need of space consumption for multiple tables. 
  • You find that you're asked this question on a regular basis.

In these cases the T-SQL approach is great, in that you can create the query structure, encapsulate it within a stored procedure, and have it at the ready when called upon.  However, in situations like I outlined previously, you simply are looking to answer the question "How much space table "X" is using?"  The quickest approach, requiring no need to remember the syntax of a query, the creation of temp tables, or the structure of a particular system view or dynamic management view is to know where to go in SSMS to find the information.

To locate this information within SSMS, follow this simple process.  For this example we will go with a scenario that you've been asked to determine how much space is being consumed by the Orders table in the Northwind database.  Open Microsoft SQL Server Management Studio, connect to the SQL instance and database via the Object Explorer, expand the Tables node in tree view.  Doing so against Northwind would look similar to this:

object explorer

Once you have the listing of tables for the Northwind database displayed, right click on the dbo.Orders table and select Properties.

northwind database

You will be presented with the following information:

table properties

This form not only answers your question concerning space consumption for the table, but additionally it provides you with similar information for index space and row count.  Within a few mouse clicks you've been able to provide a quick answer back to your customer, making you look like the SQL Rockstar you are.  Knowing the code behind this process is important, but your users want answers quickly and with this method you give them want they want, probably quicker than they expect.  That is a Win-Win situation in its purest form.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms