Setup a low disk space alert for SQL Server drives

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev, Uncategorized | Posted on 21-12-2010


This is the pretty easy and clean script that you may use to check periodically your database server drive space. You can create a job to run on hourly basis to execute this code, along with a database mail code to send you alert if  any of drive  has less than 15% or 20% threshold value.

-- Create a global temp table
CREATE TABLE ##space( dletter varchar(3), tspace BIGINT, fspace int, percentfree numeric(5,2))
-- Insert drive details
INSERT INTO ##space (dletter, fspace) EXEC master.dbo.xp_fixeddrives
-- Declare variables
DECLARE   @oFSO   INT, @oDrive INT, @drsize VARCHAR(255), @ret   INT
-- invoke OACreate
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT
DECLARE @dletter VARCHAR(3), @fspace INT, @tspace BIGINT
while (select count(*) from  ##space where tspace is null)>0
   select top 1 @dletter = dletter  + ':\',@fspace = fspace from ##space where tspace is null
   EXEC   @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
   EXEC   @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
   UPDATE   ##space SET   tspace = CAST(@drsize AS BIGINT) WHERE   lower(dletter) + ':\'   = lower(@dletter)
   EXEC master.dbo.sp_OADestroy @oDrive
EXEC master.dbo.sp_OADestroy @oFSO
update   ##space set   percentfree = fspace/((tspace/1024.0)/1024.0)*100 
-- Select your data
select [Drive] = dletter ,
         [Total Space GB]= convert(numeric(10,3), (tspace/1024.0)/1024.0/1024) ,
         [Free Space GB]=convert(numeric(10,3),fspace/1024.0) ,
         [% Free]= percentfree 
         from   ##space
-- Drop temporary table
drop table ##space

Comments (2)

Sorry man, It’s not working.
Going, going, going and I’ve to stoped it for about few minutes.

actually i m doing this in a query only see this whole logicDeclareCursor agg isselect Agg_no,client.Client_name ceinltn,End_date from AGGREMENT,client WHERE client.client_id = aggrement.client_id;Beginfor agrmt in agg loop :P2_END_DATE := agrmt.End_date; :P2_AGG_NO := agrmt.Agg_no; :P2_CLIENT_NAME := agrmt.clientn; if to_date(agrmt.End_date,’dd-mon-rr’) <= to_date(SYSDATE,'dd-mon-rr') THEN htp.p('open script); htp.p('pop(''' || :P2_AGG_NO || ''',''' || :P2_END_DATE || ''',''' || :P2_CLIENT_NAME || ''');'); htp.p('if(v == true){'); update aggrement set checked='y' where agg_no = :P2_AGG_NO; htp.p('}else{'); update aggrement set checked='n' where agg_no = :P2_AGG_NO; htp.p('}close script'); end if;end loop;End;This is my whole logic where I am calling a javascript function from select query from that function I m getting the value of 'v' which is a boolean variable and after checking this variable's value I want to update my table accordingly.this is the Pop function:var v;function pop(ag_no,end_dt,clnt_nm){v = confirm("Aggrement: " + ag_no + " with Client: " + clnt_nm + " is going to end on " + end_dt + " Date.");}For this either I need to run the update table qurery under javascript or I can reffer the javascript variable 'v' into my pl/sql function.

Write a comment


© 2010 Increase your website traffic with