Highlighting sp_spaceused


October 15, 2018 by Kenneth Fisher

I’ve been using sp_spaceused a lot recently so I thought I would bring it up again. Basically it’s a quick way to bring up the size of things. Specifically:

  • Tables
  • Indexed views
  • Service Broker queues
  • Databases

It’s the first and last that I use the most often. It’s just an incredibly quick and easy way to get row count, size and free space within a table, and total size and free space within a database.

USE Test;
EXEC sp_spaceused;

So my Test database is about 23GB in size, 20GB unallocated, 2GB of data, 23MB of indexes, and ~1MB allocated to objects but not actually in use.

Now, in the research for this post, I found that there is a flag that sets the output to a single rowset. I particularly liked that because I find the two rowsets annoying.

EXEC sp_spaceused 'Table1', @oneresultset = 1;

My Table1 table has 3 rows, is 72KB in size of which 8KB is data, 8KB is indexes and 56KB is unused. And best of all the output is in a single row.

Again, this is a really easy fast way to get size information about a table, database, indexed view or SB queue. That said, if you need all of the tables (or databases) then go with the built-in reports available, or the Object Explorer Details view, which I think is going to be my next post.

One thought on “Highlighting sp_spaceused

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: