img

دریافت هشدار فضای خالی درایو برای پایگاه‌های اطلاعاتی

/
/
/

آیا به دنبال یک ابزار ساده هشداردهنده هستید که به شما اطلاع دهد فضای دیسک در حال تمام شدن است؟ احتمالاً فایل‌های داده‌ای و فایل‌های گزارش (log file) متعددی دارید که در یک یا چند درایو دیسک مستقر هستند و می‌خواهید وقتی فضای این درایوها به زیر X درصد از فضای خالی دیسک رسید، پیام هشدار در گزارش اپلیکیشن ویندوز خود دریافت نمایید. در این مقاله قصد داریم نگاهی به اسکریپت ساده‌ای بیاندازیم که می‌توانید در زمان کم شدن فضای دیسک خود، از آن به عنوان هشدار استفاده نمایید.
یک رویه ذخیره شده به نام dbo.usp_AlertOnDBSpace نوشته‌ایم. این رویه از xp_cmdshell برای فراخواندن اسپکریپت PowerShell استفاده می‌کند که از WMI (ابزار مدیریت ویندوز) برای اطلاعات فضای درایو استفاده می‌نماید. متاسفانه نمی‌توانیم از xp_fixeddrives استفاده کنیم چون فقط مقدار فضای باقی مانده را نشان می‌دهد.

و اما مراحل کار این رویه ذخیره شده:
۱- این رویه اطلاعات مربوط به فضا را از فرمان PowerShell Get-WMIObject Win32_LogicalDisk که در درایوهای سخت محلی فیلتر شده،می‌گیرد. اطلاعات مرتبط با حرف درایو، تعداد بایت‌های آزاد و تعداد کل بایت‌های هر دیسک را به دست می‌آورد.
۲- رویه تمام این داده‌ها را وارد می‌کند، تجزیه کرده و به GB ( تقسیم بر ۱۰۲۴^۳) در یک متغییر جدولی تبدیل می‌کند. درصد خالی دیسک یک ستون محاسبه است که به صورت رُند در نظر گرفته می‌شود
(۱۰۰٫۰ * FreeSpaceGB / TotSizeGB, 1).
۳- رویه، تمام سطرها را در جدول لوپ کرده و بررسی می‌کند که آیا درایو برای ذخیره داده‌های  SQL Server استفاده می‌شود. برای انجام این کار، ستون نام فیزیکی جدول sys.master_files system را برای درایوهایی که مورد استفاده قرار می‌گیرند، بررسی می‌کند.
۴- اگر رویه، درایوی را که برای ذخیره داده‌های SQL Server یا فایل‌های گزارش به کار می‌رود را بیابد و درصد فضای خالی درایو زیر پارامتر  @minFreeDrvPctAllowed  باشد، یک پیام هشدار چاپ می‌کند و آن را در SQL Server Error Log و در Windows Event Log با استفاده از رویه ذخیره شده گسترش یافته xp_logevent گزارش می‌نماید.
T-SQL مربوط به این رویه ذخیره شده:

USE [master]
GO

CREATE PROC dbo.usp_AlertOnDBSpace (@minFreeDrvPctAllowed DECIMAL(5, 1) = 30.0)
AS
BEGIN
SET NOCOUNT ON

DECLARE @localDrivesSpaceInfo TABLE (
idx INT IDENTITY
,DriveLetter CHAR(1)
,FreeSpaceGB FLOAT
,TotSizeGB FLOAT
,pctFree AS round(100.0 * FreeSpaceGB / TotSizeGB, 1)
);
DECLARE @infoFromPS TABLE (data VARCHAR(200));
DECLARE @i INT = 0
DECLARE @tblen INT
DECLARE @currDrv CHAR(1)
DECLARE @currDrvPct DECIMAL(5, 1)
DECLARE @freeGB FLOAT
DECLARE @totGB FLOAT
DECLARE @warnMsg VARCHAR(128)

INSERT INTO @infoFromPS
EXEC xp_cmdshell ‹Powershell.exe «Get-WMIObject Win32_LogicalDisk -filter «DriveType=3»| Format-Table DeviceID,FreeSpace,Size»›;

DELETE
FROM @infoFromPS
WHERE data IS NULL
OR data LIKE ‹%DeviceID%›
OR data LIKE ‹%—-%›
OR len(rtrim(ltrim(data))) < 3;

INSERT INTO @localDrivesSpaceInfo
SELECT LEFT(data, 1) AS driveLetter
,ltrim(rtrim(substring(data, 3, len(data) – 2 – charIndex(‹ ‹, REVERSE(data), 1)))) AS FreeSpaceGB
,right(data, charIndex(‹ ‹, REVERSE(data), 1) – 1) AS TotSizeGB
FROM @infoFromPS

SELECT @tblen = count(*)
FROM @localDrivesSpaceInfo

SET @i = @i + 1

WHILE @i <= @tblen
BEGIN
SELECT @currDrv = DriveLetter
,@currDrvPct = pctFree
,@freeGB = ROUND(FreeSpaceGB / POWER(1024, 3), 1)
,@totGB = ROUND(TotSizeGB / POWER(1024, 3), 1)
FROM @localDrivesSpaceInfo
WHERE @i = idx;
IF (
@currDrv IN (
SELECT DISTINCT left(upper(rtrim(ltrim(t.physical_name))), 1) AS DB_Drive
FROM sys.master_files t
)
)
AND (@currDrvPct < @minFreeDrvPctAllowed)
BEGIN
SET @warnMsg = CONCAT (
‹Warning! – Drive «›
,@currDrv
,›» has ‹
,rtrim(ltrim(Str(@currDrvPct)))
,›% of disk space left!›
,› (‹
,rtrim(ltrim(Str(@freeGB)))
,› GB free out of ‹
,rtrim(ltrim(Str(@totGB)))
,› GB ) ‹
)
SELECT @warnMsg AS msg
— log it in viewer and in srv error logs
EXEC xp_logevent 60000
,@warnMsg
,warning
END

SET @i = @i + 1
END
END
GO

با وارد کردن مقدار درصد، این رویه مورد استفاده قرار می‌گیرد؛ در غیر این صورت از پیش فرض ۳۰ درصد که ما در رویه ذخیره شده نوشته‌ایم استفاده خواهد کرد. در اجرای این نمونه، یک درایو C  در سرور محلی با ۲۵ درصد فضای خالی داریم.
و اما ببینیم که چگونه از این رویه با پیش فرض ۳۰ درصد استفاده کنیم.

use master
go
exec dbo.usp_AlertOnDBSpace
go

بعد از استفاده از دستورها، پیام زیر را در سه جا دریافت کردیم:
۱- در کادر سمت راست در SSMS
۲- در SQL Server Error Log فعلی
۳- پیام، در Windows Event Viewer در Windows Application Log به صورت یک هشدار است.
Warning! – Drive «C» has 25% of disk space left! (75 GB free out of 298 GB)
توجه: این اسکریپت در SQL Server 2012  و ۲۰۱۴ Developer مورد آزمایش قرار گرفته است.

نظر بدهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

It is main inner container footer text