بدست آوردن حجم دیتابیس ها ، فایل ها و جداول

بعضی مواقع که با کمبود فضا در سرور دیتابیس روبرو می شویم به دنبال یافتن بیشترین فضای مصرف شده توسط دیتابیس ها ، فایل های دیتابیس و جداول یک دیتابیس می گردیم. با من همراه شوید تا این موارد را بررسی نماییم:

• یافتن حجیم ترین دیتابیس ها و فضای استفاده شده توسط آن ها
• یافتن حجیم ترین فایل های دیتابیس ها و فضای استفاده شده از آن ها
• یافتن حجیم ترین جداول یک دیتابیس

یافتن حجیم ترین دیتابیس ها و فضای استفاده شده توسط آن ها

با استفاده از کوئری زیر می توان بزرگترین دیتابیس ها به همراه فضای استفاده شده از آن ها را مشاهده کرد:

DROP TABLE IF EXISTS #files

SELECT TOP 0 DB_NAME() DbName, name [FileName] , size , FILEPROPERTY(name,'SpaceUsed')  SpaceUsed 
	INTO #files 
FROM sys.database_files 

EXEC sp_msforeachdb 'use [?] 
INSERT INTO #files
SELECT Db_Name() DbName, name [FileName] , size , FILEPROPERTY(name,''SpaceUsed'')  SpaceUsed
FROM sys.database_files'

SELECT t.DbName , SUM(t.Size) / 128.0 SizeMB , SUM(t.SpaceUsed) / 128.0 SpaceUsedMB
FROM #files t
GROUP BY t.DbName
ORDER BY SizeMB DESC

یافتن حجیم ترین فایل های دیتابیس ها و فضای استفاده شده از آن ها

با استفاده از کوئری زیر می توان بزرگترین فایل های دیتابیس ها به همراه فضای استفاده شده از آن ها را مشاهده کرد:

DROP TABLE IF EXISTS #files

SELECT TOP 0 DB_NAME() DbName, name [FileName] , physical_name , size , FILEPROPERTY(name,'SpaceUsed')  SpaceUsed 
	INTO #files 
FROM sys.database_files 

EXEC sp_msforeachdb 'use [?] 
INSERT INTO #files
SELECT Db_Name() DbName, name [FileName] , physical_name , size , FILEPROPERTY(name,''SpaceUsed'')  SpaceUsed
FROM sys.database_files'

SELECT t.DbName , t.FileName , t.physical_name , t.Size / 128.0 SizeMB , t.SpaceUsed / 128.0 SpaceUsedMB
FROM #files t
ORDER BY SizeMB DESC

یافتن حجیم ترین جداول یک دیتابیس

با استفاده از کوئری زیر می توان حجیم ترین جداول یک دیتابیس به همراه فضای استفاده شده از آن ها را مشاهده کرد:

USE AdventureWorks2017
GO

SELECT CONCAT('[',SCHEMA_NAME(t.schema_id) , '].[' , t.name , ']') AS TableName
	 , SUM(p.reserved_page_count) * 8 TotalSize_KB , SUM(p.used_page_count) * 8 UsedSize_KB 
	 , SUM(CASE WHEN p.index_id BETWEEN 0 AND 1 THEN p.row_count ELSE 0 END) TableRowCount
FROM sys.dm_db_partition_stats p
	 INNER JOIN sys.tables t ON t.object_id = p.object_id
GROUP BY CONCAT('[',SCHEMA_NAME(t.schema_id) , '].[' , t.name , ']')
ORDER BY TotalSize_KB DESC

نتیجه گیری:

به عنوان یک DBA یا توسعه دهنده یک برنامه نیاز است که از رشد دیتابیس ، فایل ها و جداول مطلع باشید خیلی از مواقع حجم زیادی از دیتابیس مربوط به جداولی است که اصلا فکرشان را هم نمی کنید. کوئری های بالا را بر روی سرورهای خود اجرا کنید و از نتایج آن شگفت زده شوید.

امیدوارم این آموزش برای خوانندگان گرامی مفید واقع شود. لطفا با ارائه نظرات ، سوالات و پیشنهادات خود به بالا بردن کیفیت مطالب آموزشی و همچنین ایجاد دلگرمی برای ادامه مسیر آموزش به ما کمک نمایید. همچنین می توانید با عضویت در کانال تلگرام IranSQL از جدیدترین مقالات و فیلم های آموزشی با خبر شوید.