When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
Technology Jobs
ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
Print this page.
Published: Wednesday, March 29, 2006

Displaying the Sizes of Your SQL Server's Database's Tables

By Scott Mitchell


Introduction
In my day-to-day work as a writer, trainer, and consultant, I usually wear the ASP.NET developer hat, focusing on designing the middle-tier and front-end portions of ASP.NET applications with my exposure to the database usually being limited to data modeling and writing queries/stored procedures to access the data. The typical DBA activities - creating indexes, doing consistency checks, backing up the database, and so on - have always been someone else's responsibility. Over the past several months, however, I've been taking a more active role in the DBA side of things for a decent sized database, one with over 200 tables, close to 500 stored procedures, and chewing through a few gigabytes of disk space.

With these new responsibilities, I've been building up my arsenal of utility pages and helper programs for this role. For example, I have a program that periodically runs the SQL Profiler on the database for intervals of 10 minutes storing the results into a trace file that is later loaded to an offline database and denormalized for parsing and reporting on various database performance metrics. (Perhaps I'll write about this process in a future article.) Another utility that I use is a web page that quickly shows a summary of the tables in the database - namely the number of rows in the table and the size on disk the table and its indexes consume. The following shows the output when running this script against the Northwind database.

NameRowsData SizeIndex Size
Categories8112 KB32 KB
CustomerCustomerDemo00 KB0 KB
CustomerDemographics00 KB0 KB
Customers9124 KB80 KB
Employees9232 KB48 KB
EmployeeTerritories498 KB24 KB
Order Details2,15572 KB208 KB
Orders830160 KB320 KB
Products778 KB96 KB
Region48 KB24 KB
Shippers38 KB16 KB
Suppliers2924 KB48 KB
Territories538 KB24 KB

The techniques I use work both in SQL Server 2000 and SQL Server 2005, and have been used in both ASP.NET 1.x and 2.0 pages. In this article we'll look at two ways to grab this information from SQL Server and how to display it in a web page. Read on to learn more!

- continued -

'

Determining the Space Used by a Database Table
SQL Server has a handy little system stored procedure named sp_spaceused that will return the space used by a database or by a particular table. To determine the size used by the database, simply run:

EXEC sp_spaceused

This will return two result sets, the first one containing the database name, size, and unallocated space and the second containing a breakdown of the database's size into how much size is reserved and how much of that is taken up by data, how much by indexes, and how much remains unused. For example, running sp_spaceused on the SQL Server 2005 Express Edition Northwind database that's included in this article's download, the following results are returned:

database_namedatabase_sizeunallocated space
C:\MY PROJECTS\ASP.NET TEACHING\TUTORIAL 2.0 WEBSITES\SPACEUSED\APP_DATA\NORTHWND.MDF4.12 MB0.42 MB

reserveddataindex_sizeunused
3280 KB1432 KB1552 KB296 KB

(Note that the database name is the same as the path to the SQL Server 2005 Express Edition database file; see Working with SQL Server 2005 Express Databases for more information on how SQL Server 2005 Express Edition databases are named.)

To return information about a particular table, simply call sp_spaceused passing in as the first parameter the name of the table. To determine the space used by the Orders table in the Northwind database use:

EXEC sp_spaceused 'Orders'

This will return a single result set that provides the following information:

  • Name - the name of the table
  • Rows - the number of rows in the table
  • Reserved - amount of total reserved space for the table
  • Data - amount of space used by the data for the table
  • Index_Size - amount of space used by the table's indexes
  • Unused - amount of usused space in the table
Running sp_spaceused on the Northwind database's Orders table results in the following output:

namerowsreserveddataindex_sizeunused
Orders830504 KB160 KB320 KB24 KB

As these numbers show, the Orders table's 830 rows take up 160 KB of space. The table's indexes take up an additional 320 KB. (There are nine such indexes on this table, hence the disproportion of index space to data space.) Along with 24 KB of unused data, the Orders table's data and indexes comprise 504 KB of the database's total size.

Returning the Space Used for All Tables
While sp_spaceused can be used to return the space usage for a single table, more than likely we want a web page where we can view the space used for all tables in a database. There are a number of ways to accomplish this, but in short we need to execute sp_spaceused once for every table in the database. In order to accomplish that we could do one of two things:

  • Query the sysobjects table to get a list of tables in the database, then use a CURSOR to iterate through these table results one at a time, executing sp_spaceused for each one. (Ick.)
  • Use the undocumented sp_MSforeachtable stored procedure, which takes in a command and executes that command against all of the user tables in the database. (No need to write any CURSORs or query the sysobjects table!)
The sp_MSforeachtable stored procedure is one of many undocumented stored procedures tucked away in the depths of SQL Server. A list of these handy stored procedures can be found at SQL Server 2000 Useful Undocumented Stored Procedures. In short, you can use sp_MSforeachtable like so:

EXEC sp_MSforeachtable @command1="command to run"

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

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

This will execute EXEC sp_spaceused 'TableName' for each user table in the database. This, of course, returns a result set for each time sp_spaceused is invoked. Therefore, to process the results in an ASP.NET page you'd need to read these results into a SqlDataReader object and then use the NextResult() method to step through the result sets.

An alternative option is to create a stored procedure from which a temporary table is created and populated with one record for each result set returned by sp_spaceused. The entire results, then, can be returned to the ASP.NET page as a single result set by selecting the entire contents of the temporary table at the end of the stored procedure.

Part 2 of this article examines how to use these two techniques for displaying size information about a database's tables using a DataGrid or GridView. The download at the end of this article includes code samples for both techniques.

  • Read Part 2!

  • Software Developer / Programmer - Distributed Systems (NYC)
    Next Step Systems
    US-NY-New York

    Justtechjobs.com Post A Job | Post A Resume


    ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article