over 8 years ago
Many times when we manage SQL server databases we need to determine how much space each table is consuming on disk. In this blog we will learn two approaches for solving this frequently encountered common problem.
Approach 1:
We can use the below query to get table wise usage details:
SELECTtab.NAME AS [Table Name],sch.Name AS [Schema Name],prt.rows AS [Row Counts],SUM(aunit.total_pages) * 8 AS [Total Space in KB],SUM(aunit.used_pages) * 8 AS [Used Space in KB],(SUM(aunit.total_pages) - SUM(aunit.used_pages)) * 8 AS [Unused Space in KB]FROMsys.tables tabINNER JOINsys.indexes indx ON tab.OBJECT_ID = indx.object_idINNER JOINsys.partitions prt ON indx.object_id = prt.OBJECT_ID AND indx.index_id = prt.index_idINNER JOINsys.allocation_units aunit ON prt.partition_id = aunit.container_idLEFT OUTER JOINsys.schemas sch ON tab.schema_id = sch.schema_idWHEREtab.is_ms_shipped = 0ANDindx.OBJECT_ID > 255GROUP BYtab.Name, sch.Name, prt.RowsORDER BYtab.Name
SELECT
tab.NAME AS [Table Name],
sch.Name AS [Schema Name],
prt.rows AS [Row Counts],
SUM(aunit.total_pages) * 8 AS [Total Space in KB],
SUM(aunit.used_pages) * 8 AS [Used Space in KB],
(SUM(aunit.total_pages) - SUM(aunit.used_pages)) * 8 AS [Unused Space in KB]
FROM
sys.tables tab
INNER JOIN
sys.indexes indx ON tab.OBJECT_ID = indx.object_id
INNER JOIN
sys.partitions prt ON indx.object_id = prt.OBJECT_ID AND indx.index_id = prt.index_id
INNER JOIN
sys.allocation_units aunit ON prt.partition_id = aunit.container_id
LEFT OUTER JOIN
sys.schemas sch ON tab.schema_id = sch.schema_id
WHERE
tab.is_ms_shipped = 0
AND
indx.OBJECT_ID > 255
GROUP BY
tab.Name, sch.Name, prt.Rows
ORDER BY
tab.Name
Approach 2:
If you are using SQL Server Management Studio, you can avoid running the above query and instead use a report.
To access the report follow the below steps:
1. Right click on the database
2. Navigate to Reports > Standard Reports > Disk Usage By Table
0 Comment(s)