مونيتور كردن ميزان فضاي خالي باقيمانده در سرور توسط اس كيوال سرور

با توجه به در حال اجرا بودن 24 ساعته‌ي سرويس SQL server agent، استفاده‌هاي ارزنده‌اي از آن مي‌توان كرد. براي مثال هر از گاهي بررسي كند كه آيا هارد سرور پر شده يا نه؟ و اگر بله (كمبود ميزان فضاي خالي به حد خطرناكي رسيده)، يك ايميل خودكار به مسؤول مربوطه ارسال كند.
عمده‌ي مطالبي كه در اين مقاله بررسي خواهند شد همانند مطلب مونيتور كردن ميزان مصرف CPU توسط اس كيوال سرور است و از تكرار آن‌ها در اين‌جا صرفنظر خواهد شد (راه اندازي ديتابيس ميل و همچنين تعريف يك job جديد كه در مورد آن‌ها صحبت شد، همانند قبل است). تنها مطلب جديدي كه به آن اشاره خواهد شد، اسكريپت بررسي ميزان فضاي خالي و سپس ارسال ايميل است كه در يك job جديد همانند مقاله‌ي قبل بايد به سرور اضافه شود. اين اسكريپت به شرح زير است:

DECLARE @DriveBenchmark INT
DECLARE @MachineName NVARCHAR(1000)
DECLARE @DiskFreeSpace INT
DECLARE @DriveLetter CHAR(1)
DECLARE @AlertMessage NVARCHAR(MAX)
DECLARE @MailSubject NVARCHAR(MAX)
DECLARE @NewLine CHAR(2)

SET @NewLine = CHAR(13) + CHAR(10)

SET @DriveBenchmark = 2048 -- 2GB
SET @MailSubject = 'Free space is low on ' + @@SERVERNAME
SET @AlertMessage = ''

IF EXISTS (
     SELECT *
     FROM   tempdb..sysobjects
     WHERE  id = OBJECT_ID(N'[tempdb]..[#disk_free_space]')
 )
  DROP TABLE #disk_free_space
 
CREATE TABLE #disk_free_space
(
 DriveLetter  CHAR(1) NOT NULL,
 FreeMB       INTEGER NOT NULL
)
 
/* Populate #disk_free_space with data */
INSERT INTO #disk_free_space
EXEC MASTER..xp_fixeddrives
 
 
DECLARE DriveSpace CURSOR FAST_FORWARD
FOR
  SELECT DriveLetter,
         FreeMB
  FROM   #disk_free_space

 OPEN DriveSpace
 FETCH NEXT FROM DriveSpace INTO @DriveLetter, @DiskFreeSpace
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
  IF @DiskFreeSpace < @DriveBenchmark
  BEGIN
      SET @AlertMessage = @AlertMessage + 'Drive ' + @DriveLetter + ' on ' + @@SERVERNAME
          + ' has only ' + CAST(@DiskFreeSpace AS VARCHAR) + ' MB left.' + @NewLine
  END

  FETCH NEXT FROM DriveSpace INTO @DriveLetter, @DiskFreeSpace
END
 CLOSE DriveSpace 
 DEALLOCATE DriveSpace
  
 DROP TABLE #disk_free_space

IF @AlertMessage <> ''
BEGIN
  EXECUTE msdb.dbo.sp_send_dbmail
  @recipients = 'nasiri@site.net', -- Change This
  @copy_recipients = 'Administrator@site.net', -- Change This
  @Subject = @MailSubject,
  @Body = @AlertMessage
  ,@importance = 'High'
END

بررسي اسكريپت فوق:

همه چيز از رويه‌ي سيستمي xp_fixeddrives شروع مي‌شود. حاصل اجراي اين رويه، دريافت ميزان فضاي خالي هر درايو موجود در سرور خواهد بود. همانطور كه در اسكريپت نيز مشخص است، براي ذخيره سازي خروجي اين رويه، يك جدول موقتي (disk_free_space) ايجاد شده و خروجي آن به درون اين جدول اضافه خواهد شد. سپس يك cursor ايجاد شده و تك تك ركوردهاي حاصل با مقدار متغير DriveBenchmark كه در اينجا 2 گيگابايت در نظر گرفته شده است، مقايسه مي‌گردند. سپس هر كدام از ركوردها كه كمتر از 2 گيگابايت بود، متغير AlertMessage ما را مقدار دهي خواهد كرد. در پايان اگر اين متغير مقدار دهي شده بود، يعني مشكل حاصل شده و نتيجه‌ي بررسي به صورت يك ايميل ارسال مي‌گردد. بديهي است كه در صورت نياز مقدار متغير DriveBenchmark و آرگومان‌هاي recipients و copy_recipients كد فوق بايد اصلاح شوند.

براي استفاده از آن يك job جديد تعريف كنيد كه مثلا هر سه ساعت يكبار اجرا شده و اين اسكريپت را فراخواني نمايد.