Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Monitoring SQL Server Disk Space

MSSQLTips author Mike Eastland By:   |   Read Comments (20)   |   Related Tips: More > Monitoring
Problem

Some DBAs are fortunate enough to have a centralized solution in place to monitor the health of the servers they manage. Many of us rely on home-grown scripts and native tools to perform this vital job function.  One of the most important aspects to monitor is available disk space.  SQL Server provides us with the xp_fixeddrives extended stored procedure, but what if the total amount of free space isn't what you're after?  Let's face it, having 50 GB free on a 100 GB file system is a lot different than the same amount of free space on a 1 TB drive.  There is also the option of executing an external script with xp_cmdshell, but sometimes security policies or DBA preferences prevent the use of xp_cmdshell on a given instance.  The solution provided in this tip uses a combination of native tools to provide a robust method for monitoring local disk space on a SQL Server instance.

Solution

Prerequisite Considerations

  • Be sure to configure database mail and SQL Agent notifications as a prerequisite for proper functionality of this monitoring job.
  • Adjust the Warning and Critical percent free space thresholds, as well as the name of the operator, in the SQL script for the target environment.
  • The script is designed to run as a single unit, so combine the code from all four of the listings into a single script before executing.  Otherwise, the code in listings 2-4 will fail due to missing variables.
  • Since the local disks are being monitored, creating this job for multiple instances on the same server will result in duplicate alerts.
  • This solution assumes that custom error messages 64000 and 64001 are not currently in use by the target instance.  If this is not the case, code changes will be necessary.
  • The script has been tested against SQL Server 2005 SP4, 2008R2 SP2, and 2012 SP1.  Creating the job on other versions may require additional testing and/or tweaks.

Variables

The code in listing 1 declares all of the variables used by the remainder of the script.  As stated in the previous section, edit the user variables as necessary for the target environment.

--Listing 1
USE [msdb]
GO
SET NOCOUNT ON
DECLARE @ReturnCode BIGINT,
 @PctWarning VARCHAR(2),
 @PctCritical VARCHAR(2),
 @JobSubSystem NVARCHAR(16),
 @JobStep NVARCHAR(128),
 @JobOper NVARCHAR(32),
 @JobName NVARCHAR(64),
 @JobID BINARY(16),
 @JobDescr NVARCHAR(128),
 @JobCmd NVARCHAR(MAX),
 @JobCategory NVARCHAR(128),
 @InstanceVersion VARCHAR(16),
 @DebugMessage VARCHAR(MAX),
 @DatabaseName NVARCHAR(12);
 
-- Set values for user variables
SET @PctWarning = '15';
SET @PctCritical = '5';
SET @JobOper = N'sqlDBA';
-- End user variable section

-- Initialize static variables
SET @InstanceVersion = SUBSTRING(CONVERT(VARCHAR(16), SERVERPROPERTY('ProductVersion')), 1, 
  (CHARINDEX('.', (CONVERT(VARCHAR(16), SERVERPROPERTY('ProductVersion')))) - 1));
SET @JobCategory = N'Instance Monitor';
SET @JobDescr = N'Monitor percent free space for local disks.';
SET @JobName = N'Monitor - Disk Space';
SET @JobStep = N'Check for local disks with free space below percentage thresholds.';
SET @ReturnCode = 0;
-- End static variable section

SQL Server Operator

The next section of code checks for the existence of an Operator that matches the name provided in the user variable section. If said Operator is not found, the registry will be searched for a designated failsafe operator. If neither is found, the script will effectively exit.

--Listing 2
-- Check for existence of specified operator; use failsafe operator if it doesn't
IF NOT EXISTS ( SELECT * FROM dbo.[sysoperators] WHERE [name] = @JobOper )
BEGIN
 SET @DebugMessage = 'Operator [' + @JobOper +
 '] not found; checking for failsafe operator.';
  RAISERROR(@DebugMessage, 0, 42) WITH NOWAIT;
 EXEC [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
  N'Software\Microsoft\MSSQLServer\SQLServerAgent',
  N'AlertFailSafeOperator', @JobOper OUTPUT;
END
IF @JobOper IS NULL
BEGIN
 SET @DebugMessage = 'No failsafe operator found; Job [' + @JobName + 
 '] will not be created without notification functionality.';
  RAISERROR(@DebugMessage, 8, 0) WITH LOG, NOWAIT;
   GOTO QuitWithRollback;
END

Custom SQL Server Error Messages and Alerts

Listing 3 creates two custom error messages and associated custom alerts that are used by the disk monitoring job for notifications.

-- Listing 3
-- Create the custom messages
EXEC [master].dbo.sp_addmessage @msgnum = 64000,
 @msgtext = 'WARNING: The following disks have less than %d percent free space: %s',
 @severity = 8, @with_log = 'FALSE', @replace = 'REPLACE';
EXEC [master].dbo.sp_addmessage @msgnum = 64001,
 @msgtext = 'CRITICAL: The following disks have less than %d percent free space: %s',
 @severity = 12, @with_log = 'FALSE', @replace = 'REPLACE';


-- Create alerts associated with custom error messages
IF EXISTS (SELECT * FROM [dbo].[sysalerts] WHERE [name] = N'Disk Critical')
 EXEC dbo.sp_delete_alert @name=N'Disk Critical';
IF EXISTS (SELECT * FROM [dbo].[sysalerts] WHERE [name] = N'Disk Warning')
 EXEC dbo.sp_delete_alert @name=N'Disk Warning';
EXEC dbo.sp_add_alert @name=N'Disk Critical', @message_id=64001, @severity=0, @enabled=1,
 @delay_between_responses=0, @include_event_description_in=3;
EXEC dbo.sp_add_notification @alert_name=N'Disk Critical', @operator_name=@JobOper,
 @notification_method = 3;
EXEC dbo.sp_add_alert @name=N'Disk Warning', @message_id=64000, @severity=0, @enabled=1,
 @delay_between_responses=0, @include_event_description_in=3;
EXEC dbo.sp_add_notification @alert_name=N'Disk Warning', @operator_name=@JobOper,
 @notification_method = 1;

SQL Server Agent Job

Once the alerts have been defined, the next step is to create the SQL Server Agent job. The script used to create the job is shown in listing 4 below.

-- Listing 4
-- Set conditional value of job parameters based on version of current instance
IF CAST(@InstanceVersion AS INT) < 10
BEGIN
 SELECT @JobSubSystem = N'ActiveScripting', @DatabaseName = N'VBScript',
  @JobCmd = N'Option Explicit
On Error Resume Next
dim sWarnMsg, sCritMsg, sConStr, sComputer, sCmd
dim oWMI, oDiskList, oDisk, oConn
dim iWarn, iPct, iError, iCrit, cWarn, cCrit

'' Set disk free space percentage thresholds
iWarn = ' + @PctWarning + '
iCrit = ' + @PctCritical + '
cWarn = 0
cCrit = 0

'' Initialize string variables
sComputer = "."
sConStr = "Provider=SQLOLEDB;server=' + @@SERVERNAME + ';database=msdb;Trusted_Connection=Yes"
sCritMsg = ""
sWarnMsg = ""

'' Initialize object variables
set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & sComputer & "\root\cimv2")

'' Drive type 3 indicates local disk
set oDiskList = oWMI.ExecQuery("SELECT * FROM Win32_LogicalDisk WHERE DriveType = 3")
set oConn = CreateObject("ADODB.Connection")

'' Loop through all local disk drives
for each oDisk in oDiskList
 iPct = round((oDisk.FreeSpace / oDisk.Size) * 100)
 if iPct < iWarn and iPct > iCrit then
  cWarn = cWarn + 1
  sWarnMsg = sWarnMsg & replace(oDisk.Name, ":", "") & " - " & iPct & "; "
 end if
 if iPct < iCrit then
  cCrit = cCrit + 1
  sCritMsg = sCritMsg & replace(oDisk.Name, ":", "") & " - " & iPct & "; "
 end if
next

'' Open local database connection
oConn.Open(sConStr)

'' Check results
if cWarn = 0 and cCrit = 0 then
 sCmd = "RAISERROR(''All disks have free space within the defined thresholds.'', 0, 2) WITH NOWAIT"
 oConn.Execute(sCmd)
else
 if cWarn > 0 then
  sCmd = "RAISERROR(64000, 0, 0, " & iWarn & ", ''" & sWarnMsg & "'') WITH LOG, NOWAIT"
  oConn.Execute(sCmd)
 end if
 if cCrit > 0 then
  sCmd = "RAISERROR(64001, 0, 1, " & iCrit & ", ''" & sCritMsg & "'') WITH LOG, NOWAIT"
  oConn.Execute(sCmd)
 end if
