What is unallocated space and unused space in SQL Server?

Introduction

When you check the size used by a database in SQL Server, you will see unallocated space and unused. They may both look like “free space,” but they mean different things. In this article, we will summarize the difference between the two, based on the fundamentals of SQL Server space management.

Fundamentals of SQL Server Space Management (Page / Extent)

SQL Server manages data using units called pages and extents.

  • Page: The basic unit that stores data (8KB)
  • Extent: A group of 8 pages (64KB). The basic unit SQL Server uses to manage space

There are two types of extents: “Uniform Extent” (owned by a single object) and “Mixed Extent” (shared by multiple objects). With a Uniform Extent, the data within one extent belongs to a single table or index. With a Mixed Extent, even within the same extent, one page might contain data from tableA while another page contains data from tableB.

Terms Shown by sp_spaceused (unallocated / reserved / unused)

sp_spaceused shows disk space usage for a database (or a table/index). The key point is that sp_spaceused classifies space based on whether it is reserved inside the database.

  • unallocated space: Space in the database that has not been allocated to any database object
  • reserved: The total space already allocated to some object
  • unused: Space that is allocated to some object but not used yet

In other words, the relationship is as follows:

  • unallocated: Not owned by any object yet (available to be allocated)
  • unused: Owned by an object, but still contains unused space
-- Entire database
EXEC sp_spaceused;
-- Per table
EXEC sp_spaceused N'dbo.tableName';

Difference Between Unallocated Space and Unused Space

Unallocated space

  • Space that exists in the database but has not been allocated to any object yet
  • Example: Newly grown data files typically increase unallocated space

Unused space

  • Page-level space within an allocated (reserved) area that has not been used yet
  • Example: If one extent is allocated and only one page is used, the remaining pages are counted as unused. Note that unused does not mean free bytes inside a page (within the 8KB page). It is treated as unused pages (page-level).

Common Scenarios Where Unused / Unallocated Increase

Example: Unused increases

Unused space may increase after a bulk insert of a small number of rows. This can happen because, for performance reasons, SQL Server may allocate new extents instead of using existing unused space. If a new extent is allocated but only a small number of rows are inserted, the pages that were not used are counted as unused space.

Example: Unallocated increases

When you grow a data file, the grown portion initially remains as “space not reserved for objects,” so unallocated space increases. Unallocated space can also be reduced by shrinking the file (for example, using DBCC SHRINKFILE).

Summary

  • SQL Server manages space based on Pages and Extents, and one Extent is 8 pages (64KB).
  • unallocated in sp_spaceused means “space not reserved for objects.”
  • unused in sp_spaceused means “reserved but not used yet.”