Scheduling Running Of Proc's

Hello, I am trying to figure out the most appropriate means to schedule a
procedure to run at a specified interval during the day.
I see the SQL Server has "SQL Server Agent" which appears to allow you to
schedule jobs to be run at periodic times, but unfortuneately the smallest
interval that I can find is every hour and I was looking to schedule this to
run every few minutes. What other means is available to me to schdeule the
procedure to run every few minutes?
I did play around with the "SQL Server Agent" and the scheduling seems to work
just fine, but the email notification never occurs. The message that I
located for why the email notification did not occur is as follows "The job
succeeded. The Job was invoked by Schedule 1 (MyFirstSchedule). The last
step to run was step 1 (First Step). NOTE: Failed to notify 'JimHeavey' via
network popup. NOTE: Failed to notify 'JimHeavey' via email."
What do I not have set up correctly to cause the email notification not to
occur?
Thanks in advance for your assistance!!!!!!!!!!You can schedule a job to run every minute if you want. Does your SQL mail
work outside of jobs? If you go into operators and try to send a test email,
it is successful?
Also, is your SQL server on a different subnet that where you are logged in?
Perhaps the routers are blocking the type of traffic that is produced by the
net send's (network popup's)
To test this you could go on the server and type in a command prompt, "net
send yourloginname hello" and see if it pops up on your screen.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Jim Heavey" <Annomous@.hotmail.com> wrote in message
news:O8v7OkumDHA.2772@.TK2MSFTNGP12.phx.gbl...
> Hello, I am trying to figure out the most appropriate means to schedule a
> procedure to run at a specified interval during the day.
> I see the SQL Server has "SQL Server Agent" which appears to allow you to
> schedule jobs to be run at periodic times, but unfortuneately the smallest
> interval that I can find is every hour and I was looking to schedule this
to
> run every few minutes. What other means is available to me to schdeule
the
> procedure to run every few minutes?
> I did play around with the "SQL Server Agent" and the scheduling seems to
work
> just fine, but the email notification never occurs. The message that I
> located for why the email notification did not occur is as follows "The
job
> succeeded. The Job was invoked by Schedule 1 (MyFirstSchedule). The last
> step to run was step 1 (First Step). NOTE: Failed to notify 'JimHeavey'
via
> network popup. NOTE: Failed to notify 'JimHeavey' via email."
> What do I not have set up correctly to cause the email notification not to
> occur?
> Thanks in advance for your assistance!!!!!!!!!!
>
>
>|||OK how do you configure the job to run every minute?
I am running the MSDE version of SQL server and I have gotten all of the tools
from an accedemic version of the software from school. It would not load the
full version of the product on my XP machine for some reason. But to me it
looks like full blown version of SQL server.
I have run anything through "SQL mail". Does this have to be configure for
the mail feature to work for job scheduling? Did not seem to mind going
through the script. How do I go about setting that up?
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:OzojzxumDHA.2820@.TK2MSFTNGP10.phx.gbl...
> You can schedule a job to run every minute if you want. Does your SQL mail
> work outside of jobs? If you go into operators and try to send a test email,
> it is successful?
> Also, is your SQL server on a different subnet that where you are logged in?
> Perhaps the routers are blocking the type of traffic that is produced by the
> net send's (network popup's)
> To test this you could go on the server and type in a command prompt, "net
> send yourloginname hello" and see if it pops up on your screen.
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Jim Heavey" <Annomous@.hotmail.com> wrote in message
> news:O8v7OkumDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > Hello, I am trying to figure out the most appropriate means to schedule a
> > procedure to run at a specified interval during the day.
> >
> > I see the SQL Server has "SQL Server Agent" which appears to allow you to
> > schedule jobs to be run at periodic times, but unfortuneately the smallest
> > interval that I can find is every hour and I was looking to schedule this
> to
> > run every few minutes. What other means is available to me to schdeule
> the
> > procedure to run every few minutes?
> >
> > I did play around with the "SQL Server Agent" and the scheduling seems to
> work
> > just fine, but the email notification never occurs. The message that I
> > located for why the email notification did not occur is as follows "The
> job
> > succeeded. The Job was invoked by Schedule 1 (MyFirstSchedule). The last
> > step to run was step 1 (First Step). NOTE: Failed to notify 'JimHeavey'
> via
> > network popup. NOTE: Failed to notify 'JimHeavey' via email."
> >
> > What do I not have set up correctly to cause the email notification not to
> > occur?
> >
> > Thanks in advance for your assistance!!!!!!!!!!
> >
> >
> >
> >
> >
>

