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”:
DBSize MyDB /S:.Special /U:sa /P:123456
Download the Database Table Size Batch Script from SysadminGeek.com
Windows Live Essentials 2011 has a new beta release, and it’s got a lot of changes for Windows Live Messenger—the biggest of which might be Facebook chat support.
Sometimes when adding music, apps, or updates to your iPod Touch or iPhone something goes wrong and it becomes unstable. Here we take a look at some basic steps on how to reset or restore it to help getting it working again.
Banks, libraries, and points programs have the annoying habit of making you dig out your card to find the magic number that will let you log in to their web interface. Rather than make the ten foot walk to get your wallet, use LastPass to store all those number at once, securely.
Do you need to remove one of your Kindle devices or applications to free up your eBooks? Here’s how you can remove one of your registered Kindle apps.
A “Dislike” button is the latest in a string of cons circulating on the social-networking site. It purports to offer users a way to express their aversion to links, videos and other annoying content posted by friends on F...
One of the more annoying things with replying or forwarding emails is the original message you reply to says open and you have to manually close out of it. Here we show you how to alleviate the annoyance.
Are you a Netflix user constantly on the hunt for new titles to add to your Queue? We’ll look at how to manage your Netflix Queue from Your Android phone using the Movies by Flixter App.
Google Calendar Sync now supports Outlook 2010 — our top feature request. Outlook 2010 comes in 32-bit and 64-bit versions, and currently only 32-bit is supported. To start syncing your calendar with Outlook 2010, download ...
We spend a lot of our online time in an RSS reader, but not everything we want to follow has an RSS feed. There are web apps to monitor pages for changes, but forum threads spill over onto many pages – how can you get an RSS feed for a specific thread?
Photo by Laughing Squid
