SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


%FreeSpace Alert in SQL 2005


%FreeSpace Alert in SQL 2005

Author
Message
Jamie-769646
Jamie-769646
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
CrazyMan
CrazyMan
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3186 Visits: 1597
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
rajankjohn
rajankjohn
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2055 Visits: 575
You can use xp_fixeddrives
Jamie-769646
Jamie-769646
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 19
Cheers for your thoughts guys. I guess I will just have to stick to SP's and scheduled jobs.
TRACEY-320982
TRACEY-320982
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3272 Visits: 1014
Is there a way to get this as Total Size and Free and in GIG and MG.
rajdba
rajdba
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 430
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 Smile

Regards,
Raj
TRACEY-320982
TRACEY-320982
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3272 Visits: 1014
Thats a good script did not know you could call vbs scripts in SQL 2005.

Nice - thanks for sharing
rajdba
rajdba
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 430
TRACEY
Thanks Smile

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

Regards,
Raj
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6786 Visits: 1439
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

rajdba
rajdba
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 430
Welcome,
Please give me a list, I will send you.

Regards,
raj
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search