We’re all familiar with trying to figure out just how much data is in our database. To get approximately how much memory the whole database takes up you can open SQL Server Management Studio, right-click on the database name you’re investigating, click properties and then click Files. To figure out how many rows, you can use the query SELECT Count(*) FROM YourTable and, if it is a large table and/or a busy database, suffer through the performance hit.
There’s a better way. Not only that, but it gives you more information, doesn’t cause performance problems and does it all in one step. What am I talking about? The built in stored procedure sp_spaceused. Most of the time I’m using it, I’m looking for information about a specific table. Lets use the AdventureWorks database for an example. Lets find out how big the Person.Address table is.
All you need is the name of the stored procedure followed by the name of the table in single quotes
And here’s what it returns:
|Address||19614||5000 KB||2240 KB||2504 KB||256 KB|
This is great! It tells us how many rows in the table, how much space is being reserved, how much is actually used and how much space the index is taking. The best part is, it didn’t even touch your tables to do it. It is querying the metadata stored in your database, basically the information SQL Server keeps track of about how your database is put together.
If you want to get space information about the whole database, just run sp_spaceused and don’t put anything after it.
This is a tool I love, so I’ll likely do more research on exactly what it is doing and follow this post up later.