Highlighting sp_spaceused

, 2018-10-15

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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads