I have inherited a fairly large SQL Server database. It seems to take up more space than I would expect, given the data it contains.
Is there an easy way to determine how much space on disk each table is consuming?
If you are using SQL Server Management Studio (SSMS), instead of running a query (which in my case returned duplicate rows) you can run a standard report.
Note: The database compatibility level must be set to 90 or above for this to work correctly. See http://msdn.microsoft.com/en-gb/library/bb510680.aspx
sp_spaceused can get you information on the disk space used by a table, indexed view, or the whole database.
This reports the disk usage information for the ContactInfo table.
To use this for all tables at once:
You can also get disk usage from within the right-click Standard Reports functionality of SQL Server. To get to this report, navigate from the server object in Object Explorer, move down to the Databases object, and then right-click any database. From the menu that appears, select Reports, then Standard Reports, and then "Disk Usage by Partition: [DatabaseName]".
After some searching, I could not find an easy way to get information on all of the tables. There is a handy stored procedure named sp_spaceused that will return all of the space used by the database. If provided with a table name, it returns the space used by that table. However, the results returned by the stored procedure are not sortable, since the columns are character values.
The following script will generate the information I'm looking for.
If you need to calculate exactly the same numbers, that are on 'table properties - storage' page in SSMS, you need to count them with the same method as it done in SSMS (works for sql server 2005 and above ... and also works correctly for tables with LOB fields - because just counting "used_pages" is not enought to show accurate index size):
Above queries are good for finding the amount of space used by the table (indexes included), but if you want to compare how much space is used by indexes on the table use this query:
This will give you the sizes, and record counts for each table.
Please note that
When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.
For get all table size in one database you can use this query :
And you can change it to insert all of result into temp table and after that select from temp table.
My post is only relevant for SQL Server 2000 and has been tested to work in my environment.
This code accesses All possible databases of a single instance, not just a single database.
I use two temp tables to help collect the appropriate data and then dump the results into one 'Live' table.
Returned data is: DatabaseName, DatabaseTableName, Rows (in the Table), data (size of the table in KB it would seem), entry data (I find this useful for knowing when I last ran the script).
Downfall to this code is the 'data' field is not stored as an int (The chars 'KB' are kept in that field), and that would be useful (but not totally necessary) for sorting.
Hopefully this code helps someone out there and saves them some time!
In case you need to know, the rsp_DatabaseTableSizes table was created through:
I added a few more columns on top of marc_s answer:
We use table partitioning and had some trouble with the queries provided above due to duplicate records.
For those who need this, you can find below the query as run by SQL Server 2014 when generating the "Disk usage by table" report. I assume it also works with previous versions of SQL Server.
It works like a charm.
Here is a way to get all tables sizes quickly with the following steps:
1) Write given T-SQL commands to list all database tables
2) Now copy list of database tables and copy into a new query analyzer
3) In sql query analyzer, select from top tool bar option Results to file (Ctrl + Shift + F).
4) Now hit finally Execute button red marked from above tool bar.
5) Database size of all tables now stored in a file on your computer.
From a command prompt using OSQL:
Riffing on @Mark answer above, added the @updateusage='true' to force the latest size stats (https://msdn.microsoft.com/en-us/library/ms188776.aspx):
A small change on Mar_c's answer, since i have been going back to this page so often, ordered by most row's first:
As a simple extension to marc_s's answer (the one that has been accepted), this is adjusted to return column count and allow for filtering: