October 2, 2008 at 6:43 am
[font="Courier New"]What is a good SQL Server disk space monitor?
We have SQLdm installed which appears to report on %FULL (data, logs) but I think autogrow will continue to extend the underlying file size therefore %FULL is not 100% reliable in terms of understanding Total Disk Space available and Total Disk Space used.
For example -- I’m interested in monitoring physical disk space like this:
SERVER NAME DRIVE ALLOCATED (GB) USED (GB) %FREE
Server ABC C: 15 12 20
D: 60 40 33
Server XYZ C: 10 8 20
D: 40 20 50
I’d like to receive an alert when my %FREE < 20%
And correct me if I’m mis-interpreting what SQLdm is providing ..
Thx in advance[/font]
October 2, 2008 at 7:19 am
I build my own tool for this purpose.
I have a database called DBA that has tools in it for DBAs. One of the tools has these objects:
USE [DBA]
GO
CREATE TABLE [dbo].[Drives](
[Letter] [char](1) primary key,
[Purpose] [varchar](max) NULL,
[CapacityG] [float] NOT NULL,
[AlertThreshhold] [float] NOT NULL);
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[DriveSpace]
as
set nocount on
create table #D (
Letter char(1),
Available float);
insert into #d (letter, available)
exec master.dbo.xp_fixeddrives;
if exists
(select *
from dbo.drives
inner join #D
on drives.letter = #d.letter
where alertthreshhold >= available/1000.00)
exec msdb.dbo.sp_send_dbmail @recipients = 'DBA',
@subject = 'Drive Space Low on ',
@importance = 'High';
if exists
(select *
from #d
left outer join dbo.drives
on drives.letter = #d.letter
where drives.letter is null)
exec msdb.dbo.sp_send_dbmail @recipients = 'DBA',
@subject = 'Drive data missing in DBA.dbo.Drives on ',
@importance = 'Normal';
GO
Insert the drives, the threshhold where you want it to alert you, etc., then schedule it to run nightly (or more often if you need).
You might be able to do something with that.
This one, as written, has come in quite handy for me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 2, 2008 at 7:30 am
G2 - thx. Very helpful!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply