When we are working with SQL Server databases, very often we will come across the problem of abnormal database size growth. In such a situation we need to get the size of tables in the database to identify the abnormal sized tables to figure out the cause of growth.
Here we will go through some of the easiest methods to get the size of all the tables in a database. This will help you to identify the tables with abnormal size.
1. Find Size Of Tables Using SSMS Standard Report
The easiest way to find the size of all the tables in a database is to use the SQL Server Management Studio’s (SSMS) standard report called Disk Usage by Table. To access the disk usage table:
- Login to SSMS.
- Right click the database.
- In the right-click menu go to Reports >> Standard Reports >> Disk Usage by Tables.
This standard report will give you the number of records in the table, reserved physical space in KB and it’s breakup.
2. Using SP_SPACEUSED
This is another way to find the size of all the tables in a database and order them. we can use the SP_SPACEUSED system stored procedure along with the SP_MSForEachTable system stored procedure. As SP_MSForEachTable returns a result set for each table, we will use a temp table to insert the result set and finally return the table sizes from the temp table.
|
1
2
3
4
5
6
7
8
9
|
Create TABLE #TableSize (TableName VARCHAR(200),Rows VARCHAR(20),
Reserved VARCHAR(20),Data VARCHAR(20),index_size VARCHAR(20),
Unused VARCHAR(20))
exec sp_MSForEachTable 'Insert Into #TableSize Exec sp_spaceused [?]'
Select
TableName, CAST(Rows AS bigint) As Rows,
CONVERT(bigint,left(Reserved,len(reserved)-3)) As Size_In_KB
from #TableSize order by 3 desc
Drop Table #TableSize
|
3. Using SYS.TABLES & SYS.Allocation_UNITS
Another method is to use sys.tables, sys.partitions and sys.allocation_units system tables.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SELECT
t.NAME AS TableName,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.partitions p ON t.object_id = p.OBJECT_ID
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY
t.Name
ORDER BY
TotalSpaceKB Desc
|
Article ID: 2208, Created: January 9, 2022 at 4:32 AM, Modified: January 9, 2022 at 4:35 AM