end if

'' Close local database connection
oConn.Close()

'' Housekeeping
set oConn = Nothing
set oDisk = Nothing
set oDiskList = Nothing
set oWMI = Nothing
';
END
ELSE
BEGIN
 SELECT @JobSubSystem = N'Powershell', @DatabaseName = N'Powershell',
  @JobCmd = N'#SqlAlert function
Function SqlAlert([string]$fConn, [string]$fCmd)
{
    $sqlConn = New-Object System.Data.SqlClient.SQLConnection($fConn);
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand($fCmd, $sqlConn);
 
    $sqlConn.Open();
 try { $sqlCmd.ExecuteNonQuery() | Out-Null; }
 catch [System.Management.Automation.MethodInvocationException] { continue; }
    $sqlConn.Close;
}

# Initialize variables
$sqlConn = "Data Source=' + @@SERVERNAME + ';Initial Catalog=msdb;Integrated Security=SSPI";
$server = "."
$pctWarn = ' + @PctWarning + '
$pctCrit = ' + @PctCritical + '
$msgWarn = ""
$msgCrit = ""
$cntWarn = 0
$cntCrit = 0

# Get fixed drive info
$disks = Get-WmiObject -ComputerName $server -Class Win32_LogicalDisk -Filter "DriveType = 3";
foreach ($disk in $disks)
{
 $deviceID = $disk.DeviceID;
 $drive = $deviceID -replace ":", " -";
 [float]$size = $disk.Size;
 [float]$freespace = $disk.FreeSpace;
 $pctFree = [Math]::Round(($freespace / $size) * 100, 2);
 
 if ( ($pctFree -lt $pctWarn) -and ($pctFree -gt $pctCrit) )
 {
  $cntWarn = $cntWarn + 1;
  $msgWarn = "$msgWarn $drive $pctFree; ";
 }
 
 if ($pctFree -lt $pctCrit)
 {
  $cntCrit = $cntCrit + 1;
  $msgCrit = "$msgCrit $msgWarn $drive $pctFree; ";
 }
}
# Set conditional value of RAISERROR command and execute SqlAlert function where applicable
if ( ($cntWarn -eq 0) -and ($cntCrit -eq 0) )
{
 $sql = "RAISERROR(''All disks have sufficient free space.'', 0, 2) WITH NOWAIT";
 SqlAlert $sqlConn $sql;
}
if ($cntWarn -gt 0)
{
 $sql = "RAISERROR(64000, 0, 0, $pctWarn, ''$msgWarn'') WITH LOG, NOWAIT";
 SqlAlert $sqlConn $sql;
}
if ($cntCrit -gt 0)
{
 $sql = "RAISERROR(64001, 0, 1, $pctCrit, ''$msgCrit'') WITH LOG, NOWAIT";
 SqlAlert $sqlConn $sql;
}

# Close SQL connection
$sqlConn.Close;
';
END
BEGIN TRANSACTION

-- Create job category if it doesn't already exist
IF NOT EXISTS (SELECT name FROM dbo.syscategories WHERE name=@JobCategory AND category_class=1)
BEGIN
 EXEC @ReturnCode = dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=@JobCategory;
 IF (@@ERROR <> 0 OR @ReturnCode <> 0)
 BEGIN
  SET @DebugMessage = 'Error creating job category [' + @JobCategory + ']';
   RAISERROR(@DebugMessage, 0, 42) WITH NOWAIT;
    GOTO QuitWithRollback;
 END
END

-- Delete job if it already exists
IF EXISTS (SELECT job_id FROM dbo.sysjobs_view WHERE [name] = @JobName)
 EXEC dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;

-- Create disk monitoring job
EXEC @ReturnCode = dbo.sp_add_job @job_name=@JobName, @enabled=1, @notify_level_eventlog=2,
 @notify_level_email=2, @notify_level_netsend=0, @notify_level_page=0,
 @notify_email_operator_name=@JobOper, @delete_level=0, @description=@JobDescr,
 @category_name=@JobCategory, @owner_login_name=N'sa', @job_id = @JobID OUTPUT;
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
 SET @DebugMessage = 'Error creating job [' + @JobName + ']';
  RAISERROR(@DebugMessage, 0, 42) WITH NOWAIT;
   GOTO QuitWithRollback;
END

-- Add step to job
EXEC @ReturnCode = dbo.sp_add_jobstep @job_id=@JobID, @step_name=@JobStep, @step_id=1,
 @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0,
 @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0,
 @os_run_priority=0, @subsystem=@JobSubSystem, @command=@JobCmd,
 @database_name=@DatabaseName, @flags=0;
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
 SET @DebugMessage = 'Error creating job step [' + @JobStep + ']';
  RAISERROR(@DebugMessage, 0, 42) WITH NOWAIT;
   GOTO QuitWithRollback;
END

-- Explicitly set step id at which job will start
EXEC @ReturnCode = dbo.sp_update_job @job_id = @JobID, @start_step_id = 1;
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
 SET @DebugMessage = 'Error setting start step for job [' + @JobName + ']';
  RAISERROR(@DebugMessage, 0, 42) WITH NOWAIT;
   GOTO QuitWithRollback;
END

-- Create job schedule
EXEC @ReturnCode = dbo.sp_add_jobschedule @job_id=@JobID, @name=@JobName, @enabled=1, @freq_type=4,
 @freq_interval=1, @freq_subday_type=8, @freq_subday_interval=1, @freq_relative_interval=0,
 @freq_recurrence_factor=0, @active_start_date=19900101, @active_end_date=99991231,
 @active_start_time=0200, @active_end_time=230800;
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
 SET @DebugMessage = 'Error creating job schedule [' + @JobName + ']';
  RAISERROR(@DebugMessage, 0, 42) WITH NOWAIT;
   GOTO QuitWithRollback;
END

-- Designate server for job execution
EXEC @ReturnCode = dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)';
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
 SET @DebugMessage = 'Error setting job server for job [' + @JobName + ']';
  RAISERROR(@DebugMessage, 0, 42) WITH NOWAIT;
   GOTO QuitWithRollback;
END

-- Commit changes if no errors occur
IF @@TRANCOUNT <> 0
 COMMIT TRANSACTION;
SET @DebugMessage = 'DiskSpace monitoring job has been (re)created.';
 RAISERROR(@DebugMessage, 0, 42) WITH NOWAIT;

-- Rollback changes if error occurs at any point in script
QuitWithRollback:
IF (@@TRANCOUNT <> 0)
 ROLLBACK TRANSACTION;
GO

The first section of the code to create the job is to set the version-specific job-related variables, mainly due to the fact that the ActiveScripting subsystem is being deprecated in favor of Powershell. Therefore, if the instance is running on SQL Server version 2005 or earlier, the job step will be created using VBScript under the ActiveScripting subsystem. Otherwise, Powershell will be used. In either case, the job makes use of Windows Management Instrumentation (WMI) to determine the percent free space for each local disk on the SQL Server. It then creates a database connection to the local SQL Server instance and issues one or more RAISERROR commands to trigger the Disk Warning and/or Disk Critical alert (see previous section).

Next Steps
  • Update the job schedule based on the desired monitoring interval. The job is scheduled to run hourly by default.
  • The Win32_LogicalDisk class in WMI does not include mounted volumes. If your environment makes use of mount points, the code should be updated accordingly.


Last Update: 4/19/2013


About the author
MSSQLTips author Mike Eastland
Mike Eastland has been a SQL Server DBA since 1999. His main areas of interest are monitoring and the automation of administrative tasks.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, April 19, 2013 - 10:19:58 AM - Kevin Read The Tip

Thank you for breaking the code down and explaining key sections. It helps in the learning process to actually know up front what the code is intended to do. Makes it easier to understand. So often all you see is the code posted, with no explanation. How is a person supposed to learn - by reverse engineering and hoping you understand what the programmer intended? Thank you again!


Tuesday, April 23, 2013 - 9:56:55 AM - Dave Hubble Read The Tip

Thanks this looks really great. I've just tested it on a version of SQLServer 2008 and it works fine.

If you get problems running the script twice due to messages already existing add :-

,@REPLACE='REPLACE'

To the --Listing 3 as below (in bold).

-- Listing 3
-- Create the custom messages
EXEC [master].dbo.sp_addmessage @msgnum = 64000,
 @msgtext = 'WARNING: The following disks have less than %d percent free space: %s',
 @severity = 8, @with_log = 'FALSE',@REPLACE='REPLACE';
EXEC [master].dbo.sp_addmessage @msgnum = 64001,
 @msgtext = 'CRITICAL: The following disks have less than %d percent free space: %s',
 @severity = 12, @with_log = 'FALSE',@REPLACE='REPLACE';

Hope this makes sense.

Thanks for this.


Friday, April 26, 2013 - 10:08:56 AM - Mark Hill Read The Tip

When managing a cluster it is common to run out of drive letters and have to use mount points, the xp_fixeddrives extended stored procedure does not support mount points and there is no easy way in Windows to see the space utilization of mount points. But there is a way to view the volume space with PowerShell and WMI queries. Code as follows.

 

$computer = "localhost"
    gwmi -query "select * from Win32_Volume where DriveType='3'" -computer $computer | Select `
        @{Name="MountPoint";Expression={$DID=$_.DeviceID;gwmi Win32_MountPoint -computer $computer | ? { (($_.Volume.Split("=")[1] -replace "[^a-z0-9-]|Volume","") -match ($DID -replace "[^a-z0-9-]|Volume","")) } | % { $_.Directory.Split("=")[1] -replace "`"","" }}},`
        @{Name="Capacity";Expression={[math]::round(($($_.Capacity)/1GB),2)}},`
        @{Name="FreeSpace";Expression={[math]::round(($($_.FreeSpace)/1GB),2)}},`
        @{Name="UsedSpace";Expression={[math]::round((($_.Capacity - $_.FreeSpace)/1GB),2)}},`
        @{Name="PercentFree";Expression={[math]::round(($($_.FreeSpace)/$($_.Capacity)*100),2)}
        } | Format-Table -Auto



Tuesday, April 30, 2013 - 8:29:35 AM - Reynold Joseph Read The Tip

I am trying to parse these three group of scripts (listing 1, listing 2, and listing 3) in an SQL 2008 environment and I am having trouble with the "QuitwithRollback" variable in listing 2. It complains that the variable is not defined.

Here is the error message "A GOTO statement references the label 'QuitWithRollback' but the label has not been declared."

BEGIN
 SET @DebugMessage = 'No failsafe operator found; Job [' + @JobName + 
 '] will not be created without notification functionality.';
  RAISERROR(@DebugMessage, 8, 0) WITH LOG, NOWAIT;
   GOTO QuitWithRollback;
END



Tuesday, April 30, 2013 - 11:53:23 AM - Mike Eastland Read The Tip

Reynold - The very last section of code in Listing 4 creates the QuitWithRollback label.-- Rollback changes if error occurs at any point in script

QuitWithRollback:
IF (@@TRANCOUNT <> 0)
ROLLBACK TRANSACTION;
GO

As indicated in the instructions, all 4 listings of code need to be executed as a single unit.


Tuesday, April 30, 2013 - 1:30:09 PM - Reynold Read The Tip

I am deeply sorry. I saw that after I went back to the instructions again to use all 4 scripts as unit. Once again thanks, you guys are excellent. I am not a DBA yet, I just got in to it, but I am sure with this site and these wonderful tips I will be able to get somewhere.

Thanks and thanks


Wednesday, May 29, 2013 - 2:46:43 PM - Michael Read The Tip

Great post!

I may have detected a typo as I was testing your solution and could not get it to raise an error.

 I think the bolded red text was missed in "Listing 4". I have not tested the powershell component yet but it may have a typo too.

 if cWarn > 0 then

  sCmd = "RAISERROR(64000, 0, 0, " &iWarn & ", ''" & sWarnMsg & "'') WITH LOG, NOWAIT"

  oConn.Execute(sCmd)

 end if

 if cCrit > 0 then

  sCmd = "RAISERROR(64001, 0, 1, " &iCrit & ", ''" & sCritMsg & "'') WITH LOG, NOWAIT"

  oConn.Execute(sCmd)

 end if


Thursday, May 30, 2013 - 2:59:25 PM - Mike Read The Tip

Michael - The code should work as written as that is how it was tested.  If your testing returns contrary results, please let me know.


Wednesday, June 05, 2013 - 4:45:26 AM - dioscoredes Read The Tip

I am always hubmled by the depth of skill and time spent by our authors in researching and publishing a topic.  But am I the only timestrapped DBA looking for a five liner that simply does the job eg pump out disk and datafile stats?  

 


