December 22, 2003 at 1:35 am
Hello,
I´ve been searching for some way to monitor free disk space on all my servers, not only the SqlServer.
Have seen a lot of articles covering this subject but only on the sqlserver.
Is there a way to achieve this?
Most thankful for any help!
//Robert
December 22, 2003 at 7:18 am
There are lots of ways, depending on your budget. The best solution would be to get some sort of monitoring software such as Patrol or HP OpenView. These will tell you alot more than just free disk space, but they tend to be very expensive.
A cheaper alternative is to write some Windows Script files to check the diskspace and send e-mails at certain levels. This isn't too difficult, but it does require some programming knowledge and may be a pain to maintain.
How many servers do you need to monitor?
December 22, 2003 at 7:23 am
If you're using HP/Compaq servers, you can use Insight Manager 7, which is free. It also monitors critical hardware alerts such as temperature, power supply failures, etc. I'm sure Dell has something similar.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 22, 2003 at 9:19 am
Hi,
Well i could make a small program in c++, c#, vb or similar but i
got some nice mailing-functions running along with other monitoring
procedures so i thought i could use Sql-server to do my work...
Good to have all small apps/procs/jobs in one place, easy to maintain.
Got 4 servers running, one with Sql the others for mail, web and applications.
//R
December 22, 2003 at 12:06 pm
You can use Servers Alive (http://www.woodstone.nu/salive/), it's free for up to 10 alerts.
It can monitor disk space, services, processes, databases, ping servers, etc.
December 23, 2003 at 10:19 am
PRINT 'Author : Ricky Petilla / Dave Yoder'
PRINT 'Purpose: Identify drives at are below 500 MB free space.'
PRINT 'Written: 2003/07/28 Version 1.0'
use RBSAVE
/*
create table psdrivesfreespace
(
ASOFDATEdatetime,
MACHINEvarchar(15),
DRIVEvarchar(2),
MBvarchar(7)
)
*/
set nocount on
create table #allpsdrives (
machinevarchar(15),
drivevarchar(2))
create table #dir1 (
freespacevarchar(120))
create table #dir2 (
machinevarchar(15),
drivevarchar(2),
freespacebigint)
insert into #allpsdrives values ('\\server01\','c$')
insert into #allpsdrives values ('\\server01\','d$')
insert into #allpsdrives values ('\\server01\','e$')
insert into #allpsdrives values ('\\server01\','f$')
insert into #allpsdrives values ('\\server02\','c$')
insert into #allpsdrives values ('\\server02\','d$')
insert into #allpsdrives values ('\\server02\','e$')
insert into #allpsdrives values ('\\server02\','f$')
DECLARE
@t_machinevarchar(15),
@t_drive char(3),
@t_freespacebigint,
@f_freespacebigint,
@s_commandvarchar(100),
@f_commandvarchar(80)
DECLARE psdrive_cursor CURSOR FOR
SELECT machine, drive
FROM #allpsdrives
order by machine, drive
OPEN psdrive_cursor
FETCH NEXT FROM psdrive_cursor
INTO @t_machine, @t_drive
WHILE @@FETCH_STATUS = 0
BEGIN
set @s_command = '\\server01\d$\mssql\batch\dirs.bat'
set @f_command = @s_command + ' ' + @t_machine + @t_drive
delete from #dir1
insert into #dir1
exec master..xp_cmdshell @f_command
delete from #dir1
where freespace not like '%Dir(s)%' or freespace is NULL
select @f_freespace = convert(bigint,(select
substring (freespace,(charindex ('Dir(s)', freespace, 1) + 7),
(charindex ('bytes free', freespace, 1) -1) - (charindex ('Dir(s)', freespace, 1) + 7))
from
#dir1))
insert into #dir2
values (@t_machine, @t_drive, @f_freespace)
-- Get the next #allpsdrives
FETCH NEXT FROM psdrive_cursor
INTO @t_machine, @t_drive
END
CLOSE psdrive_cursor
DEALLOCATE psdrive_cursor
declare @asofdate datetime
set @asofdate = getdate()
insert into psdrivesfreespace
select @asofdate,machine, drive, convert(char(7),freespace/(1024*1024))
from #dir2
EXEC master..xp_sendmail @recipients = 'dyoder',
@query = 'SELECT convert(char(19),ASOFDATE) as "AS OF DATE",Drive = MACHINE+DRIVE, str(MB,8) as " MB Free "
FROM RBSAVE..psdrivesfreespace
where convert(char(2), ASOFDATE,114) = convert(char(2), getdate(),114) AND
convert(char(10),ASOFDATE,126) = convert(char(10),getdate(),126) AND
MB IS NOT NULL
order by DRIVE, MACHINE',
@subject = 'Server Disk Free Space',
@message = ' Disk(s) free space:'
December 23, 2003 at 10:50 am
xp_fixeddrives will list all the drives on the SQL Server along with the free space on that drive.
All you'd need is a means to execute it against all your machines and report back in a single report. OR, if multiple reports are ok, just create a job to execute it on each server.
December 29, 2003 at 2:11 pm
Const HARD_DISK = 3
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colDisks = objWMIService.ExecQuery _
("SELECT * FROM Win32_LogicalDisk WHERE DriveType = " _
& HARD_DISK & "")
Set objEmail = CreateObject("CDO.Message")
Set objSysInfo = CreateObject("ADSystemInfo")
objEmail.From = "your-mail"
objEmail.To = "your-mail"
objEmail.cc = "your-mail"
For Each objDisk in colDisks
'Wscript.Echo "Computer name: " & objSysInfo.ComputerName
'Wscript.Echo "Device ID: " & objDisk.DeviceID
'Wscript.Echo "Total Disk Space: " & objDisk.Size
'Wscript.Echo "Free Disk Space: " & objDisk.FreeSpace
'Wscript.Echo "percentage used: "&(100-(( objDisk.FreeSpace/objDisk.Size)*100))
x= (100-(( objDisk.FreeSpace/objDisk.Size)*100))
'Wscript.Echo "percentage used: "&(100-(( objDisk.FreeSpace/objDisk.Size)*100))
'if (75< x ) then
'Wscript.Echo "percentage of harddrive used used: "&x
objEmail.Subject = "low in the space on the "+objSysInfo.ComputerName+" on the "+objDisk.DeviceID+" Drive "
objEmail.Textbody = objSysInfo.ComputerName+" Drive "+objDisk.DeviceID +" is too low on the free space (it is below 75%)"
objEmail.Send
'end if
Next
save script above as .vbs file
in windows sceduling create job that will run
following string
C:\WINDOWS\system32wscript.exe C:\myfile.vbs
or
C:\WINNT\system32wscript.exe C:\myfile.vbs
if running NT or windows 2000
schedule that job at regular intervals and when the occupied disk space on any of local disks will go above 75% it will shoot you an e-mail
January 2, 2004 at 1:10 am
Sorry i havent replyed sooner...
Im going to take a while to check this tips out and will comeback to you and post my results.
Thanks in advance and happy new year to you all!
//Robert
January 5, 2004 at 8:42 am
Robert,
The limitation of xp_fixeddrives is that it doesnt return the size of the drive.
I use srvinfo -ns. Its in the Resource Kit. The command returns lots of data but with some tsql, you can extract the figures you want. For example
declare @drive_letter char(1)
select @drive_letter = 'c'
create table #svrinfo(text1 varchar(500))
insert #svrinfo (text1)
execute master..xp_cmdshell 'srvinfo -ns'
SELECT text1 FROM #svrinfo WHERE text1 like '%'+@drive_letter+'$%ntfs%[0-9]%'
Tim
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply