How to create disk space alert?

I would like SQL Server (7 and 2K) to alert me when disk
space drops to a preset level. Is there a way to do this
using sql agent alerts? I looked at the alerts that are
pre-defined, they are more DB oriented. If someone could
point me to some examples it would be appreciated.
Dave K.Which OS are you running on ?
W2KAS has "quota" management , so you might look into it.
there are also commercial prods available
W2K quotas can be found with the Windows Exploder -select disk--> right
click-->properties--> Quota tab
"Dave K" <anonymous@.discussions.microsoft.com> wrote in message
news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...

quote:

---

> I would like SQL Server (7 and 2K) to alert me when disk
> space drops to a preset level. Is there a way to do this
> using sql agent alerts? I looked at the alerts that are
> pre-defined, they are more DB oriented. If someone could
> point me to some examples it would be appreciated.
> Dave K.

---

|||Can you do this without putting a limit on disk space?
"MaSa" <matti putTheDotHere saukkonen _ POISTA_ mandatum.fi> wrote in
message news:uWp2rKE3DHA.2544@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Which OS are you running on ?
> W2KAS has "quota" management , so you might look into it.
> there are also commercial prods available
> W2K quotas can be found with the Windows Exploder -select disk--> right
> click-->properties--> Quota tab
>
> "Dave K" <anonymous@.discussions.microsoft.com> wrote in message
> news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...
>

---

|||Win2K. BTW, I don't want to restrict anything with a
quota, I just want to be notified when I run out of backup
space. I have some 15GB databases that eat up space
fast. If the regular maintenance backups don't delete the
prior jobs, and sometimes they don't, I want to be
notified before the backup fails, not after.
Dave K.

quote:

---

>--Original Message--
>Which OS are you running on ?
>W2KAS has "quota" management , so you might look into it.
>there are also commercial prods available
>W2K quotas can be found with the Windows Exploder -select

---

disk--> right

quote:

---

>click-->properties--> Quota tab
>
>"Dave K" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...
this[QUOTE]
could[QUOTE]
>
>.
>

---

|||Here's how I do it.
I scan my SQL instances to run xp_fixeddrives to get free
disk space for each hard drive and check each to see
whether it's below a threshold. If yes, I raise a SQL
error of severity 17. My errorlog monitoring setup will
pick it up from there and page the DBAs.
Of course, this can be consistently done with a NetIQ
configuration as well.
Linchi

quote:

---

>--Original Message--
>I would like SQL Server (7 and 2K) to alert me when disk
>space drops to a preset level. Is there a way to do this
>using sql agent alerts? I looked at the alerts that are
>pre-defined, they are more DB oriented. If someone could
>point me to some examples it would be appreciated.
>Dave K.
>.
>

---

