SQL Script to get Disk Space details

[Technical Editor]

This SQL script is designed to query and display information about the disk space on the server where your SQL Server instance is running. It uses a combination of T-SQL and PowerShell commands executed through xp_cmdshell to gather disk space details. Here’s a breakdown of its functionalities and usage:

 

SQL Server Disk Space Query Script

This script queries and displays disk space information for SQL Server:

DECLARE @srvName VARCHAR(255)
DECLARE @sql VARCHAR(400)
DECLARE @output TABLE (line VARCHAR(255))

-- Default server name, can be set to another server name
SET @srvName = @@SERVERNAME 
IF CHARINDEX ('\', @srvName) > 0
    SET @srvName = SUBSTRING(@srvName, 1, CHARINDEX('\',@srvName)-1)

SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@srvName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | SELECT name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576/1024+''%''+$_.freespace/1048576/1024+''*''}"'

-- Inserting disk name, total space, and free space value into temporary table
INSERT @output
EXEC xp_cmdshell @sql

-- Retrieve the values in GB from PowerShell Script output
SELECT @@servername as Hostname, RTRIM(LTRIM(SUBSTRING(line,1,CHARINDEX('|',line) -1))) AS drivename
      ,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) AS FLOAT),0) AS 'capacity(GB)'
      ,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) AS FLOAT),0) AS 'freespace(GB)'
      ,CAST (((ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) AS FLOAT),0)))*100/
      (ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) AS FLOAT),0)) AS INT) AS 'freespace %'
FROM @output
WHERE line LIKE '[A-Z][:]%'
ORDER BY drivename
        

 

Script Breakdown and Usage

1. Declaring Variables and Table:
   • @srvName: Holds the server name. By default, it’s set to the current server name (@@SERVERNAME), but you can modify it to target a different server.
   • @sql: Stores the PowerShell command to be executed.
   • @output: A table variable to temporarily hold the results returned by the PowerShell command.
2. Setting the Server Name:
   • If @srvName contains a backslash (indicating a server instance), the script extracts just the server’s name.
3. PowerShell Command:
   • The PowerShell command (Get-WmiObject) queries Win32_Volume class objects on the specified server.
   • It filters for drives (DriveType = 3) and selects their name, capacity, and free space.
   • The output format is: drive name, total capacity (in GB), and free space (in GB).
4. Executing PowerShell via xp_cmdshell:
   • xp_cmdshell executes the PowerShell command.
   • The results are inserted into the @output table.
5. Parsing and Displaying Results:
   • The script parses each line of the @output table to extract drive name, capacity, free space, and calculates the free space percentage.
   • It uses string manipulation functions like SUBSTRING, CHARINDEX, RTRIM, and LTRIM.
   • The final output includes the server name (Hostname), drive name, total capacity in GB, free space in GB, and free space as a percentage.
6. Usage:
   • This script is used for monitoring disk space on SQL Server machines.
   • It’s helpful for database administrators to ensure sufficient disk space is available and to plan for storage capacity.
   • It can be incorporated into larger monitoring or maintenance scripts or run as a standalone query for immediate disk space insights.

Points to Consider

• Permissions: Ensure xp_cmdshell is enabled and the account executing the script has the necessary permissions.
• Security: Be cautious with xp_cmdshell as it can execute system-level commands and may pose a security risk.
• Compatibility: The script is designed for environments where both SQL Server and PowerShell are available and properly configured.
• Server Access: The script assumes access to the target server for querying WMI objects.

Overall, this script is a valuable tool for SQL Server DBAs to monitor server disk space directly from within SQL Server, particularly useful in environments where DBAs have more access to SQL Server than the underlying operating system.

[Author]

Posts