RSS

How-To Geek

With any active database, disk storage requirements are going to grow over time. While you can easily view the disk space used by an entire database by either look in the Files page of the database properties in SQL Management Studio or simply viewing the underlying files in Windows Explorer, what if you want to dig a bit deeper and see the parts that comprise the sum of the whole?

To see this information, you need to view the size of the individual tables. Thankfully, SQL Server has a built in stored procedure, sp_SpaceUsed, which displays the storage statistics of individual tables. Leveraging this stored procedure, we have created a batch script which allows you to easily produce a listing of each table in a database and view its storage statistics.

When the script is run, the following information for each table in the database is listed in a tabular format:

Database table name

Number of rows in the table

Total disk space allocated to this table by SQL

Amount of disk space used for data storage

Amount of disk space used for internal SQL indexes

Amount of disk space currently unused

Using the Script

The DBSize batch script is compatible with SQL 2005 and higher and must be run on a machine which has the SQLCMD tool installed (installed as part of the SQL Server installation). It is recommended you drop this script into a location set in your Windows PATH variable (i.e. C:Windows) so it can easily be called like any other application from the command line.

To view the help information, simply enter:

DBSize /?

Examples

To run a report on “MyDB” on the default instance and direct the output to “MyDB Table Size.txt” on the desktop:

DBSize MyDB > “%UserProfile%DesktopMyDB Table Size.txt”

To run a report on “MyDB” on the named instance “Special” using the “sa” user with password “123456”:

GEEK TRIVIA

DID YOU KNOW?

The twist-grip throttle found on millions of motorcycles the world over, wherein you twist one of the handles to adjust the engine throttle, was introduced in 1867 and has remained largely unchanged ever since.