Monitoring Disk Space with SQL Server and PowerShell via SQL Agent Job
Abstract
Monitoring disk space utilization of server(s) is the critical and important job for any administrator. Keeping things organized might improve application availability and server availability. The recent study reveals that 24.45%the administrators manually performing disk space monitoring operations on daily basis. There may be many reasons for manually doing these tasks. Have written series of disk monitoring article to understand and use the existing tools and utilities to automate this critical tasks.
Introduction
This article shows one possible solution for monitoring disk space usage using SQL Server and PowerShell script executed via SQL Agent Job. When you have to run a PowerShell command across multiple servers, you will frequently see examples in which the list of servers are stored in a text file and read by using the Get-Content cmdlet. My other post is an example of using text or CSV file as input for server traversing.However, most database professionals maintain a list of SQL Servers they manage in either a table they create. Instead of using a text file, have used SQL table to fetch the database servers. Let’s take a look at an example. The disk usage details are stored in a repository. The input list of the servers is also getting from the table. The metrics can be later used for forecasting and capacity planning. It also shows the way to query win32 class libraries and capture the details in a repository. The template can be used to monitor and gather other details which are available from various win32 class libraries.
Step by Step Details
Discusses in detailed step to set up the SQL agent job which gathers the disk usage metric as per the schedule frequency
Create Demo database
- Connect to the Database Engine.
- From the Standard bar, click New Query.
- Execute the below SQL. Change the values as per the requirement and setup
USE master ;
GO
CREATE DATABASE PowerSQL
ON
( NAME = PowerSQL_dat,
FILENAME = 'F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PowerSQL_dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = PowerSQL_log,
FILENAME = 'G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PowerSQL_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO
Getting the server list
In the section, the table winServers is created. It in-houses the list of the server names along with its status. The dummy data is inserted for demonstration. The table is queried to get an active list of servers for data gathering.
CREATE TABLE [dbo].[WinServers](
[ServerName] [varchar](128) NOT NULL,
[status] [varchar](10) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[WinServers] values('HQDBSP18','Yes'),('APMESDP02','YES')
GO
SELECT * FROM [dbo].[WinServers]
GO
The active list of servers is selected using the below SQL
SELECT UPPER(ServerName) ServerName,Status FROM dbo.WinServers
Repository to Store to Disk Usage details
The table TLOG_DiskDetails acts as a repository for disk utilization. The columns of this table are self-explanatory. The constraint logDate is default constraint which stores the date of every query execution.
CREATE TABLE [dbo].[TLOG_DiskDetails](
[servername] [varchar](100) NULL,
[DriveName] [varchar](3) NULL,
[DriveLabel] [varchar](100) NULL,
[TotalCapacity] [decimal](10,2) NULL,
[UsedCapacity] [decimal](10, 2) NULL,
[FreeSpace] [decimal](10, 2) NULL,
[FreeSpacePercentage] [int] NULL,
[logDate] [date] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TLOG_DiskDetails] ADD DEFAULT (CONVERT([varchar](10),GETDATE(),(112))) FOR [logdate]
GO
Getting the disk space details
The Get-ServerDiskStatus function which collects disk space statistics. We now need to gather extra information out of the properties of Win32_logicaldisk. The output is then fed to wrtiteDiskInfo function to calculate Total Capacity and Free Space Percentage. We can do everything in the same function. Just to simplify the code for better understanding have split the code into multiple modules.
Function Get-ServerDiskStatus ($SQLInstance)
{
$dp = Get-WmiObject win32_logicaldisk -ComputerName $SQLInstance| Where-Object {$_.drivetype -eq 3}
foreach ($item in $dp){
writeDiskInfo $SQLInstance $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size }
}
The below section code fetches the server list from the table on a central server HQDBST11 and PowerSQL database. The below parameter has to be changed as per your requirement.
The server HQDBSTT11 and PowerSQL are the server and database which houses server list under winServers table.
#Connection information for the database server where the SQLInstances and DatabaseBackups tables reside
$params = @{'server'='HQDBSTT11';'Database'='PowerSQL'}
#Grab our list of servers, iterate through them and call the function which rights to the database
$Srv = invoke-sqlcmd @params -Query "SELECT ServerName from dbo.WinServers where status='Yes'"
foreach ($Instance in $srv)
{
Get-ServerDiskStatus $Instance.ServerName
}
After manipulation, the disk usage statistics are inserted into the TLOG_DiskDetails. The server and database name parameters are declared and its passed to invoke-sqlcmd for further processing
$InsertResults = @"
INSERT INTO [PowerSQL].[dbo].[TLOG_DiskDetails](servername,DriveName,DriveLabel,TotalCapacity,UsedCapacity,FreeSpace,FreeSpacePercentage)
VALUES ('$SERVER','$devId','$volName',$totSpace,$usedSpace,$frSpace,$freePercent)
"@
invoke-sqlcmd @params -Query $InsertResults
}
Download
https://gallery.technet.microsoft.com/Disk-Space-with-SQL-Server-585d1421
Code
Change the parameters list as per your requirement. The Server and Database are the two parameters that are fed to the SQL Agent script
Code of SQL Agent job step
#Connection information for the database server where the SQLInstances and DatabaseBackups tables reside
$params = @{'server'='HQDBST11';'Database'='PowerSQL'}
Function writeDiskInfo
{
param($server,$devId,$volName,$frSpace,$totSpace)
try
{
if($totSpace -ne 0 -or $frSpace -ne 0)
{
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=[Math]::Round(($frSpace/1073741824),2)
$usedSpace = $totSpace - $frspace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = ($frspace/$totSpace)*100
$freePercent = [Math]::Round($freePercent,0)
}
else
{
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=0
$usedSpace = $totSpace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = 0
}
}
catch
{
$freePercent=0
}
$InsertResults = @"
INSERT INTO [PowerSQL].[dbo].[TLOG_DiskDetails](servername,DriveName,DriveLabel,TotalCapacity,UsedCapacity,FreeSpace,FreeSpacePercentage)
VALUES ('$SERVER','$devId','$volName',$totSpace,$usedSpace,$frSpace,$freePercent)
"@
invoke-sqlcmd @params -Query $InsertResults
}
Function Get-ServerDiskStatus ($SQLInstance)
{
$dp = Get-WmiObject win32_logicaldisk -ComputerName $SQLInstance| Where-Object {$_.drivetype -eq 3}
foreach ($item in $dp)
{
writeDiskInfo $SQLInstance $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size
}
}
#Grab our list of servers, iterate through them and call the function which rights to the database
$Srv = invoke-sqlcmd @params -Query "SELECT ServerName from dbo.WinServers where status='Yes'"
foreach ($Instance in $srv)
{
Get-ServerDiskStatus $Instance.ServerName
}
Complete Code
If you don't wish to use the default setup then change the server and database name in the below code and execute it over SSMS.
The following code creates
- Demo database PowerSQL
- Create table Winservers
- Insert dummy data into WinServers
- Create repository TLOG_DiskDetails to gather disk metrics
- SQL job script
- Schedule the job as per your requirement
CREATE DATABASE [PowerSQL]
Go
USE [PowerSQL]
GO
CREATE TABLE [dbo].[WinServers](
[ServerName] [varchar](128) NOT NULL,
[status] [varchar](10) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[WinServers] values('HQDBSP18','Yes'),('APMESDP02','YES')
GO
SELECT * FROM [dbo].[WinServers]
GO
CREATE TABLE [dbo].[TLOG_DiskDetails](
[servername] [varchar](100) NULL,
[DriveName] [varchar](3) NULL,
[DriveLabel] [varchar](100) NULL,
[TotalCapacity] [decimal](5, 2) NULL,
[UsedCapacity] [decimal](5, 2) NULL,
[FreeSpace] [decimal](5, 2) NULL,
[FreeSpacePercentage] [int] NULL,
[logDate] [date] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TLOG_DiskDetails] ADD DEFAULT (CONVERT([varchar](10),GETDATE(),(112))) FOR [logdate]
GO
SELECT * FROM [TLOG_DiskDetails]
GO
USE [msdb]
GO
/****** Object: Job [PowerSQL-DiskStatus] Script Date: 10/24/2016 15:51:14 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 10/24/2016 15:51:14 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'PowerSQL-DiskStatus',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [DiskStatus] Script Date: 10/24/2016 15:51:14 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DiskStatus',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'#Connection information for the database server where the SQLInstances and DatabaseBackups tables reside
$params = @{''server''=''HQDBST11'';''Database''=''PowerSQL''}
Function writeDiskInfo
{
param($server,$devId,$volName,$frSpace,$totSpace)
try
{
if($totSpace -ne 0 -or $frSpace -ne 0)
{
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=[Math]::Round(($frSpace/1073741824),2)
$usedSpace = $totSpace - $frspace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = ($frspace/$totSpace)*100
$freePercent = [Math]::Round($freePercent,0)
}
else
{
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=0
$usedSpace = $totSpace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = 0
}
}
catch
{
$freePercent=0
}
$InsertResults = @"
INSERT INTO [PowerSQL].[dbo].[TLOG_DiskDetails](servername,DriveName,DriveLabel,TotalCapacity,UsedCapacity,FreeSpace,FreeSpacePercentage)
VALUES (''$SERVER'',''$devId'',''$volName'',$totSpace,$usedSpace,$frSpace,$freePercent)
"@
invoke-sqlcmd @params -Query $InsertResults
}
Function Get-ServerDiskStatus ($SQLInstance)
{
$dp = Get-WmiObject win32_logicaldisk -ComputerName $SQLInstance| Where-Object {$_.drivetype -eq 3}
foreach ($item in $dp)
{
writeDiskInfo $SQLInstance $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size
}
}
#Grab our list of servers, iterate through them and call the function which rights to the database
$Srv = invoke-sqlcmd @params -Query "SELECT ServerName from dbo.WinServers where status=''Yes''"
foreach ($Instance in $srv)
{
Get-ServerDiskStatus $Instance.ServerName
}
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Conclusion
The effort is to show the details of gathering the disk metrics, the one of the important system measurement parameter using SQL. The deactivation is made simple. The available metrics play a major role in Capacity planning and forecasting process and its made very simpler. This proactive monitoring may avoid unforeseen disk space issues. You can set up a proxy account to run PoSH script. The above script uses the SQL agent service account to query all the remote servers.
References
The below section details the reference links
TechNet
External Blog
See Also
- PoSH - DiskSpace GUI Tool
- PowerShell : CSV - Disk Space Report - CSV
- PoSH- Disk Space - HTML Report
- PowerShell : CSV - Disk Space Report - HTML
- PowerShell : CSV - Disk Space Report - Excel
- Disk-space-GUI-tool-multi-server-s-search-grid-and-email-output-PowerSQL
- Run Windows PowerShell Steps in SQL Server Agent