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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 |
Result
Script 02
Below script is to find Database Name, Logical Name & Physical location for a particular database.
|
1 2 3 4 5 6 7 8 9 10 |
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 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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 |
Result
script need to get the all dbs information instead of selected one