Tag Archives: size of tables

Check the size of database and table in Microsoft SQL Server

Check the size of Database and table in Microsoft SQL Server

Check the Size of Database in SQL Server

use "ADVENTUREWORKS"
exec sp_spaceused

Output:

database_name   database_size   unallocated space
adventureworks  17899.13 MB     5309.39 MB

Find the Size of Database files in Microsoft SQL Server

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) like '%AdventureWorks%'
order by SizeMB desc

Output:

DatabaseName    Logical_Name        Physical_Name                SizeMB
ADVENTUREWORKS  ADVENTUREWORKS      D:\MSSQL\Data\adventure.mdf  21453             
AVENTUREWORKS   ADVENTUREWORKS_LOG  D:\MSSQL\Data\adventure.ldf  5234

Check the size of database

sp_helpdb 'adventureworks'

Check size of data files in database

USE AdventureWorks;
GO
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files ;

Check Size of Table

sp_spaceused 'MyTable'