Thursday, June 20, 2013 - 4:47:06 PM - Ernest Stalnaker Read The Tip

Has anybody had trouble getting this job to send an e-mail on a SQL Server 2005 server?  I set it up on three 2005 servers that I maintain as well as a 2012 server, and the only server that I am receiving alerts from is the 2012 one.  The 2005 servers all have database mail enabled, and I do receive alerts for other jobs I have running (Such as backup and other maintenance jobs).  It says the job is running successfully, but the e-mails are never sent.


Friday, June 21, 2013 - 9:56:28 AM - Mike Read The Tip

Hi Earnest.  The job is designed to only send emails if there are disks on the target server with freespace less than the defined threshold percentages.  Is it possible that the 2005 servers in question have more freespace than the thresholds defined in the job?

My only other thought is to make sure that the custom error messages and associated alerts are configured properly.  If you open an SSMS query window and connect to one of the 2005 instances, you can issue a RAISERROR command such as the following:

RAISERROR(64000, 0, 0, 0, 'This is a test') WITH LOG, NOWAIT;

If that generates an email, I'm guessing you have plenty of free space available on the drives for that server.  If not, there is something amiss with the custom error message and alert setup.


Friday, June 21, 2013 - 5:11:11 PM - Ernest Stalnaker Read The Tip

Mike, this ran fine on both my 2012 and 2005 servers.  I had tested the job originally by changing the values for PctWarning and PctCritical to higher numbers, so that the job was guaranteed to generate an alert.  Even when I set the numbers to 99, I am still getting no e-mails on my 2005 servers, even though the job appears to be running, and I do get e-mails on my 2012 server.  Plus the statement you gave me does generate e-mails, and other SQL Agent jobs I have running generate e-mails.  Very strange.

Ernest Stalnaker

 


Saturday, June 22, 2013 - 7:41:36 AM - Mike Read The Tip

Ernest - The thresholds are for configured for lower-end values.  For example, if you set PctWarning to 10, then it will only send an alert if there are disks on the server with less than 10% free space.  My apologies if that wasn't clear in the tip notes.  Hope this helps.

-Mike


Monday, June 24, 2013 - 9:33:35 AM - Ernest Stalnaker Read The Tip

Mike,

I understand that, but I was just trying to trick it into sending me an alert even if my disks were only 20 or 30% full, for example.  So a number of 99 should send an alert unless the disks are less than 1% full, correct?  When I used those values it did generate alerts on my 2012 server.  Then I changed the numbers back to 5 and 15, and I haven't had any alerts since then, because the disks are not that full.  But that proved to me that the job was running correctly.

However, no values that I try work on my 2005 servers.  So I think it's something that is different between SQL Server 2005 and 2012.  I have SP4 on my 2005 servers, and database mail works fine on them.

Thanks again for any help you can give me!

Ernest


Thursday, June 27, 2013 - 9:30:51 PM - Mike Read The Tip

Ernest - I finally got around to restesting the code on 2005, and the post above from @Wednesday, May 29, 2013 - 2:46:43 PM - Michael provides the correct fix.

 

Michael@Wednesday, May 29, 2013 - 2:46:43 PM - Thanks for keeping me honest.

 

All - My apologies for the oversight.


Monday, July 01, 2013 - 4:07:24 PM - Mike Read The Tip

The required change suggested by Michael@Wednesday, May 29, 2013 - 2:46:43 PM , as well as the change suggested by Tuesday, April 23, 2013 - 9:56:55 AM - Dave Hubble have been made to the code.  It has been retested against both 2005 and 2008R2 with the expected results.

Thanks again to everyone who reviewed the code and helped point out what I missed.


Tuesday, July 02, 2013 - 10:35:14 AM - Ernest Stalnaker Read The Tip

Thanks Mike!!!  I appreciate you looking into this.  I will implement the new code this week.

Ernest


Tuesday, July 30, 2013 - 4:48:19 PM - Edgar Márquez Read The Tip

Help me please !!!

 

Mens 15279, Nivel 16, Estado 1, Procedimiento sp_addmessage, Línea 97

Debe agregar la versión us_english de este mensaje para poder agregar también la versión 'Español'.

Mens 15279, Nivel 16, Estado 1, Procedimiento sp_addmessage, Línea 97

Debe agregar la versión us_english de este mensaje para poder agregar también la versión 'Español'.

