SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Highlighting sp_spaceused

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.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...