Hi friends, In previous article we were learn OPENJSON function in Sql and ISJSON, JSON_VALUE, JSON_QUERY and JSON_MODIFY in Sql
In this article, we will learn How to find Database Size in SQL Server ? and How to Get size of all tables in SQL Server database ? using two most important SQL Queries, Which will help you to find Data Base Storage for Entire DataBase and Tables in DataBase.
Some times we needs to know the size of Database When we plans to move our entire database from one server to another server. With the help of following query you can find Database Size on the disc as follows.
USE AdventureWorks2014
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
OUTPUT: DATABASE_NAME LOG_SIZE_MB ROW_SIZE_MB TOTAL_SIZE_MB ----------------------- -------------- ---------------- ------------------ AdventureWorks2014 2.00 205.25 207.25
Another Example:
USE AdventureWorks2014
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
OUTPUT: DatabaseName Logical_Name Physical_Name SizeMB ---------------------- ------------------------- ---------------------------------------------------------------------------------------------------- ----------- AdventureWorks2014 AdventureWorks2014_Data C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014_Data.mdf 205 AdventureWorks2014 AdventureWorks2014_Log C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014_Log.ldf 2 (2 row(s) affected)
Most Important Some time we needs to know about largest table size in database, for example, in case of when we were planed to store all files on database, after some time it increases size of database frequently.
So the question is :
How to find largest table in DATABASE to reduce DATABASE Size.
Following Sql Query is used to find Largest Table in Database.
USE AdventureWorks2014
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
OUTPUT: TABLE_NAME SCHEMA_NAME ROW_COUNTS TOTAL_SPACE_KB TOTAL_SPACE_MB UsedSpaceKB USED_SPACE_MB UNUSED_SPACE_KB UNUSED_SPACE_MB ----------------------------------------- ----------------------------------- -------------------- -------------------- ---------------------- -------------------- ---------------------- -------------------- ----------------- Person Person 19972 32760 31.99 32184 31.43 576 0.56 SalesOrderDetail Sales 121317 17448 17.04 16160 15.78 1288 1.26 TransactionHistory Production 113443 11088 10.83 10432 10.19 656 0.64 TransactionHistoryArchive Production 89253 8864 8.66 8312 8.12 552 0.54 SalesOrderHeader Sales 31465 8640 8.44 8256 8.06 384 0.38 WorkOrderRouting Production 67131 7400 7.23 6944 6.78 456 0.45 DatabaseLog dbo 1597 6656 6.50 6600 6.45 56 0.05 WorkOrder Production 72591 7184 7.02 6440 6.29 744 0.73 Address Person 19614 5960 5.82 5472 5.34 488 0.48 EmailAddress Person 19972 3664 3.58 3496 3.41 168 0.16 CreditCard Sales 19118 2512 2.45 2328 2.27 184 0.18 BusinessEntityAddress Person 19614 2656 2.59 2312 2.26 344 0.34 Customer Sales 19820 2656 2.59 2296 2.24 360 0.35 ProductPhoto Production 101 2400 2.34 2296 2.24 104 0.10 PersonPhone Person 19972 2320 2.27 2176 2.13 144 0.14 Password Person 19972 1992 1.95 1896 1.85 96 0.09 BusinessEntity Person 20777 1488 1.45 1336 1.30 152 0.15 CurrencyRate Sales 13532 1304 1.27 1208 1.18 96 0.09 Store Sales 701 920 0.90 896 0.88 24 0.02 PurchaseOrderDetail Purchasing 8845 728 0.71 704 0.69 24 0.02 SalesOrderHeaderSalesReason Sales 27647 776 0.76 704 0.69 72 0.07 PurchaseOrderHeader Purchasing 4012 544 0.53 496 0.48 48 0.05 PersonCreditCard Sales 19118 584 0.57 496 0.48 88 0.09 Document Production 13 416 0.41 408 0.40 8 0.01 BillOfMaterials Production 2679 472 0.46 360 0.35 112 0.11 Illustration Production 5 288 0.28 248 0.24 40 0.04 Product Production 504 248 0.24 232 0.23 16 0.02 ProductDescription Production 762 248 0.24 200 0.20 48 0.05 BusinessEntityContact Person 909 192 0.19 192 0.19 0 0.00 Employee HumanResources 290 200 0.20 192 0.19 8 0.01 ProductModel Production 128 256 0.25 192 0.19 64 0.06 JobCandidate HumanResources 13 232 0.23 160 0.16 72 0.07 ProductVendor Purchasing 460 104 0.10 104 0.10 0 0.00 SpecialOfferProduct Sales 538 88 0.09 88 0.09 0 0.00 StateProvince Person 181 80 0.08 80 0.08 0 0.00 ProductInventory Production 1069 72 0.07 72 0.07 0 0.00 ProductReview Production 4 72 0.07 72 0.07 0 0.00 CountryRegion Person 238 64 0.06 64 0.06 0 0.00 EmployeeDepartmentHistory HumanResources 296 64 0.06 64 0.06 0 0.00 ShipMethod Purchasing 5 48 0.05 48 0.05 0 0.00 ProductModelProductDescriptionCult Production 762 48 0.05 48 0.05 0 0.00 SalesPersonQuotaHistory Sales 163 48 0.05 48 0.05 0 0.00 SalesTerritory Sales 10 48 0.05 48 0.05 0 0.00 ProductCategory Production 4 48 0.05 48 0.05 0 0.00 Vendor Purchasing 104 48 0.05 48 0.05 0 0.00 AddressType Person 6 48 0.05 48 0.05 0 0.00 Shift HumanResources 3 48 0.05 48 0.05 0 0.00 ProductProductPhoto Production 504 104 0.10 56 0.05 48 0.05 SalesTaxRate Sales 29 48 0.05 48 0.05 0 0.00 ProductSubcategory Production 37 48 0.05 48 0.05 0 0.00 ProductCostHistory Production 395 40 0.04 40 0.04 0 0.00 ProductListPriceHistory Production 395 40 0.04 40 0.04 0 0.00 CountryRegionCurrency Sales 109 32 0.03 32 0.03 0 0.00 SpecialOffer Sales 16 32 0.03 32 0.03 0 0.00 Location Production 14 32 0.03 32 0.03 0 0.00 Culture Production 8 32 0.03 32 0.03 0 0.00 Department HumanResources 16 32 0.03 32 0.03 0 0.00 Currency Sales 105 32 0.03 32 0.03 0 0.00 ShoppingCartItem Sales 3 32 0.03 32 0.03 0 0.00 SalesPerson Sales 17 32 0.03 32 0.03 0 0.00 SalesTerritoryHistory Sales 17 32 0.03 32 0.03 0 0.00 ContactType Person 20 32 0.03 32 0.03 0 0.00 ScrapReason Production 16 32 0.03 32 0.03 0 0.00 UnitMeasure Production 38 32 0.03 32 0.03 0 0.00 EmployeePayHistory HumanResources 316 32 0.03 32 0.03 0 0.00 PhoneNumberType Person 3 16 0.02 16 0.02 0 0.00 SalesReason Sales 10 16 0.02 16 0.02 0 0.00 ProductDocument Production 32 16 0.02 16 0.02 0 0.00 AWBuildVersion dbo 1 16 0.02 16 0.02 0 0.00 ProductModelIllustration Production 7 16 0.02 16 0.02 0 0.00 ErrorLog dbo 0 0 0.00 0 0.00 0 0.00 (71 row(s) affected)
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.