Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Determine space used for a table using SQL Server Management Studio


By:   |   Read Comments   |   Related Tips: More > SQL Server Management Studio

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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:

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools