Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

%FreeSpace Alert in SQL 2005 Expand / Collapse
Author
Message
Posted Wednesday, June 04, 2008 4:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 17, 2009 6:59 AM
Points: 2, Visits: 19
Hi All,

Im looking for a way to create an alert to email an operator when, for arguments sake, a physical disk becomes 70% full. I know this can be done within system monitor but all that can do is perform a net send, what we need is an email. Is it possible for an SQL Server alert to do this?

I cannot find anything under SQL Server performance condition alert so im assuming that the only way this is possible would be a WMI query?

After much researching through books online and lots of googling I am only finding creating stored procedures which will do this. Thats all well and good but we want this set to fire an alert as soon as the threshold is breached.

If anyone could provide any help on this it will be greatly appreciated.
Post #511254
Posted Wednesday, June 04, 2008 5:16 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 3:00 PM
Points: 770, Visits: 1,593
Hi Jamie
You can use the XP to get drive and space information and the put that into a job on SQL server, this miget alert you via a mail, we have a application that runs as a alerting service, which will be usefull, if you create one, it looks into servers space every 5 minutes and then updates it into a table, then an application looks into the predefined space and throws error when there is a space breach

Cheers
Post #511261
Posted Wednesday, June 04, 2008 5:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:36 AM
Points: 1,199, Visits: 574
You can use xp_fixeddrives
Post #511267
Posted Wednesday, June 04, 2008 6:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 17, 2009 6:59 AM
Points: 2, Visits: 19
Cheers for your thoughts guys. I guess I will just have to stick to SP's and scheduled jobs.
Post #511283
Posted Wednesday, June 04, 2008 7:57 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, March 02, 2014 4:34 PM
Points: 724, Visits: 1,001
Is there a way to get this as Total Size and Free and in GIG and MG.

Post #511390
Posted Thursday, June 05, 2008 3:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 11:35 PM
Points: 100, Visits: 405
Create a vbs file in C; Drive named as disk.vbs
content are given below

Set DiskSet = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery("select FreeSpace,Size,Name from Win32_LogicalDisk where DriveType=3")
for each Disk in DiskSet
If (Disk.FreeSpace/Disk.Size) < 0.30 Then
WScript.Echo "Drive " + Disk.Name + " is low on space."
End If
Next

Then Create a Job which will run on your requirement to check sapce and in Step 1 put the below code.

create table ##diskspace (details varchar(1000))
insert into ##diskspace
exec xp_cmdshell 'cscript c:\disk.vbs'

if (select count(*) from ##diskspace where cmd like '%low on space%') > 0
begin
declare @sql varchar(200)
SELECT @sql ='select * from ##diskspace where cmd like ''%low on space%'''
PRINT @SQL
exec master.dbo.xp_sendmail @recipients='Your ID',
@query = @sql,
@subject = 'Disk Space Full'
end
drop table ##DiskSpace

Hope So It is HelpFul :)

Regards,
Raj


Post #511981
Posted Thursday, June 05, 2008 6:30 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, March 02, 2014 4:34 PM
Points: 724, Visits: 1,001
Thats a good script did not know you could call vbs scripts in SQL 2005.

Nice - thanks for sharing
Post #512054
Posted Thursday, June 05, 2008 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 11:35 PM
Points: 100, Visits: 405
TRACEY
Thanks :)

For Size in MB and GB you can divide by 1024.
Disk.Size and Disk.FreeSpace.

Regards,
Raj
Post #512078
Posted Friday, June 06, 2008 3:35 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, January 24, 2014 7:02 AM
Points: 3,066, Visits: 1,413
Hey Rajdba, that's a great script, thanks for that. Do you have any other scripts that can help to monitor the databases. Thanks in advance.




My blog
Post #512766
Posted Friday, June 06, 2008 3:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 11:35 PM
Points: 100, Visits: 405
Welcome,
Please give me a list, I will send you.

Regards,
raj
Post #512769
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse