Part of the database and system administrator’s daily routine consists of monitoring disk usage. It can be achieved manually, however, it is much more logical to do this automatically. Even better – the automatic script can warn only when a specific space threshold on the specific drive has left.
In this article, we are going to share free disk usage alert and explain how to use it, and how it works internally. All you need to get it done is access to monitored servers and PowerShell.
By the way, this check is a part of our SQL Server Health Check service.
Prerequisites
Two important things before we begin:
- By default, the current account that will run the script must be a member of the Administrators group on the remote computer;
- Execution Policy on Powershell should be set to RemoteSigned. To check that and change if needed, these steps should be followed:
- Open Powershell as Administrator. To do this, click on the “Start” button, type PowerShell. Then right-click the mouse on Windows PowerShell ISE (simple Windows PowerShell will also work in this step), and choose Run as administrator as it is shown in Figure 1.
- When the window opens, enter Get-ExecutionPolicy and hit enter. If it is not Unrestricted or RemoteSigned, enter Set-ExecutionPolicy RemoteSigned. You will be asked if you are sure. Enter Y and hit enter.
Now Powershell is ready to run monitoring script. Let’s analyze that.
Script Settings
To begin with, download and open DiskSpaceMonitoring.ps1 script and Settings.csv file. Look at the top part of the script – settings:
Now open the Settings.csv file, using your favorite text editor (Notepad works fine). Do not edit the first row (header), just add new rows (or edit existing ones) below that contains Computer Name, Drive, and Drive capacity threshold. You can add as many servers and drives as you need just separate all values by a comma. There is an example in Figure 2 below.
Nothing else should be changed inside the script. It will automatically iterate the settings list, generate HTML text, and sent it via email to the recipients’ list. An example of email message content is in Figure 3 below.
The next step would be to schedule this script so it can run automatically. This can be done at least in two ways:
- Using Task scheduler
- Using Microsoft SQL Server Agent jobs (this requires SQL Server version, higher than Express edition, installed)
In this article, we will describe the first option. At first, open start, type Task Scheduler, and hit enter, as it is shown in Figure 4.
When the Task scheduler window opens, click Create Task… button. On General tab fill in Name, Description, pick Run whether the user is logged on or not and pick Run with highest privileges. On triggers tab click New, choose preferred schedule (example – Daily at 8 am), and tick the box Synchronize across time zones, click OK. Then go to Actions tab, click New, and fill in Program/script by text Powershell and Add arguments (optional) by –file E:\Scripts\DiskSpaceMonitoring.ps1 (change path where PS script is located at). Example screenshot in Figure 5. After those boxes are filled, click OK twice.
Before start using this script in production, we recommend to set high Threshold values – to make sure no firewalls or spam filters are blocking email and all permissions are in place.