Scheduling of snapshot agent

I have replicated a database as a snapshot replication. I have
scheduled distribution agent to run at the interval of one hour daily
from 9:00 AM to 5:30 PM. Do i also need to schedule snapshot agent
accordingly? What will be optimal schedule depending on the scheduling
of distribution agent?
Quick reply will be appreciated
Ali Raza Rajput
Database Administratoryes you have to configure the snapshot agent to run
before synchoronization runs otherwise no data will be updated
--
thanks,
joey
"perhiyar" wrote:

> I have replicated a database as a snapshot replication. I have
> scheduled distribution agent to run at the interval of one hour daily
> from 9:00 AM to 5:30 PM. Do i also need to schedule snapshot agent
> accordingly? What will be optimal schedule depending on the scheduling
> of distribution agent?
> Quick reply will be appreciated
> Ali Raza Rajput
> Database Administrator
>|||Thanks for reply joeydj
I am monitoring the snapshot agent and distribution agent.I have
noticed the latency for distribution agent upto 352373 (msec) on LAN.
I think it is abnormal what do you say?
Ali Raza Perhiyar
joeydj wrote:
> yes you have to configure the snapshot agent to run
> before synchoronization runs otherwise no data will be updated
> --
> thanks,
> --
> joey
>
> "perhiyar" wrote:
>sql

Schedule for Backing up logs

How does one determine a reasonable interval for backing up transaction
logs? For example, we have one small db that is only 1.8GB and the
transaction log is consistently around 1GB even after backup. I suppose I'd
need to add the "truncate" option to make it shrink? We only get really
busy about 1/2 of the week but right now our DBA backs up tlogs once daily
with a full backup weekly. Is this really sufficient?"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl...
> How does one determine a reasonable interval for backing up transaction
> logs? For example, we have one small db that is only 1.8GB and the
> transaction log is consistently around 1GB even after backup. I suppose
> I'd need to add the "truncate" option to make it shrink? We only get
> really busy about 1/2 of the week but right now our DBA backs up tlogs
> once daily with a full backup weekly. Is this really sufficient?
In case of a disaster is recovering to the beginning of the day acceptable?
In case of a disaster, is the recovery time to restore the weekly full and 5
days of transaction logs acceptable?
David|||Well, that's easy....HELL no and no...
Thanks that was quite simple. Doh
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u7N064VaGHA.4936@.TK2MSFTNGP05.phx.gbl...
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl...
> In case of a disaster is recovering to the beginning of the day
> acceptable?
> In case of a disaster, is the recovery time to restore the weekly full and
> 5 days of transaction logs acceptable?
> David
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl...
> How does one determine a reasonable interval for backing up transaction
> logs? For example, we have one small db that is only 1.8GB and the
> transaction log is consistently around 1GB even after backup. I suppose
I'd
> need to add the "truncate" option to make it shrink?
No, you would need to do a DBCC shrinkfile.
I would highly suggest not to. If your log keeps growing to 1 gb between
backups, that's basically what you need. Leave it at that.
If you keep shrinking it, you run the risk of getting disk level
fragmentation.

> We only get really
> busy about 1/2 of the week but right now our DBA backs up tlogs once daily
> with a full backup weekly. Is this really sufficient?
Depends, what's your disaster recovery plan call for? Can you live with
losing a day's worth of data?
I have one system where we do transaction log backups every 20 minutes,
another we just keep the db in simple mode. Depends on your needs/desires.

