Determine space used for a table using SQL Server Management Studio

By: Tim Ford   |   Updated: 2008-09-26   |   Comments   |   Related: > SQL Server Management Studio

Problem

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.

Solution

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:

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

You will be presented with the following information:

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

• Review a previous tip from MSSQLtips.com on using T-SQL to accomplish this task against multiple tables.
• Learn more about what the SQL Server Management Studio GUI provides in these other tips
  • Scripting out T-SQL commands generated by SQL Server Management Studio
  • Options for scripting SQL Server database objects
  • Building and customizing SQL Server script templates
  • Insert data from Excel to SQL Server 2005 by using copy and paste commands
  • Dynamic stored procedure execution form in SQL Server 2005 Management Studio

About the author

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