I will show you how to check disk space utilization in SQL Server in this article, with a particular emphasis on how to find the disk space consumed by different tables. Database administrators must keep an eye on disk space use to guarantee optimal database performance and prevent storage-related problems. Through this approach, you will be able to better plan, optimize, and manage your storage resources by knowing how much disk space each table takes up.
How Can I Check How Much Disk Space Each Table Is Using?
A SQL Server database can be checked for disk space use by table using one of two approaches.
Integrated function located in Reports (SQL Server Management Studio)
We will go over "Disk Usage By Table" in this section, but first, let's take a step-by-step look at the built-in function under reports (the report menu has a lot of reports that we may see). I've downloaded the "NorthWind" database in order to provide an example of disk utilization by table.
The total amount of space allotted to each table in the list will be shown in the report. The "Orders" table takes up the most room in the example below, followed by the "OrderDetails" table, the "Employees" table, and so on.
Using SQL Stored Procedure/Query
If you want to calculate disk space utilization by tables in an SQL Server database using a stored procedure and customize your report, you can do so with a stored procedure. I’ve created one to calculate disk space utilization by tables. Please see the stored procedure below.
CREATE PROCEDURE USP_GetDiskSpaceUsedByTables
AS
BEGIN
SELECT
t.name AS TableName,
s.name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalReservedSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalReservedSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.object_id = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.name NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.object_id > 255
GROUP BY
t.name, s.name, p.rows
ORDER BY
TotalReservedSpaceMB DESC, t.name
END
Let's execute this stored procedure and review the results, which are calculated in the same way as the built-in SQL report functionality.
Why Check Disk Space Utilization by Tables in a SQL Server Database?
Here are the key reasons to check disk space utilization by tables in an SQL Server database.
- Performance optimization
- Data management
- Cost management
- Compliance and auditing
- Resource allocation
- Data Archiving
- Post index maintenance
Summary
I've covered efficient ways to keep an eye on and control disk space usage in your SQL Server database in this post. I specifically walk through two methods for monitoring disk space usage at the table level. By employing these techniques, you can obtain important knowledge about how much storage is being used by your database, which will enable you to better effectively schedule your tasks. In your database environment, this proactive management may result in better resource allocation and performance.
Happy reading!
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