>|||Well, what's an acceptable data loss window? Whatever it is, that
becomes your new tlog backup frequency. For example, if losing the last
30 minute's worth of data is acceptable (no data loss is desirable but
you've got to be realistic about it) then do transaction log backups
every 30 minutes. That will almost certainly keep the size of the log
file down (or rather it will use less of the log file and so if you're
tight on disk space it'd be OK to shrink it down to cater for about 30
minutes worth of transactions).
If the recovery time of restoring a full backup & up to 5 daily tlogs is
unacceptable then you may want to think about slipping in regular
differentials. For example, if you do a full weekly backup, a daily
differential and half-hourly log backups then the recovery time would be
the time it takes to restore the last full backup, the most recent
differential and every log backup done since then (which would be up to
48 log backups, but they're be much smaller than your current log
backups because they're only 30 minutes worth of changes rather than 24
hours worth of changes). The differentials will make it possible to
essentially skip ahead to a much more recent recovery point without
having to apply all the log backups since the full backup, which will
speed up the recovery time. If that's still too long, then maybe you
should do nightly full backups with the half-hourly log backups (and
maybe even slip in a differential every couple hours).
It's basically just a case of juggling the DB, diff & log backups to
cover your maximum acceptable data loss window and maximum acceptable
recovery time. The more often you do log backups the smaller those log
backups will be and hence the less physical log file you'll need to
store those transactions between log backups.
*mike hodgson*
http://sqlnerd.blogspot.com
Tim Greenwood wrote:

>Well, that's easy....HELL no and no...
>Thanks that was quite simple. Doh
>"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>message news:u7N064VaGHA.4936@.TK2MSFTNGP05.phx.gbl...
>
>
>

Schedule for Backing up logs

