June 4, 2012 at 8:37 pm
I would like to setup a low disk space alert when space more than 70 percent without using powershell scripts and i required to alert for one server not for multiple server.
Please suggest..
June 5, 2012 at 8:50 am
There are different ways to monitor disk space and generate trend
1. xp_fixeddrives: gived you free space for all the drive
2. Sysinternal command prompt utility will give more information includes total drive size and free soace. You have to download the utility (http://technet.microsoft.com/en-us/sysinternals/bb897550.aspx).
Run the command: "psinfo \\ServerName -d disk"
3. Use performance counters "LogicalDisk\%Free Space" and "LogicalDisk\Free Megabytes". Using these two values you can calculate total drive size
June 5, 2012 at 10:24 am
Have you researched SQL Server Alerts?
Jared
CE - Microsoft
June 5, 2012 at 10:51 am
As far as I know, SQL server just gives free space information.
To know % free space we need to find something ouside of sql like perfmon, psinfo, wmi etc...
please correct me if I am wrong
June 5, 2012 at 10:55 am
Daxesh Patel (6/5/2012)
As far as I know, SQL server just gives free space information.To know % free space we need to find something ouside of sql like perfmon, psinfo, wmi etc...
please correct me if I am wrong
Maybe you should read up on SQL Server Agent Alerts so you can see what they can do? 🙂
Here's a sample: http://msdn.microsoft.com/en-us/library/ms186385.aspx
Jared
CE - Microsoft
June 5, 2012 at 11:00 am
in order to get drive sizes vs free space and run a comparison in SQL, you have to use sp_OAMethod
so the xp_Fixeddrives is only half the solution. you have to use xp_FixedDrives to populate a temp table and then via a cursor, use sp_OAMethod to gather the rest of the info.
Its ugly, but it would like this;
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT @@servername as ServerName, drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
GETDATE() as Date_Entered
FROM #drives
June 5, 2012 at 11:13 am
and the whole thing to send an email from the server that has low disk space.....
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ;
SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT @@servername as ServerName, drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
GETDATE() as Date_Entered
into #result_set
FROM #drives
declare @servername nvarchar(100), @drive1 nvarchar(2), @freeMB int, @totalMB int, @free int, @date_entered nvarchar(50)
declare db_crsr_T cursor for
SELECT [ServerName], [drive], [Free(MB)], [Total(MB)], [Free(%)], [Date_Entered] from #result_set
open db_crsr_T
fetch next from db_crsr_T into @servername, @drive1, @FreeMB, @totalMB, @free, @date_entered
while @@fetch_status = 0
begin
if @free < 30 and @free > 10
begin
declare @msg1 nvarchar(500)
SET @msg1 = 'Instance ' + RTRIM(@servername) + ' only has ' + CONVERT(NVARCHAR(9),@freeMB) + ' MB free on disk ' + CONVERT(CHAR(1),@drive1) + ':\. The percentage free is ' + CONVERT(NVARCHAR(3),@free) + '. Drive ' + CONVERT(CHAR(1),@drive1) +':\ has a total size of ' + LTRIM(CONVERT(NVARCHAR(10),@totalMB)) + ' MB and ' + CONVERT(NVARCHAR(9),@freeMB) + ' MB free.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB_Mail', -- CHANGE THIS TO YOUR SERVERS MAIL PROFILE NAME...
@recipients = 'DBA@gmail.com', --CHANGE THIS TO YOUR EMAIL ADDRESS...
@body = @msg1,
@subject = 'Disk space alert' ;
end
if @free < 10
begin
declare @msg2 nvarchar(500)
SET @msg2 = 'WARNING!! Instance ' + RTRIM(@servername) + ' only has ' + CONVERT(NVARCHAR(9),@freeMB) + ' MB free on disk ' + CONVERT(CHAR(1),@drive1) + ':\. The percentage free is ' + CONVERT(NVARCHAR(3),@free) + '. Drive ' + CONVERT(CHAR(1),@drive1) +':\ has a total size of ' + LTRIM(CONVERT(NVARCHAR(10),@totalMB)) + ' MB and ' + CONVERT(NVARCHAR(9),@freeMB) + ' MB free.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB_Mail', -- CHANGE THIS TO YOUR SERVERS MAIL PROFILE NAME...
@recipients = 'DBA@gmail.com', --CHANGE THIS TO YOUR EMAIL ADDRESS...
@body = @msg2,
@subject = 'Disk Space Warning!' ;
end
fetch next from db_crsr_T into @servername, @drive1, @FreeMB, @totalMB, @free, @date_entered
end
close db_crsr_T
deallocate db_crsr_T
DROP TABLE #drives
DROP TABLE #result_set
make sure to change the email address and profile name. schedule it to run once an hour via SQL agent and done.
June 5, 2012 at 11:20 am
Maybe you should read up on SQL Server Agent Alerts so you can see what they can do?
Here's a sample: http://msdn.microsoft.com/en-us/library/ms186385.aspx
Yes there are capabilities to setup various kind of alerts in SQL Agent.
What I am saying is, a custom code is required to get %free information as Geoff has mentioned
and yes I was not aware of sp_OAMethod
June 5, 2012 at 11:24 am
Daxesh Patel (6/5/2012)
Maybe you should read up on SQL Server Agent Alerts so you can see what they can do?
Here's a sample: http://msdn.microsoft.com/en-us/library/ms186385.aspx
Yes there are capabilities to setup various kind of alerts in SQL Agent.
What I am saying is, a custom code is required to get %free information as Geoff has mentioned
and yes I was not aware of sp_OAMethod
I understand now.
Jared
CE - Microsoft
June 5, 2012 at 11:25 am
and i should mention that to get that code to work Ole Automation Procedures has to be enabled. (not on by default)
sp_configure 'show advanced options' ,'1'
go
reconfigure
go
sp_configure 'Ole Automation Procedures', '1'
go
reconfigure
go
June 5, 2012 at 1:40 pm
Please do not enable OLE Automation procs unless absolutely necessary...which is only if a third-party vendor requires it in my opinion.
May I recommend using PowerShell for this task? This gives you what you want in one line of code:
Get-WMIObject Win32_LogicalDisk -Filter "DriveType=3" -Computer "COMPUTERNAME" | Select SystemName,DeviceID,VolumeName,FileSystem,BlockSize,NumberOfBlocks,@{Name="size(GB)";Expression={"{0:N1}" -f($_.size/1gb)}},@{Name="freespace(GB)";Expression={"{0:N1}" -f($_.freespace/1gb)}},@{Name="freespace(%)";Expression={"{0:N1}" -f(($_.freespace/$_.size)*100)}}
Building a script around this to email you when a drive has less than n-percent free will be well worth the time investment. Enhancing the script to work against multiple servers will only be a stone's throw farther than that 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 5, 2012 at 6:21 pm
opc.three (6/5/2012)
Please do not enable OLE Automation procs unless absolutely necessary...
Please explain why or provide a link that does.
Also, is there a way to format that one line of code into logical sections in a multiline format to enhance readability or would that break the code?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 6, 2012 at 12:20 pm
Jeff Moden (6/5/2012)
opc.three (6/5/2012)
Please do not enable OLE Automation procs unless absolutely necessary...Please explain why or provide a link that does.
Issues I have with the OLE Automation procedures:
Issue #1
From BOL article OLE Automation Objects in Transact-SQL:
Unhandled errors in the Class_Initialize and Class_Terminate subroutines can cause unpredictable errors, such as an access violation in an instance of the Database Engine.
This means that a poorly written COM object used from one of the sp_OA procs can cause an access violation when running in the SQL Server process. That gives me pause.
CLR objects do not suffer from this issue.
Issue #2
A poorly written COM object can also have a memory leak which over time Windows cannot recover from without a reboot.
Managed CLR objects do not suffer from memory leaks unless there is a bug in the .NET Framework. Not choosing the SQLCLR for this reason would be like not choosing SQL Server in case there was a memory leak in SQLOS. It's possible, but I would venture a guess that it is somewhat rare, has limited exposure and is something Microsoft will react to fast.
Issue #3
From BOL article sp_OACreate (Transact-SQL):
[Use of sp_OACreate] Requires membership in the sysadmin fixed server role.
Are there ways to lock this down, sure, but its not a trivial affair, and not required meaning someone grabbing for a quick solution off the shelf is likely to bypass the effort. Does that make OLE Automation procs bad? No. sp_OA procs do not create security holes, database administrators do, but there are logical stopping points that provoke decisions when using other tools like SQLCLR to expose functionality not provided in T-SQL making it a better option IMHO.
...
The first issue is cause enough for me to leave them disabled. The third is also a deal-breaker when looking for or recommending a solution. Since SQL 2005 the OLE Automation procs have been disabled by default forcing us to make a conscious decision to enable them. The same can be said of SQLCLR, and when searching for a solution and deciding which one to enable guess which way I would choose. SQLCLR is my preferred alternative.
Also, is there a way to format that one line of code into logical sections in a multiline format to enhance readability or would that break the code?
Here is another way to write the same code. The backtick escapes the line-break letting PowerShell know we're continuing our code on the next line. The pipe separates commands instructing PowerShell to feed the results of one command into the next creating a left-to-right pipeline. In this case PowerShell runs the Get-WMIObject CmdLet and pipes the results into the Select-Object CmdLet (can be aliased as "Select").
Get-WMIObject Win32_LogicalDisk -Filter "DriveType=3" -Computer "COMPUTERNAME" `
| Select-Object SystemName,`
DeviceID,`
VolumeName,`
FileSystem,`
BlockSize,`
NumberOfBlocks,`
@{Name="size(GB)";Expression={"{0:N1}" -f($_.size/1gb)}},`
@{Name="freespace(GB)";Expression={"{0:N1}" -f($_.freespace/1gb)}},`
@{Name="freespace(%)";Expression={"{0:N1}" -f(($_.freespace/$_.size)*100)}}
You can also break up the pipeline and do the same this way in case you want to inspect the variable values along the way or use them for multiple purposes:
$wmiInfo = (Get-WMIObject Win32_LogicalDisk -Filter "DriveType=3" -Computer "COMPUTERNAME")
Select-Object `
-InputObject $wmiInfo `
-Property SystemName,`
DeviceID,`
VolumeName,`
FileSystem,`
BlockSize,`
NumberOfBlocks,`
@{Name="size(GB)";Expression={"{0:N1}" -f($_.size/1gb)}},`
@{Name="freespace(GB)";Expression={"{0:N1}" -f($_.freespace/1gb)}},`
@{Name="freespace(%)";Expression={"{0:N1}" -f(($_.freespace/$_.size)*100)}}
Get PowerShell 2.0 and use the PowerShell ISE (Integrated Scripting Environment, powershell_ise.exe) to try out this code.
Edit: remove actual computer name from sample code
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 12:46 pm
best way, the way i use, is a WMI command line query, this will catch logical drives and mounted volumes too as it looks at the volume level 😉
You can run it via an agent job step and then using some t-sql suck it into the instance.
wmic /output:"C:\tempfolder\volsize.txt" volume get capacity, "free space", name
you could also use this from a central server to read info from other servers
wmic /output:"c:\temp\mountsizes.txt" /node:sqlnodea,sqlnodeb,sqlnodec,sqlnoded,sqlnodee volume get capacity, "free space", name, systemname
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply