A while back, I wrote an article that appeared on SQLServerCentral.com that presented a routine to gather disk usage data for all the SQL Servers in an organization. One of the pieces of data that routine gathered was the size of the SQL Server data and log files and the total size of the drives they resided on. This was useful information, but if you wanted to calculate the percentage of total drive space used, it could possibly be inaccurate. If programs other than SQL Server used that drive, they could eat up all the disk space and this routine would not detect it. This made the nice little gauges I made for my report somewhat misleading.
I’ve revisited this routine and have added code to collect the necessary data to determine the total disk used amount. In retrospect, this was remarkably easy to do and I should have included this in the first pass. In my defense though, this was my first Powershell project and I was (and am) still learning.
I will assume you have already read my article on SQL Server Central and have used the scripts there to create the tables, logins, etc.
The additional data I will collect is the drive free space. To store this, we need to add a column to the DatabaseDiskStats table:
ALTER TABLE DatabaseDiskStats ADD DriveFreeSpace_in_MB int null
Now we need to modify the routine to collect this data. This is done by adding some code to the Powershell script. I created a new variable in the script called $DriveFreeSpace. I convert this number from the bytes it is reported in to megabytes and then write it to the table at the same time the MaxDriveSize_in_MB value is written. The entire modified script is:
# --------------------------------------------------------------------------- ### Author: ### Shaun Stuart ### www.shaunjstuart.com # --------------------------------------------------------------------------- $DiskStatsServer = '<your servername here>' $DiskStatsDB = '<your db name here>' $SelectQuery = "SELECT DISTINCT server_name, Drive_letter FROM DatabaseDiskStats WHERE MaxDriveSize_in_MB is NULL" ####################### ## MAIN ## ####################### $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=$DiskStatsServer;Database=$DiskStatsDB;Integrated Security=True" # Can't issue the update command via the same connection as the SELECT because of the DataReader loop, so we need a new connection $SqlUpdConnection = New-Object System.Data.SqlClient.SqlConnection $SqlUpdConnection.ConnectionString = "Server=$DiskStatsServer;Database=$DiskStatsDB;Integrated Security=True" $SqlConnection.open() $SQLUpdConnection.open() $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SelectQuery $SqlCmd.Connection = $SqlConnection $SqlUpdCmd = New-Object System.Data.SqlClient.SqlCommand $SqlUpdCmd.Connection = $SqlUpdConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $ds = $SqlCmd.ExecuteReader() while ($ds.Read()) { $Servername = $ds.GetValue(0) $DriveLetter = $ds.GetValue(1) $Driveobject=gwmi win32_logicaldisk -computername $servername -filter "DeviceID='$DriveLetter'" $DriveSize=$Driveobject.size $DriveSize=[int64]$DriveSize/[int64]1024/[int64]1000 $DriveSize = [math]::Round($DriveSize,0) $DriveFreeSpace=$Driveobject.freespace $DriveFreeSpace=[int64]$DriveFreeSpace/[int64]1024/[int64]1000 $DriveFreeSpace = [math]::Round($DriveFreeSpace,0) echo "The server $Servername drive $DriveLetter is $DriveSize MB" echo "The server $Servername drive $DriveLetter has $DriveFreeSpace MB free" $UpdateQuery = @" UPDATE DatabaseDiskStats SET MaxDriveSize_in_MB = '$DriveSize' ,DriveFreeSpace_in_MB = '$DriveFreeSpace' WHERE Server_name = '$ServerName' AND Drive_letter = '$DriveLetter' AND MaxDriveSize_in_MB is NULL "@ $SqlUpdCmd.CommandText = $UpdateQuery $SqlUpdCmd.ExecuteNonQuery() } $ds.Close() $SqlConnection.Close() $SqlUpdConnection.Close()
Replace your existing DBDiskSpaceAnalysis.ps1 file with the above code and save it (after you’ve filled in your server name and database name in the first couple of lines). Now the routine will start collecting how much free space is left on the disks.
The final step is to incorporate this new data into the report and change the gauges to use it. I decided to update the gauges on my report by adding an additional pointer. The needle pointer still shows the percentage of the disk drive that the SQL files are taking up. But I have added a bar pointer that goes around the outside of the gauge to indicate how full the drive is overall:
The above image shows gauges for two of my servers The top row of gauges shows SQL Server files pretty much make up all the data on the drives for one server (recall, in my report, the different pointer colors indicate different drives). The lower row shows that on the other server, the data drive is being shared with something else – the drive is about 15% full but SQL Server files only account for about 1 or 2 percent of that.
In the comments on my initial post about this report, I gave the query I used as the data source for the gauges in my report. Here is the updated version:
SELECT server_name, Drive_letter, SUM(size_in_mb) as DBSize_in_MB, statDate, MAX(maxdrivesize_in_mb) as TotalDriveSize_in_MB, MAX(drivefreespace_in_mb) as TotalFreeSpace_in_MB, (SUM(CAST(size_in_mb as decimal (9,2)))/MAX(CAST(maxdrivesize_in_mb as decimal(9,2)))) * 100 as SQLPercentFull, ((MAX(CAST(maxdrivesize_in_mb as decimal(9,2)))- MAX(CAST(drivefreespace_in_mb as decimal(9,2))))/MAX(CAST(maxdrivesize_in_mb as decimal(9,2)))) * 100 as TotalPercentFull FROM DatabaseDiskStats WHERE statDate = CONVERT([date],GETDATE(),(0)) AND MaxDriveSize_in_MB <>0 GROUP BY Server_name, Drive_Letter, StatDate ORDER BY drive_letter
Recall, there is a filter on each gauge so that it only looks at the server and drive it is representing.
Now the report is a little more useful.
Free Added Bonus!
As a bonus for reading this far, here’s an additional stored procedure, provide at no cost to you! While I was creating this report, I realized I could use this new bit of data to send out an alert when a disk reaches a certain percentage full. I know, I get the report emailed every day which includes this data, but it is precisely because I get it everyday that I want an additional alert. Human nature being what it is, I’m sure I will eventually start to just casually glance at the daily report. It is entirely possible that I could miss seeing a drive that is approaching being full.
To help me catch these cases, I made the below stored procedure. It takes two input parameters, although defaults are used, so you don’t even have to supply those. The first parameter, @WarningThreshold, is the percentage full a drive has to be at or above in order for an alert to be sent out. The second parameter, @AlertEmail, is the email address an alert will be sent to. Of course, this procedure assumes you have database mail enabled.
CREATE PROCEDURE SQLDriveSpaceWarningEmail @WarningThreshold decimal(4,2) = '90', @AlertEmail varchar(200) = '<enter your default email address here>' AS DECLARE @AlertSubject varchar(200) DECLARE @AlertBody varchar(3000) DECLARE @Server_name varchar(100) DECLARE @Drive_letter char(2) DECLARE @SQLPercentFull decimal(4,2) DECLARE @TotalPercentFull decimal(4,2) SET @AlertSubject = 'ALERT!!! SQL DRIVE SPACE WARNING ON SERVER ' DECLARE OverThresholdCursor CURSOR FOR SELECT server_name, Drive_letter, (SUM(CAST(size_in_mb as decimal (9,2)))/MAX(CAST(maxdrivesize_in_mb as decimal(9,2)))) * 100 as SQLPercentFull, ((MAX(CAST(maxdrivesize_in_mb as decimal(9,2)))- MAX(CAST(drivefreespace_in_mb as decimal(9,2))))/MAX(CAST(maxdrivesize_in_mb as decimal(9,2)))) * 100 as TotalPercentFull FROM DatabaseDiskStats WHERE statDate = CONVERT([date],GETDATE(),(0)) AND MaxDriveSize_in_MB <>0 GROUP BY Server_name, Drive_Letter, StatDate HAVING (((MAX(CAST(maxdrivesize_in_mb as decimal(9,2)))- MAX(CAST(drivefreespace_in_mb as decimal(9,2))))/MAX(CAST(maxdrivesize_in_mb as decimal(9,2)))) * 100) >= @WarningThreshold ORDER BY server_name,drive_letter OPEN OverThresholdCursor FETCH NEXT FROM OverThresholdCursor INTO @Server_name, @Drive_letter, @SQLPercentFull, @TotalPercentFull WHILE @@fetch_status = 0 BEGIN SET @AlertSubject = @AlertSubject + UPPER(@Server_name) + '!!!' SET @AlertBody = 'Drive ' + @Drive_letter + ' on SQL Server ' + @Server_name + ' is ' + cast(@TotalPercentFull as varchar(5)) + '% full. SQL Server files are using ' + cast(@SQLPercentFull as varchar(5)) + '% of the disk.' exec msdb..sp_send_dbmail @recipients = @AlertEmail, @body = @AlertBody, @subject = @AlertSubject FETCH NEXT FROM OverThresholdCursor INTO @Server_name, @Drive_letter, @SQLPercentFull, @TotalPercentFull END CLOSE OverThresholdCursor DEALLOCATE OverThresholdCursor
I added the execution of this stored procedure as the last step of the job that collects the data.
You all should have alerts set up for severity 17 errors (the severity which includes out of disk space errors). But by the time you get that alert, your drives are already full and SQL Server will likely have come to a halt. Using this routine, you can be notified before a drive is filled so you can take the steps needed to provide more space before the problem escalates into an emergency.
UPDATE: I found a bug in the stored procedure of this routine that may result in the @@SERVERNAME system variable getting set to NULL after a restart. More details and a fix here.
This is GREAT! Thank you!
Found a bug where if the @servername starts with a number, e.g. 1SQLServer, it will throw the following error:
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ‘1’.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ‘1’.
(0 row(s) affected)
Eugene – I am unable to duplicate this issue. I created a VM named 1SQLSVR, installed SQL 2008 R2 on it, and loaded my routines. Everything worked correctly.