|||I put together this vbs file and run it from task scheduler, works for me.
'*****************************
'Monitors Drive Space and emails if below threshhold
'Application Variables
On error resume next
'Application Variables
Dim sEmailFrom, sEmailTo, sMyMailServer, sDriveC, sDriveD, sBodyText,
sSubject
sEmailFrom = "support@.assoft.com.au"
sEmailTo = "support@.assoft.com.au"
sConEmailMonitor = "dgrover@.assoft.com.au"
sMyMailServer = "assoftsvr"
iEmailPort = 25
'Does drive C: or D: have less than 3 gigabyte free if so email support
sDriveC = RetDriveSpace("c:\")
sDriveD = RetDriveSpace("d:\")
If Trim(sDriveC & sDriveD) <> "" Then
sBodyText = "CokeShop Hard Drive Storage, is getting low on space" & vbcrlf
& _
"Please rectify this problem Urgently" & VbCrLf & VbCrLf & _
"Drive C: =" & sDriveC & " Drive D: =" & sDriveD & VbCrLf &
VbCrLf & _
"Automated email from CokeShop System " & VbCrLf &
FormatDateTime(Now,1)
sSubject = "CokeShop Drive space low !!!"
SendEmail sSubject,sBodyText
End If
Function RetDriveSpace(drvpath)
Dim fso, d, s
Set fso = CreateObject("Scripting.FileSystemObject")
Set d = fso.GetDrive(fso.GetDriveName(fso.GetAbsolutePathName(drvpath)))
If FormatNumber(d.AvailableSpace/(1024 * 1000000), 1) < 3 Then
s = FormatNumber(d.AvailableSpace/(1024 * 1000000), 1)
End If
RetDriveSpace = s
Set fso = Nothing
Set d = Nothing
End Function
Function SendEmail(sSubJect, sBody)
'***************************************
***************
'*** Send the message Using CDOSYS Win2k & Win2003 ****
'***************************************
***************
' CDO mail object
sch = "http://schemas.microsoft.com/cdo/configuration/"
Set cdoConfig = CreateObject("CDO.Configuration")
cdoConfig.Fields.Item(sch & "sendusing") = 2
cdoConfig.Fields.Item(sch & "smtpserverport") = iEmailPort
cdoConfig.Fields.Item(sch & "smtpserver") = sMyMailServer
cdoConfig.fields.update
Set cdoMessage = CreateObject("CDO.Message")
Set cdoMessage.Configuration = cdoConfig
cdoMessage.From = sEmailFrom
cdoMessage.To = sEmailTo
cdoMessage.BCC = sConEmailMonitor
cdoMessage.Subject = sSubJect
cdoMessage.TextBody = sBody
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/smtpauthenti
cate").value = 1 ' use clear text authenticate
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendpassword
").value ="mypassword"
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendusername
").value ="yourusername"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-MSMail-Priority") =
"High"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Priority") = 2
cdoMessage.Fields.Item("urn:schemas:mailheader:Keywords") = "COKESHOP"
cdoMessage.Fields.Item("urn:schemas:mailheader:Sensitivity") =
"Company-Confidential"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Message-Flag") = "Do
not Forward"
cdoMessage.Fields.Update
cdoMessage.Send
Set cdoMessage = Nothing
Set cdoConfig = Nothing
End Function
'*****************************
Regards
Don Grover
"Dave K" <anonymous@.discussions.microsoft.com> wrote in message
news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...

quote:

---

> I would like SQL Server (7 and 2K) to alert me when disk
> space drops to a preset level. Is there a way to do this
> using sql agent alerts? I looked at the alerts that are
> pre-defined, they are more DB oriented. If someone could
> point me to some examples it would be appreciated.
> Dave K.

---

sql

How to create disk space alert?

I would like SQL Server (7 and 2K) to alert me when disk
space drops to a preset level. Is there a way to do this
using sql agent alerts? I looked at the alerts that are
pre-defined, they are more DB oriented. If someone could
point me to some examples it would be appreciated.
Dave K.Which OS are you running on ?
W2KAS has "quota" management , so you might look into it.
there are also commercial prods available
W2K quotas can be found with the Windows Exploder -select disk--> right
click-->properties--> Quota tab
"Dave K" <anonymous@.discussions.microsoft.com> wrote in message
news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...
> I would like SQL Server (7 and 2K) to alert me when disk
> space drops to a preset level. Is there a way to do this
> using sql agent alerts? I looked at the alerts that are
> pre-defined, they are more DB oriented. If someone could
> point me to some examples it would be appreciated.
> Dave K.|||Can you do this without putting a limit on disk space?
"MaSa" <matti putTheDotHere saukkonen _ POISTA_ mandatum.fi> wrote in
message news:uWp2rKE3DHA.2544@.TK2MSFTNGP10.phx.gbl...
> Which OS are you running on ?
> W2KAS has "quota" management , so you might look into it.
> there are also commercial prods available
> W2K quotas can be found with the Windows Exploder -select disk--> right
> click-->properties--> Quota tab
>
> "Dave K" <anonymous@.discussions.microsoft.com> wrote in message
> news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...
> > I would like SQL Server (7 and 2K) to alert me when disk
> > space drops to a preset level. Is there a way to do this
> > using sql agent alerts? I looked at the alerts that are
> > pre-defined, they are more DB oriented. If someone could
> > point me to some examples it would be appreciated.
> >
> > Dave K.
>|||Win2K. BTW, I don't want to restrict anything with a
quota, I just want to be notified when I run out of backup
space. I have some 15GB databases that eat up space
fast. If the regular maintenance backups don't delete the
prior jobs, and sometimes they don't, I want to be
notified before the backup fails, not after.
Dave K.
>--Original Message--
>Which OS are you running on ?
>W2KAS has "quota" management , so you might look into it.
>there are also commercial prods available
>W2K quotas can be found with the Windows Exploder -select
disk--> right
>click-->properties--> Quota tab
>
>"Dave K" <anonymous@.discussions.microsoft.com> wrote in
message
>news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...
>> I would like SQL Server (7 and 2K) to alert me when disk
>> space drops to a preset level. Is there a way to do
this
>> using sql agent alerts? I looked at the alerts that are
>> pre-defined, they are more DB oriented. If someone
could
>> point me to some examples it would be appreciated.
>> Dave K.
>
>.
>|||Here's how I do it.
I scan my SQL instances to run xp_fixeddrives to get free
disk space for each hard drive and check each to see
whether it's below a threshold. If yes, I raise a SQL
error of severity 17. My errorlog monitoring setup will
pick it up from there and page the DBAs.
Of course, this can be consistently done with a NetIQ
configuration as well.
Linchi
>--Original Message--
>I would like SQL Server (7 and 2K) to alert me when disk
>space drops to a preset level. Is there a way to do this
>using sql agent alerts? I looked at the alerts that are
>pre-defined, they are more DB oriented. If someone could
>point me to some examples it would be appreciated.
>Dave K.
>.
>|||I put together this vbs file and run it from task scheduler, works for me.
'*****************************
'Monitors Drive Space and emails if below threshhold
'Application Variables
On error resume next
'Application Variables
Dim sEmailFrom, sEmailTo, sMyMailServer, sDriveC, sDriveD, sBodyText,
sSubject
sEmailFrom = "support@.assoft.com.au"
sEmailTo = "support@.assoft.com.au"
sConEmailMonitor = "dgrover@.assoft.com.au"
sMyMailServer = "assoftsvr"
iEmailPort = 25
'Does drive C: or D: have less than 3 gigabyte free if so email support
sDriveC = RetDriveSpace("c:\")
sDriveD = RetDriveSpace("d:\")
If Trim(sDriveC & sDriveD) <> "" Then
sBodyText = "CokeShop Hard Drive Storage, is getting low on space" & vbcrlf
& _
"Please rectify this problem Urgently" & VbCrLf & VbCrLf & _
"Drive C: =" & sDriveC & " Drive D: =" & sDriveD & VbCrLf &
VbCrLf & _
"Automated email from CokeShop System " & VbCrLf &
FormatDateTime(Now,1)
sSubject = "CokeShop Drive space low !!!"
SendEmail sSubject,sBodyText
End If
Function RetDriveSpace(drvpath)
Dim fso, d, s
Set fso = CreateObject("Scripting.FileSystemObject")
Set d = fso.GetDrive(fso.GetDriveName(fso.GetAbsolutePathName(drvpath)))
If FormatNumber(d.AvailableSpace/(1024 * 1000000), 1) < 3 Then
s = FormatNumber(d.AvailableSpace/(1024 * 1000000), 1)
End If
RetDriveSpace = s
Set fso = Nothing
Set d = Nothing
End Function
Function SendEmail(sSubJect, sBody)
'******************************************************
'*** Send the message Using CDOSYS Win2k & Win2003 ****
'******************************************************
' CDO mail object
sch = "http://schemas.microsoft.com/cdo/configuration/"
Set cdoConfig = CreateObject("CDO.Configuration")
cdoConfig.Fields.Item(sch & "sendusing") = 2
cdoConfig.Fields.Item(sch & "smtpserverport") = iEmailPort
cdoConfig.Fields.Item(sch & "smtpserver") = sMyMailServer
cdoConfig.fields.update
Set cdoMessage = CreateObject("CDO.Message")
Set cdoMessage.Configuration = cdoConfig
cdoMessage.From = sEmailFrom
cdoMessage.To = sEmailTo
cdoMessage.BCC = sConEmailMonitor
cdoMessage.Subject = sSubJect
cdoMessage.TextBody = sBody
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/smtpauthenti
cate").value = 1 ' use clear text authenticate
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendpassword
").value ="mypassword"
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendusername
").value ="yourusername"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-MSMail-Priority") ="High"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Priority") = 2
cdoMessage.Fields.Item("urn:schemas:mailheader:Keywords") = "COKESHOP"
cdoMessage.Fields.Item("urn:schemas:mailheader:Sensitivity") ="Company-Confidential"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Message-Flag") = "Do
not Forward"
cdoMessage.Fields.Update
cdoMessage.Send
Set cdoMessage = Nothing
Set cdoConfig = Nothing
End Function
'*****************************
Regards
Don Grover
"Dave K" <anonymous@.discussions.microsoft.com> wrote in message
news:048f01c3dc3c$46938f90$a001280a@.phx.gbl...
> I would like SQL Server (7 and 2K) to alert me when disk
> space drops to a preset level. Is there a way to do this
> using sql agent alerts? I looked at the alerts that are
> pre-defined, they are more DB oriented. If someone could
> point me to some examples it would be appreciated.
> Dave K.

How to create a single reusable alert in SQL Server Agent?

Hi,
I have numerous SQL scripts that run as jobs under SQL Server Agent.
I've been playing with the alert system and got it working using a
test script.
However, it seems that it's not possible to create a single alert and
associate it with every script.
When right-clicking on a job under SQL Server Agent in SSMS and
selecting the 'Alerts' tab and the 'Add...' option, there is no way
to
select an alert that I have already created. So, this forces me to
create a new alert, which basically does exactly the same thing
(raises an alert when the job message contains the word 'error') as
one that I created previously, meaning the only difference will be
the
alert name.
The problem is that I have over a two dozen scripts that I want to
set
an alert on.
Am I missing something or are multiple duplicate scripts the right
way
to go?
Thanks,
Frank.
You can also write a VBScript that sends email alerts using SMTP. This way
you can pass parameters like probably the names of the jobs and call this
script inside your SQL job. This is what I did for log shipping alerts for a
clustered SQL Server 2000 since IMAP is not supported in this scenario
<francis.moore@.gmail.com> wrote in message
news:801cbe16-c882-4f96-bfb6-7dec556ddd50@.j44g2000hsj.googlegroups.com...
> Hi,
> I have numerous SQL scripts that run as jobs under SQL Server Agent.
> I've been playing with the alert system and got it working using a
> test script.
> However, it seems that it's not possible to create a single alert and
> associate it with every script.
> When right-clicking on a job under SQL Server Agent in SSMS and
> selecting the 'Alerts' tab and the 'Add...' option, there is no way
> to
> select an alert that I have already created. So, this forces me to
> create a new alert, which basically does exactly the same thing
> (raises an alert when the job message contains the word 'error') as
> one that I created previously, meaning the only difference will be
> the
> alert name.
> The problem is that I have over a two dozen scripts that I want to
> set
> an alert on.
> Am I missing something or are multiple duplicate scripts the right
> way
> to go?
> Thanks,
> Frank.
|||Tibor,
Thanks for the response.
I'm still confused, although I can see what you are saying.

> When an alert fires, you can specify to execute a job.
I can see that now, but what I want to do is execute a job using SQL
Server Agent and then if that job fails, I want it to trigger an alert
that emails me the fact that the job has failed. I've had a few
occasions recently where batch jobs have failed and it hasn't been
noticed until a day or so later.
In SSMS, when selecting a job under SQL Server Agent | Jobs and right-
clicking, you can get to a Properties dialog for that job.
On that dialog there are some tabs, General, Steps, Schedules, Alerts,
Notifications and Targets.
Selecting Alerts give you a page with a button titled 'Add...' at the
bottom of it.
Clicking 'Add...' brings up a 'New Alert' dialog.
I am expecting to enter details in there to create an alert for that
job.
It allows me to do this once, but when I go to the next job, to create
a similar alert for that job, I can neither pick the alert that I just
created, nor can I create another alert similar to the pervious one as
it says that one like this already exists.
What I would really like to do is create one alert (i.e. when message
contains text like 'error') and then select that to be the alert that
I can use for that job and possibly others as well. However, it
doesn't seem to work like that. So, I'm still confused.
So, is it possible to use alerts as I want to in SQL Server 2005?
And if not, how would you let an operator know that a batch job has
failed (preferably by email)?
Many thanks for your time,
Frank.
|||Tibor,
Once someone points it out to you it's becomes so blatantly
obvious :-)
You're a star!
Many thanks,
Frank.
|||On Nov 27, 12:40 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I'm glad you "see the light", Frank.
> Actually, I've always been confused what the "Alert" tab does for a job. I do know what alerts are
> (EventLog, Perfmon and now WMI), but I just failed to understand why we would have an alert tab for
> the job. It just felt ... backwards. You configure and alert, and then what job to fire. So your
> post made me investigate what this alert tab (for a job) does and indeed it doesn't bring any new
> functionality, it just allow us to configure that this job is to be fired for an alert - in a pretty
> backwards kind of way (and indeed the same config as you do for the alert).
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <francis.mo...@.gmail.com> wrote in message
> news:d59e3dd6-8aa5-4bea-bc03-515257f5a3b3@.a35g2000prf.googlegroups.com...
>
>
>
> - Show quoted text -
Frank - you should check out the xSQL Software's RSS Reporter for SQL
Server (http://www.xsqlsoftware.com/Product/
Sql_Server_Rss_Reporter.aspx) - it is a great way to notify someone on
the status of SQL Server Jobs. It automatically generates RSS feeds
that aggregate job status information from multiple servers - it
avoids the hassles associated with the email notifications, it allows
you to drill down on a job to see what step of it actually failed
etc.
JC