Monitor Disk Space on Multiple SQL Servers

Monitor Disk Space on Multiple SQL Servers

In the typical IT department, an un-avoidable task is to monitor the disk space on all drives on certain servers. In addition, the methods presented here will help in monitoring the growth of files. In this article, I am going to discuss three different ways to monitor disk space on a list of servers and store the output either in a .CSV file or on a database table.

Method 1:

Check the drive space on all of the servers listed in a text file and create a .csv output file with the Server name, Drive names, Disk Space and Free space.

Let's say we have 3 servers and need to monitor the disk space on all of the hard disk drives on those servers.

Step1: Create a text file c:\ computerlist.txt with a list of server names

Example:

SQL2K
YUKON
DOTNET

Step2: Copy and paste the code below into c:\DiskSpacetoCSV.vbs

'Objective: Find Disk Free Space in all the listed servers and write to a .csv file

'Author: MAK

'Contact: mak_999@yahoo.com

Set iFSO = CreateObject("Scripting.FilesyStemObject")

Set oFSO = CreateObject("Scripting.FilesyStemObject")

InputFile="c:\computerlist.txt"

Outputfile="c:\Freespacelist_" + cstr(Month(now()))+"_"+cstr(day(now()))+".csv"

Set ofile = ofso.createTextFile(OutputFile, True)

Set ifile = iFSO.OpenTextFile(inputfile) 

Const MBCONVERSION= 1048576 

ofile.writeline "Computer,Drive,Disk Size,FreeSpace,%"

Do until ifile.AtEndOfLine

Computer       = ifile.ReadLine

Set objWMIService = GetObject("winmgmts://" & Computer) 

Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk") 

For Each objLogicalDisk In colLogicalDisk 

   if objLogicalDisk.drivetype=3 then

ofile.writeline Computer & "," & objLogicalDisk.DeviceID &_

 "," &  objLogicalDisk.size/MBCONVERSION & "," &_

 objLogicalDisk.freespace/MBCONVERSION & "," &_

 ((objLogicalDisk.freespace/MBCONVERSION)/(objLogicalDisk.size/MBCONVERSION))*100

   end if

Next 

Loop

Step3: Execute c:\DiskSpacetoCSV.vbs

When we execute this VB script, it will create a file c:\Freespacelist_12_27.csv that will contain details similar to those below.

SQL SERVER – Checkpoint

 SQL SERVER – Checkpoint

Checkpoint is the process that writes all dirty pages to disk for the current database. Checkpoint process help to minimize time during a later recovery by creating a point where all dirty pages have been written to disk.

    Checkpoints can be performed concurrently on the databases
    If a checkpoint fails or is interrupted and a recover is required, the database engine can not recover from the “failure” checkpoint, is necessary to recover from the last successful checkpoint.
    The database engine performs any modification to database pages in memory (for performance reasons) it cause dirty pages, those dirty pages are not written to disk on each modification performed, those are written to disk just when a checkpoint occur.
    When checkpoints occur?
        Before a backup, the database engine performs a checkpoint, in order that all the changes to database pages (dirty pages) are contained in the backup.
        Stopping the server using any of the following methods, they it cause a checkpoint.
            Using Shutdown statement,
            Stopping SQL Server through SQL Server configuration, SSMS, net stop mssqlserver and ControlPanel-> Services -> SQL Server Service.
            When the “SHUTDOWN WITH NOWAIT” is used, it does not execute checkpoint on the database.
        When the recovery internal server configuration is accomplished. This is when the active portion of logs exceeds the size that the server could recover in amount of time defined on the server configuration (recovery internal).
        When the transaction log is 70% full and the database is in truncation mode.
            The database is in truncation mode, when is in simple recovery model and after a backup statement has been executed.
    The time required to perform a checkpoint depends directly of the amount of dirty pages that the checkpoint must write.

[Questions & Answers]

Q: What is Dirty Page?.
A: Dirty pages are the data pages that exists on the buffer cache and have been modified, but not yet written to disk.

Q: What is the checkpoint syntax?
A:

Checkpoint [duration_time]

Duration_time:
    Time desired to perform the checkpoint process.
    Is specified in seconds.
    Must be greater than 0.
    When is omitted, the database engine adjust automatically the duration time to minimize the performance impact.
    Depending of the duration defined, is the amount of resources that SQL Servers assign to checkpoint process. Ex. If the duration_time is defined in 10 seconds but the checkpoints normally is going to take 20 seconds, SQL Server assigns more resources with performance impact in order to accomplish the 10 seconds defined. In the other hand, if the checkpoint is going to take 5 seconds, SQL Server assigns fewer resources than would be assigned by default. Checkpoint process usually takes more/less time than the specified.