How does one determine a reasonable interval for backing up transaction
logs? For example, we have one small db that is only 1.8GB and the
transaction log is consistently around 1GB even after backup. I suppose I'd
need to add the "truncate" option to make it shrink? We only get really
busy about 1/2 of the week but right now our DBA backs up tlogs once daily
with a full backup weekly. Is this really sufficient?"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl...
> How does one determine a reasonable interval for backing up transaction
> logs? For example, we have one small db that is only 1.8GB and the
> transaction log is consistently around 1GB even after backup. I suppose
> I'd need to add the "truncate" option to make it shrink? We only get
> really busy about 1/2 of the week but right now our DBA backs up tlogs
> once daily with a full backup weekly. Is this really sufficient?
In case of a disaster is recovering to the beginning of the day acceptable?
In case of a disaster, is the recovery time to restore the weekly full and 5
days of transaction logs acceptable?
David|||Well, that's easy....HELL no and no...
Thanks that was quite simple. Doh
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u7N064VaGHA.4936@.TK2MSFTNGP05.phx.gbl...
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl...
>> How does one determine a reasonable interval for backing up transaction
>> logs? For example, we have one small db that is only 1.8GB and the
>> transaction log is consistently around 1GB even after backup. I suppose
>> I'd need to add the "truncate" option to make it shrink? We only get
>> really busy about 1/2 of the week but right now our DBA backs up tlogs
>> once daily with a full backup weekly. Is this really sufficient?
> In case of a disaster is recovering to the beginning of the day
> acceptable?
> In case of a disaster, is the recovery time to restore the weekly full and
> 5 days of transaction logs acceptable?
> David
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl...
> How does one determine a reasonable interval for backing up transaction
> logs? For example, we have one small db that is only 1.8GB and the
> transaction log is consistently around 1GB even after backup. I suppose
I'd
> need to add the "truncate" option to make it shrink?
No, you would need to do a DBCC shrinkfile.
I would highly suggest not to. If your log keeps growing to 1 gb between
backups, that's basically what you need. Leave it at that.
If you keep shrinking it, you run the risk of getting disk level
fragmentation.
> We only get really
> busy about 1/2 of the week but right now our DBA backs up tlogs once daily
> with a full backup weekly. Is this really sufficient?
Depends, what's your disaster recovery plan call for? Can you live with
losing a day's worth of data?
I have one system where we do transaction log backups every 20 minutes,
another we just keep the db in simple mode. Depends on your needs/desires.
>|||This is a multi-part message in MIME format.
--000900060703070102060205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Well, what's an acceptable data loss window? Whatever it is, that
becomes your new tlog backup frequency. For example, if losing the last
30 minute's worth of data is acceptable (no data loss is desirable but
you've got to be realistic about it) then do transaction log backups
every 30 minutes. That will almost certainly keep the size of the log
file down (or rather it will use less of the log file and so if you're
tight on disk space it'd be OK to shrink it down to cater for about 30
minutes worth of transactions).
If the recovery time of restoring a full backup & up to 5 daily tlogs is
unacceptable then you may want to think about slipping in regular
differentials. For example, if you do a full weekly backup, a daily
differential and half-hourly log backups then the recovery time would be
the time it takes to restore the last full backup, the most recent
differential and every log backup done since then (which would be up to
48 log backups, but they're be much smaller than your current log
backups because they're only 30 minutes worth of changes rather than 24
hours worth of changes). The differentials will make it possible to
essentially skip ahead to a much more recent recovery point without
having to apply all the log backups since the full backup, which will
speed up the recovery time. If that's still too long, then maybe you
should do nightly full backups with the half-hourly log backups (and
maybe even slip in a differential every couple hours).
It's basically just a case of juggling the DB, diff & log backups to
cover your maximum acceptable data loss window and maximum acceptable
recovery time. The more often you do log backups the smaller those log
backups will be and hence the less physical log file you'll need to
store those transactions between log backups.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Tim Greenwood wrote:
>Well, that's easy....HELL no and no...
>Thanks that was quite simple. Doh
>"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>message news:u7N064VaGHA.4936@.TK2MSFTNGP05.phx.gbl...
>
>>"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>>news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl...
>>
>>How does one determine a reasonable interval for backing up transaction
>>logs? For example, we have one small db that is only 1.8GB and the
>>transaction log is consistently around 1GB even after backup. I suppose
>>I'd need to add the "truncate" option to make it shrink? We only get
>>really busy about 1/2 of the week but right now our DBA backs up tlogs
>>once daily with a full backup weekly. Is this really sufficient?
>>
>>In case of a disaster is recovering to the beginning of the day
>>acceptable?
>>In case of a disaster, is the recovery time to restore the weekly full and
>>5 days of transaction logs acceptable?
>>David
>>
>
>
--000900060703070102060205
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Well, what's an acceptable data loss window? Whatever it is, that
becomes your new tlog backup frequency. For example, if losing the
last 30 minute's worth of data is acceptable (no data loss is desirable
but you've got to be realistic about it) then do transaction log
backups every 30 minutes. That will almost certainly keep the size of
the log file down (or rather it will use less of the log file and so if
you're tight on disk space it'd be OK to shrink it down to cater for
about 30 minutes worth of transactions).<br>
<br>
If the recovery time of restoring a full backup & up to 5 daily
tlogs is unacceptable then you may want to think about slipping in
regular differentials. For example, if you do a full weekly backup, a
daily differential and half-hourly log backups then the recovery time
would be the time it takes to restore the last full backup, the most
recent differential and every log backup done since then (which would
be up to 48 log backups, but they're be much smaller than your current
log backups because they're only 30 minutes worth of changes rather
than 24 hours worth of changes). The differentials will make it
possible to essentially skip ahead to a much more recent recovery point
without having to apply all the log backups since the full backup,
which will speed up the recovery time. If that's still too long, then
maybe you should do nightly full backups with the half-hourly log
backups (and maybe even slip in a differential every couple hours).<br>
<br>
It's basically just a case of juggling the DB, diff & log backups
to cover your maximum acceptable data loss window and maximum
acceptable recovery time. The more often you do log backups the
smaller those log backups will be and hence the less physical log file
you'll need to store those transactions between log backups.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Tim Greenwood wrote:
<blockquote cite="mid%2310nr4WaGHA.4780@.TK2MSFTNGP02.phx.gbl"
type="cite">
<pre wrap="">Well, that's easy....HELL no and no...
Thanks that was quite simple. Doh
"David Browne" <davidbaxterbrowne no potted <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:meat@.hotmail.com">meat@.hotmail.com</a>> wrote in
message <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:u7N064VaGHA.4936@.TK2MSFTNGP05.phx.gbl">news:u7N064VaGHA.4936@.TK2MSFTNGP05.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl">news:%23j7aO2VaGHA.608@.TK2MSFTNGP02.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">How does one determine a reasonable interval for backing up transaction
logs? For example, we have one small db that is only 1.8GB and the
transaction log is consistently around 1GB even after backup. I suppose
I'd need to add the "truncate" option to make it shrink? We only get
really busy about 1/2 of the week but right now our DBA backs up tlogs
once daily with a full backup weekly. Is this really sufficient?
</pre>
</blockquote>
<pre wrap="">In case of a disaster is recovering to the beginning of the day
acceptable?
In case of a disaster, is the recovery time to restore the weekly full and
5 days of transaction logs acceptable?
David
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--000900060703070102060205--