Table of Contents

Introduction

In this article, we will try to pull all sizes from SQL server. How to find Database Size in SQL Server? How to Get size of all tables in the SQL Server database? We will use different SQL Queries to find Database file size, database size, and tables size. Very often we need to check the size of the Database whenever we plan to move our entire database or file or only tables from one server to another server. Using these queries above mention task will easier for us.

Script 01

Below script is to find DATABASE NAME, LOG SIZE in MB, ROW SIZE in MB & TOTAL SIZE in MB for a particular database.

USE [OurTechIdeas.com]
Go
 
SELECT 
      DATABASE_NAME = DB_NAME(database_id)
    , LOG_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , ROW_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , TOTAL_SIZE_MB = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() 
GROUP BY database_id
Go

USE [OurTechIdeas.com]

SELECT

DATABASE_NAME = DB_NAME(database_id)

, LOG_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))

, ROW_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))

, TOTAL_SIZE_MB = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))

FROM sys.master_files WITH(NOWAIT)

WHERE database_id = DB_ID()

GROUP BY database_id

Result

Script 02

Below script is to find Database Name, Logical Name & Physical location for a particular database.

USE [OurTechIdeas.com]
Go
 
SELECT DB_NAME(database_id) AS DatabaseName,
       Name AS Logical_Name,
       Physical_Name,
       (size * 8) / 1024 SizeMB
FROM sys.master_files
WHERE database_id = DB_ID()
Go

USE [OurTechIdeas.com]

SELECT DB_NAME(database_id) AS DatabaseName,

Name AS Logical_Name,

Physical_Name,

(size * 8) / 1024 SizeMB

FROM sys.master_files

WHERE database_id = DB_ID()

Result

Script 03

Very often DBAs needs to know about the largest table size in the database. Following SQL query is used to find the largest table in the Database.

USE [OurTechIdeas.com]
Go
 
;with CTE AS(
SELECT 
    t.NAME AS TABLE_NAME,
    s.Name AS SCHEMA_NAME,
    p.rows AS ROW_COUNTS,
    SUM(a.total_pages) * 8 AS TOTAL_SPACE_KB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TOTAL_SPACE_MB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS USED_SPACE_MB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UNUSED_SPACE_KB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UNUSED_SPACE_MB
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
)
SELECT * FROM CTE ORDER BY USED_SPACE_MB DESC
Go

USE [OurTechIdeas.com]

;with CTE AS(

SELECT

t.NAME AS TABLE_NAME,

s.Name AS SCHEMA_NAME,

p.rows AS ROW_COUNTS,

SUM(a.total_pages) * 8 AS TOTAL_SPACE_KB,

CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TOTAL_SPACE_MB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS USED_SPACE_MB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UNUSED_SPACE_KB,

CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UNUSED_SPACE_MB

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

)

SELECT * FROM CTE ORDER BY USED_SPACE_MB DESC

Introduction

Script 01

Result

Script 02

Result

Script 03

Result

SQL Instance Last restart date and time in SQL Server

List of jobs associated with database in SQL Server

Comments

Categories

Recent Posts

Recent Comments

Archives

Related Posts