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:
- Indexed views
- Service Broker queues
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; GO 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.
Category: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL | Tags: Microsoft SQL Server, system functions, T-SQL
One thought on “Highlighting sp_spaceused”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] sp_spaceused […]