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'

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.