# SQL Server Table Size Report (Used vs Allocated Space)

##

### **Overview**

This page documents a SQL Server query used to analyse **table‑level storage usage** within a database. It provides a breakdown of how much space each table consumes, both in terms of **used pages** and **allocated pages**, and orders the results from largest to smallest.

This is useful for:

* Capacity planning
* Identifying unusually large tables
* Performance tuning
* Understanding storage growth over time

***

### **Purpose of the Query**

SQL Server stores data in **8 KB pages**, grouped into allocation units. This query aggregates those pages for each table and converts them into megabytes, giving you a clear view of:

* **Used MB** – space actively used by the table
* **Allocated MB** – space reserved by SQL Server for future growth

The result is a concise report showing which tables consume the most space.

***

### **The SQL Query**

```sql
select schema_name(tab.schema_id) + '.' + tab.name as [table],
    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
    cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables tab
    inner join sys.indexes ind 
        on tab.object_id = ind.object_id
    inner join sys.partitions part 
        on ind.object_id = part.object_id and ind.index_id = part.index_id
    inner join sys.allocation_units spc
        on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc;
```

***

### **How It Works**

#### **1. Table Identification**

`sys.tables` provides the list of all user tables in the database.

#### **2. Joining System Views**

The query joins:

* `sys.indexes` – to include all index structures
* `sys.partitions` – to access partition-level metadata
* `sys.allocation_units` – to retrieve page counts

These views together expose how SQL Server physically stores table data.

#### **3. Calculating Space Usage**

SQL Server pages are 8 KB each.\
The query converts pages → KB → MB:

```
pages * 8 KB / 1024 = MB
```

Two metrics are calculated:

* **used\_pages** → actual data
* **total\_pages** → allocated space

#### **4. Grouping and Ordering**

Tables are grouped by schema and name, then sorted by **largest used space first**.

***

### **Example Output**

| table                | used\_mb | allocated\_mb |
| -------------------- | -------- | ------------- |
| dbo.Orders           | 512.00   | 768.00        |
| sales.TransactionLog | 430.25   | 512.00        |
| dbo.Customers        | 120.50   | 256.00        |

*(Values shown are illustrative.)*

***

### **When to Use This Query**

Use this script when you need to:

* Audit database size
* Identify large or growing tables
* Investigate performance issues related to storage
* Prepare for migrations or archiving
* Monitor space usage trends

***

### **Related Tools & Queries**

* `sp_spaceused` for quick table-level stats
* `sys.dm_db_partition_stats` for row counts
* Index fragmentation reports
* Filegroup and database size queries

***

If you'd like, I can also generate:

* A shorter “quick reference” version
* A version formatted for Confluence, GitHub Wiki, or Azure DevOps Wiki
* A companion page explaining how to automate this report

Just tell me the style you want.

Thanks Jack for sharing this. 


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://wiki.owain.codes/coding/snippets/untitled/sql-server-table-size-report-used-vs-allocated-space.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.