Mens 14262, Nivel 16, Estado 1, Procedimiento sp_verify_alert, Línea 85

Se especificó @message_id ('64001'), que no existe.

Mens 14262, Nivel 16, Estado 1, Procedimiento sp_verify_notification, Línea 26

Se especificó @alert_name ('Disk Critical'), que no existe.

Mens 14262, Nivel 16, Estado 1, Procedimiento sp_verify_alert, Línea 85

Se especificó @message_id ('64000'), que no existe.

Mens 14262, Nivel 16, Estado 1, Procedimiento sp_verify_notification, Línea 26

Se especificó @alert_name ('Disk Warning'), que no existe.

DiskSpace monitoring job has been (re)created.


Tuesday, July 30, 2013 - 4:59:27 PM - Edgar Márquez Read The Tip

Sorry... 

I just had to add this statement

 @lang = 'us_english',

 

tx


Tuesday, March 25, 2014 - 11:25:26 AM - David Hutton Read The Tip

I use a simple script and I am not sure where I found it but it just like the one above from Mark Hill so I will give him credit.  I did add $Computer on line 2 so that I have one script with all of the computers that I am checking and it will generate a report with all of the computers.  I usually port it out to a .txt file and now I have my disk space report for all of my systems that I run from one machine.   

 $computer = "PutComputerNameHere1"
        $computer
    gwmi -query "select * from Win32_Volume where DriveType='3'" -computer $computer | Select `
        @{Name="MountPoint";Expression={$DID=$_.DeviceID;gwmi Win32_MountPoint -computer $computer | ? { (($_.Volume.Split("=")[1] -replace "[^a-z0-9-]|Volume","") -match ($DID -replace "[^a-z0-9-]|Volume","")) } | % { $_.Directory.Split("=")[1] -replace "`"","" }}},`
        @{Name="Capacity";Expression={[math]::round(($($_.Capacity)/1GB),2)}},`
        @{Name="FreeSpace";Expression={[math]::round(($($_.FreeSpace)/1GB),2)}},`
        @{Name="UsedSpace";Expression={[math]::round((($_.Capacity - $_.FreeSpace)/1GB),2)}},`
        @{Name="PercentFree";Expression={[math]::round(($($_.FreeSpace)/$($_.Capacity)*100),2)}
        } | Format-Table -Auto

$computer = "PutComputerNameHere2"
        $computer
    gwmi -query "select * from Win32_Volume where DriveType='3'" -computer $computer | Select `
        @{Name="MountPoint";Expression={$DID=$_.DeviceID;gwmi Win32_MountPoint -computer $computer | ? { (($_.Volume.Split("=")[1] -replace "[^a-z0-9-]|Volume","") -match ($DID -replace "[^a-z0-9-]|Volume","")) } | % { $_.Directory.Split("=")[1] -replace "`"","" }}},`
        @{Name="Capacity";Expression={[math]::round(($($_.Capacity)/1GB),2)}},`
        @{Name="FreeSpace";Expression={[math]::round(($($_.FreeSpace)/1GB),2)}},`
        @{Name="UsedSpace";Expression={[math]::round((($_.Capacity - $_.FreeSpace)/1GB),2)}},`
        @{Name="PercentFree";Expression={[math]::round(($($_.FreeSpace)/$($_.Capacity)*100),2)}
        } | Format-Table -Auto

 and so on.........

 

Here is the output - (looks better in a .txt file).  And if you want you can import this .txt file into Excel

 

PutComputerNameHere1

MountPoint Capacity FreeSpace UsedSpace PercentFree
---------- -------- --------- --------- -----------
C:\\          49.66      8.68     40.97       17.49
E:\\            300    183.67    116.32       61.23
F:\\            100     60.18     39.82       60.18
G:\\             50     37.93     12.07       75.87
H:\\            720     30.82    689.18        4.28
V:\\             15     13.92      1.08        92.8


PutComputerNameHere2

MountPoint Capacity FreeSpace UsedSpace PercentFree
---------- -------- --------- --------- -----------
C:\\          49.66     15.14     34.52       30.48
E:\\            500    482.67     17.33       96.53
F:\\            100     87.89     12.11       87.89
G:\\             50     37.89     12.11       75.79
H:\\            900    566.41    333.58       62.94
V:\\             15      14.9       0.1       99.35

 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.