Hi all!
Is it possible to schedule reports to refresh overnight? Particularly if
they take a long time to return data - for example in business objects i
would normally schedule a report to refresh overnight via the broadcast
agent console...is the same thing at all possible in reporting services?
does RS have sheduling functionality?
many thanks
GregYes, it has extensive functionality in this area.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:5E5D3B2C-AE87-4ABB-8E84-469A51511863@.microsoft.com...
> Hi all!
> Is it possible to schedule reports to refresh overnight? Particularly if
> they take a long time to return data - for example in business objects i
> would normally schedule a report to refresh overnight via the broadcast
> agent console...is the same thing at all possible in reporting services?
> does RS have sheduling functionality?
> many thanks
> Greg|||ok.....anything further? lol
HOW DO I DO IT!!!
anyone'
"Bruce L-C [MVP]" wrote:
> Yes, it has extensive functionality in this area.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:5E5D3B2C-AE87-4ABB-8E84-469A51511863@.microsoft.com...
> > Hi all!
> >
> > Is it possible to schedule reports to refresh overnight? Particularly if
> > they take a long time to return data - for example in business objects i
> > would normally schedule a report to refresh overnight via the broadcast
> > agent console...is the same thing at all possible in reporting services?
> > does RS have sheduling functionality?
> >
> > many thanks
> >
> > Greg
>
>|||It seemed like you just wondered if it was possible. I thought that maybe
you weren't using Reporting Services and were just making plans.
Anyway, a few of what you need to understand. Search in books online for
snapshot, schedule and history. Reading up on those things should get you
started. Here are a few links to get you rolling:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsmain/htm/rsc_ov_using_v1_4u43.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_catalog_v1_9zsm.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_scheduling_v1_2qgk.asp
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:593EBCB8-9CA6-42BB-994C-CFAB8F44495F@.microsoft.com...
> ok.....anything further? lol
> HOW DO I DO IT!!!
> anyone'
> "Bruce L-C [MVP]" wrote:
>> Yes, it has extensive functionality in this area.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Greg" <Greg@.discussions.microsoft.com> wrote in message
>> news:5E5D3B2C-AE87-4ABB-8E84-469A51511863@.microsoft.com...
>> > Hi all!
>> >
>> > Is it possible to schedule reports to refresh overnight? Particularly
>> > if
>> > they take a long time to return data - for example in business objects
>> > i
>> > would normally schedule a report to refresh overnight via the
>> > broadcast
>> > agent console...is the same thing at all possible in reporting
>> > services?
>> > does RS have sheduling functionality?
>> >
>> > many thanks
>> >
>> > Greg
>>
Showing posts with label example. Show all posts
Showing posts with label example. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Scheduling Montly Report
Hi all,
I currently have a report which needs to be scheduled to run on every 1st of the month to get records of last month. For example, on Feb. 01 the report will get all records by the date range of Jan. 01 to Jan. 31. My current report does not have any defined report parameter. It gets all the date range it finds from the table.
Please advise how can I start with this.
Any help is much appreciated! Thanks!
Hi,
Couldnt get it completely.
you want to run a report on monthly basis, then you need to have two datetime parameters i.e. from_time and to_time, then you want default value to be last months dates, use expression for the defualt value....
your expr. for "from time" should be like
=switch(month(now())=1,"01/01/2007",
month(now())=2,"02/01/2007, and in same way you can have it for to time frame....
HTH
Priyank
|||Hi
As i understand the requirement over here is to have a report which shows the data for previous month
say the report is executed on mar 1st then the report should display data till feb28th correct?
For this a report has to be created with 2 parameters Startdate and EndDate.
However subscription can be created in Report Manager.
Regards
Smitha
|||You don't need a param for this, although to give you an perfectly appropriate example it would be good to know if you are calling a stored procedure or writing the query directly in the report, or what.
Let's say you are writing the query directly in the report, not a stored procedure.
You already know that you can schedule it to run on the first of the month, right? Here is a query that will do what you want, given that you know you are running it every month on the first:
Code Snippet
select <WHATEVER> from <YOUR TABLE> where
DATEDIFF(month,GETDATE()-1,<YOUR DATE COLUMN>) = 0
' the default behavior of the date subtraction is by days, see?
' and you're running the subscription on the first of the month,
' so this works.
Let's make it better. Here is a query that will do what you want, no matter what date of the month it is run on, which is : provide the results for the month *before* the month in which it is run:
Code Snippet
select <WHATEVER> from <YOUR TABLE> where
DATEDIFF(month,DATEADD(month,-1,GETDATE()),
<YOUR DATE COLUMN>) = 0
Now, if you prefer, you can add a parameter and give your parameter the default value of Today (you may have to do some CASTing in here, not checking this):
Code Snippet
select <WHATEVER> from <YOUR TABLE> where
DATEDIFF(month,DATEADD(month,-1,@.YourParam),
<YOUR DATE COLUMN>) = 0
... hope this helps,
>L<
|||Thank you all for the helpful solutions.
Yes basically it is just to get a report of previous month when it is scheduled to run.
|||Lisa that's a cool little function!
Only question I have it whether it would work if you had data for more than one year in the table? i.e If you had 2 rows, one with July 2006 and one with July 2007 wouldn't the DateDiff(month, etc) return 0 for that even though the year is different?
As my own $0.02 I usually use something like
Beginning of last month
DATEADD(month,DATEDIFF(month,0,getdate)-1,0)
Beginning of this month
DATEADD(month,DATEDIFF(month,0,getdate),0)
Beginning of next month
DATEADD(month,DATEDIFF(month,0,getdate)+1,0)
which is sort of based off similar logic. Found it on a forum, and loved it. Can also do days/weeks by just switching the period name.
|||>> whether it would work if you had data for more than one year in the table
It will actually work fine, D, check it out:
select datediff(month, '1/1/2007','1/1/2004')
will return -36 even though both dates are (obviously) in January. 3 * 12.
It's a *difference of months*. Not a difference of *month numbers* <s>.
>L<
|||Right you are - I should have run it through Query Analyzer first
Scheduling Montly Report
Hi all,
I currently have a report which needs to be scheduled to run on every 1st of the month to get records of last month. For example, on Feb. 01 the report will get all records by the date range of Jan. 01 to Jan. 31. My current report does not have any defined report parameter. It gets all the date range it finds from the table.
Please advise how can I start with this.
Any help is much appreciated! Thanks!
Hi,
Couldnt get it completely.
you want to run a report on monthly basis, then you need to have two datetime parameters i.e. from_time and to_time, then you want default value to be last months dates, use expression for the defualt value....
your expr. for "from time" should be like
=switch(month(now())=1,"01/01/2007",
month(now())=2,"02/01/2007, and in same way you can have it for to time frame....
HTH
Priyank
|||Hi
As i understand the requirement over here is to have a report which shows the data for previous month
say the report is executed on mar 1st then the report should display data till feb28th correct?
For this a report has to be created with 2 parameters Startdate and EndDate.
However subscription can be created in Report Manager.
Regards
Smitha
|||You don't need a param for this, although to give you an perfectly appropriate example it would be good to know if you are calling a stored procedure or writing the query directly in the report, or what.
Let's say you are writing the query directly in the report, not a stored procedure.
You already know that you can schedule it to run on the first of the month, right? Here is a query that will do what you want, given that you know you are running it every month on the first:
Code Snippet
select <WHATEVER> from <YOUR TABLE> where
DATEDIFF(month,GETDATE()-1,<YOUR DATE COLUMN>) = 0
' the default behavior of the date subtraction is by days, see?
' and you're running the subscription on the first of the month,
' so this works.
Let's make it better. Here is a query that will do what you want, no matter what date of the month it is run on, which is : provide the results for the month *before* the month in which it is run:
Code Snippet
select <WHATEVER> from <YOUR TABLE> where
DATEDIFF(month,DATEADD(month,-1,GETDATE()),
<YOUR DATE COLUMN>) = 0
Now, if you prefer, you can add a parameter and give your parameter the default value of Today (you may have to do some CASTing in here, not checking this):
Code Snippet
select <WHATEVER> from <YOUR TABLE> where
DATEDIFF(month,DATEADD(month,-1,@.YourParam),
<YOUR DATE COLUMN>) = 0
... hope this helps,
>L<
|||
Thank you all for the helpful solutions.
Yes basically it is just to get a report of previous month when it is scheduled to run.
|||Lisa that's a cool little function!
Only question I have it whether it would work if you had data for more than one year in the table? i.e If you had 2 rows, one with July 2006 and one with July 2007 wouldn't the DateDiff(month, etc) return 0 for that even though the year is different?
As my own $0.02 I usually use something like
Beginning of last month
DATEADD(month,DATEDIFF(month,0,getdate)-1,0)
Beginning of this month
DATEADD(month,DATEDIFF(month,0,getdate),0)
Beginning of next month
DATEADD(month,DATEDIFF(month,0,getdate)+1,0)
which is sort of based off similar logic. Found it on a forum, and loved it. Can also do days/weeks by just switching the period name.
|||>> whether it would work if you had data for more than one year in the table
It will actually work fine, D, check it out:
select datediff(month, '1/1/2007','1/1/2004')
will return -36 even though both dates are (obviously) in January. 3 * 12.
It's a *difference of months*. Not a difference of *month numbers* <s>.
>L<
|||Right you are - I should have run it through Query Analyzer first
Friday, March 23, 2012
Scheduled tasks
Is it possible to schedule tasks in SQL Express, for example a database backup, or to periodically execute a stored procedure?
hi Allan,
yes, it is possible, but not using the SQL Server Agent as SQLExpress does not provide the relative bits.. you have so to rely on the native OS scheduler, third party (or self written alternative) scheduler, or rely on the Service Broker features..
personally I usually go for the native OS scheduler, where you can define a list of xx.sql scripts to be executed by a xx.cmd file.. then the xx.cmd file, connecting to SqlCMD.exe and executing each defined xx.sql script, has to be scheduled... quiete simple and not expensive... results are output to a text file similar to:
[cmdfile.cmd]
ECHO. >>Backing up databases
SqlCmd -E -S(Local)\InstanceName -i"c:\somefolder\ScheduledBackup.sql" >c:\SomeFolder\ScheduledBackupLog.txt
ECHO. >>Sending results via mail to aministrators
SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Mailing log of - ' + CONVERT(varchar, GETDATE());" >E:\e-VbHot\Files\Log\MailingLogBCKschedulato.txt
SqlCmd -E -S(Local)\InstanceName -Q"SET NOCOUNT ON; DECLARE @.ret int;EXEC @.ret = [applicationDB].[dbo].[amSMTPmail] @.Server = N'smtp server name', @.Sender = N'scheduledbackup@.my_company.com', @.AddressesTO = N'admin_name@.my_company.com', @.AddressesCC = N'me@.me.com', @.AddressesCCN = NULL, @.AttachFiles = N'c:\SomeFolder\ScheduledBackupLog.txt', @.Subject = N'Backup performed', @.MessageBody = N'Backup performed', @.UserName = NULL, @.UserPassword = NULL; SELECT @.ret AS [Execution result];" >>c:\SomeFolder\MailingLog.txt
[/cmdfile.cmd]
as you can see, I do personally even send the c:\SomeFolder\ScheduledBackupLog.txt resulting file via e-mail to a "list" of addresses.. this is performed via a (free) CLR stored procedure of mine, amDBObj, where the .Net code implements a poor's man SMTP mail feature to "replace" (ok... it is not comparable with) Database Mail missing feature of SQLExpress..
regards
|||Thanks for the info Andrea. I will look into it.
Thanks, too, for the offer of the CLR email proc - but I already did one myself :)
sqlWednesday, March 21, 2012
Scheduled Stored Procedure
Can someone help me find a source of information or give me an example of a scheduled strored procedure ... is it even possible?
Here's the scenario:
I have a field in one of my tables "date_due," once a day I need to check this field and send an email to the owner of any record (thier email address is also stored in this table) where "date_due" is equal to today.
Any help would be greatly appreciated ... thanks!Write a DTS Package. Use an ActiveX object and write some VB code to connect to the DB, run the SP, then send out the necessary emails based upon the results of your SP (remember to close your DB connection at the end). Then schedule the DTS Package.|||Write a procedure to check the table. Use xp_sendmail to email to the appropriate recipients. Use SQL Server Agent to schedule a job that runs the procedure once a day. All this can be done easily and is easily researched in Books Online.|||derrickleggett,
Is xp_sendmail native to SQL Server 7.0+ or does it need to be set up seperately? Let me know ... thanks!|||xp_sendmail is part of SQL Server (including 7).
exec @.Severity = master..xp_sendmail
@.recipients = @.Recipients
, @.subject = @.Subject
, @.message = @.Message
Here's the scenario:
I have a field in one of my tables "date_due," once a day I need to check this field and send an email to the owner of any record (thier email address is also stored in this table) where "date_due" is equal to today.
Any help would be greatly appreciated ... thanks!Write a DTS Package. Use an ActiveX object and write some VB code to connect to the DB, run the SP, then send out the necessary emails based upon the results of your SP (remember to close your DB connection at the end). Then schedule the DTS Package.|||Write a procedure to check the table. Use xp_sendmail to email to the appropriate recipients. Use SQL Server Agent to schedule a job that runs the procedure once a day. All this can be done easily and is easily researched in Books Online.|||derrickleggett,
Is xp_sendmail native to SQL Server 7.0+ or does it need to be set up seperately? Let me know ... thanks!|||xp_sendmail is part of SQL Server (including 7).
exec @.Severity = master..xp_sendmail
@.recipients = @.Recipients
, @.subject = @.Subject
, @.message = @.Message
Saturday, February 25, 2012
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...
>
>
>
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--
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--
Tuesday, February 21, 2012
schedule a trace
Can someone share an example of how to schedule a trace? I have seen some stuff online that used xp_trace extended procedures however examples are not detailed enough for me to create a script of my own. So if someone can help me with their knowledge that will be great.
thanks
See SQL Server 2005 Books Online topics:
Scheduling Traces
http://msdn2.microsoft.com/en-us/library/ms187656.aspx
Using SQL Trace
http://msdn2.microsoft.com/en-us/library/ms191443.aspx
Schedule a SQL statement to run from SQL Server ?
How can I schedule a SQL Statement to run from SQL Server say every day at 4
pm ?
For example, I would like to run the following sql statement every day at 4
pm:
"delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically on SQL server ?
Thanks.
Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at 4 pm ?
> For example, I would like to run the following sql statement every day at 4 pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule it to run automatically
> on SQL server ?
> Thanks.
>
|||"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
Create a new job in the SQL Server Agent, where it runs your T-SQL
statement. Then schedule it to run every 4 hours (see the Schedules tab).
Steve
|||Have you looked at Management / SQL Server Agent / Jobs?
Also, since other things can be running on the server, and your job won't
always run at *precisely* 4:00, and if precision in the amount of data you
delete is important, you may want to say:
DECLARE @.yesterday400 SMALLDATETIME
SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
GETDATE())))
DELETE myTable WHERE myCol < @.yesterday400
(This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
opposed to the time the statement is executed, which could be a few seconds
off.)
You should also consider putting your DELETE statement in a stored
procedure, and calling the stored procedure from the job.
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
> Thanks.
>
|||Thank you all for your replies.
In Enterprise Manager, I do not see Management or SQL Server Agent.
I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
run. Is this what you all meant ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>
|||I think you're looking in the wrong place.
http://www.aspfaq.com/img/2403.gif
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:#Si9kYcGFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Thank you all for your replies.
> In Enterprise Manager, I do not see Management or SQL Server Agent.
> I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
> run. Is this what you all meant ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
at
>
|||Thank you all for your replies.
I found the Management / SQL Server Agent / Jobs.
When I went there, and click "Jobs", I did see the job that I created
earlier using the menu "Tools" - "Job Scheduling".
So, creating a job thru menu "Tools" - "Job Scheduling" and thru Management
/ SQL Server Agent / Jobs are the same, right ?
Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8FBAVcGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job won't
> always run at *precisely* 4:00, and if precision in the amount of data you
> delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
> GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
> opposed to the time the statement is executed, which could be a few
> seconds
> off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> 4
> 4
>
|||> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
Management
> / SQL Server Agent / Jobs are the same, right ?
To be honest, I've never done the former, so I can't guarantee that they
produce identical results.
I think I prefer the latter because you can visually see which server is
creating the job.
A
|||Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OZ1AtTdGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Management
> To be honest, I've never done the former, so I can't guarantee that they
> produce identical results.
> I think I prefer the latter because you can visually see which server is
> creating the job.
> A
>
|||Aaron,
Just for my curriosity - What would be the adavantage of putting the DELETE
statement in a stored proc? I know that a Stored Proc often performs a bit
faster and it's good practice to create stored proc's for recurring scripts,
but is there any specific reason here that I'm missing?
Regards
Steen
Aaron [SQL Server MVP] wrote:[vbcol=seagreen]
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job
> won't always run at *precisely* 4:00, and if precision in the amount
> of data you delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY,
> 0, GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00
> PM, as opposed to the time the statement is executed, which could be
> a few seconds off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
pm ?
For example, I would like to run the following sql statement every day at 4
pm:
"delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically on SQL server ?
Thanks.
Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at 4 pm ?
> For example, I would like to run the following sql statement every day at 4 pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule it to run automatically
> on SQL server ?
> Thanks.
>
|||"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
Create a new job in the SQL Server Agent, where it runs your T-SQL
statement. Then schedule it to run every 4 hours (see the Schedules tab).
Steve
|||Have you looked at Management / SQL Server Agent / Jobs?
Also, since other things can be running on the server, and your job won't
always run at *precisely* 4:00, and if precision in the amount of data you
delete is important, you may want to say:
DECLARE @.yesterday400 SMALLDATETIME
SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
GETDATE())))
DELETE myTable WHERE myCol < @.yesterday400
(This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
opposed to the time the statement is executed, which could be a few seconds
off.)
You should also consider putting your DELETE statement in a stored
procedure, and calling the stored procedure from the job.
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
> Thanks.
>
|||Thank you all for your replies.
In Enterprise Manager, I do not see Management or SQL Server Agent.
I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
run. Is this what you all meant ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>
|||I think you're looking in the wrong place.
http://www.aspfaq.com/img/2403.gif
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:#Si9kYcGFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Thank you all for your replies.
> In Enterprise Manager, I do not see Management or SQL Server Agent.
> I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
> run. Is this what you all meant ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
at
>
|||Thank you all for your replies.
I found the Management / SQL Server Agent / Jobs.
When I went there, and click "Jobs", I did see the job that I created
earlier using the menu "Tools" - "Job Scheduling".
So, creating a job thru menu "Tools" - "Job Scheduling" and thru Management
/ SQL Server Agent / Jobs are the same, right ?
Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8FBAVcGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job won't
> always run at *precisely* 4:00, and if precision in the amount of data you
> delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
> GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
> opposed to the time the statement is executed, which could be a few
> seconds
> off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> 4
> 4
>
|||> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
Management
> / SQL Server Agent / Jobs are the same, right ?
To be honest, I've never done the former, so I can't guarantee that they
produce identical results.
I think I prefer the latter because you can visually see which server is
creating the job.
A
|||Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OZ1AtTdGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Management
> To be honest, I've never done the former, so I can't guarantee that they
> produce identical results.
> I think I prefer the latter because you can visually see which server is
> creating the job.
> A
>
|||Aaron,
Just for my curriosity - What would be the adavantage of putting the DELETE
statement in a stored proc? I know that a Stored Proc often performs a bit
faster and it's good practice to create stored proc's for recurring scripts,
but is there any specific reason here that I'm missing?
Regards
Steen
Aaron [SQL Server MVP] wrote:[vbcol=seagreen]
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job
> won't always run at *precisely* 4:00, and if precision in the amount
> of data you delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY,
> 0, GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00
> PM, as opposed to the time the statement is executed, which could be
> a few seconds off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
Schedule a SQL statement to run from SQL Server ?
How can I schedule a SQL Statement to run from SQL Server say every day at 4
pm ?
For example, I would like to run the following sql statement every day at 4
pm:
"delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically on SQL server ?
Thanks.Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...[
vbcol=seagreen]
> How can I schedule a SQL Statement to run from SQL Server say every day at
4 pm ?
> For example, I would like to run the following sql statement every day at
4 pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically
> on SQL server ?
> Thanks.
>[/vbcol]|||"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
Create a new job in the SQL Server Agent, where it runs your T-SQL
statement. Then schedule it to run every 4 hours (see the Schedules tab).
Steve|||Have you looked at Management / SQL Server Agent / Jobs?
Also, since other things can be running on the server, and your job won't
always run at *precisely* 4:00, and if precision in the amount of data you
delete is important, you may want to say:
DECLARE @.yesterday400 SMALLDATETIME
SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
GETDATE())))
DELETE myTable WHERE myCol < @.yesterday400
(This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
opposed to the time the statement is executed, which could be a few seconds
off.)
You should also consider putting your DELETE statement in a stored
procedure, and calling the stored procedure from the job.
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
> Thanks.
>|||Thank you all for your replies.
In Enterprise Manager, I do not see Management or SQL Server Agent.
I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
run. Is this what you all meant ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>|||I think you're looking in the wrong place.
http://www.aspfaq.com/img/2403.gif
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:#Si9kYcGFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Thank you all for your replies.
> In Enterprise Manager, I do not see Management or SQL Server Agent.
> I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
> run. Is this what you all meant ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
at[vbcol=seagreen]
>|||Thank you all for your replies.
I found the Management / SQL Server Agent / Jobs.
When I went there, and click "Jobs", I did see the job that I created
earlier using the menu "Tools" - "Job Scheduling".
So, creating a job thru menu "Tools" - "Job Scheduling" and thru Management
/ SQL Server Agent / Jobs are the same, right ?
Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8FBAVcGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job won't
> always run at *precisely* 4:00, and if precision in the amount of data you
> delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
> GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
> opposed to the time the statement is executed, which could be a few
> seconds
> off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> 4
> 4
>|||> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
Management
> / SQL Server Agent / Jobs are the same, right ?
To be honest, I've never done the former, so I can't guarantee that they
produce identical results.
I think I prefer the latter because you can visually see which server is
creating the job.
A|||Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OZ1AtTdGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Management
> To be honest, I've never done the former, so I can't guarantee that they
> produce identical results.
> I think I prefer the latter because you can visually see which server is
> creating the job.
> A
>|||Aaron,
Just for my curriosity - What would be the adavantage of putting the DELETE
statement in a stored proc? I know that a Stored Proc often performs a bit
faster and it's good practice to create stored proc's for recurring scripts,
but is there any specific reason here that I'm missing?
Regards
Steen
Aaron [SQL Server MVP] wrote:[vbcol=seagreen]
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job
> won't always run at *precisely* 4:00, and if precision in the amount
> of data you delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY,
> 0, GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00
> PM, as opposed to the time the statement is executed, which could be
> a few seconds off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
pm ?
For example, I would like to run the following sql statement every day at 4
pm:
"delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically on SQL server ?
Thanks.Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...[
vbcol=seagreen]
> How can I schedule a SQL Statement to run from SQL Server say every day at
4 pm ?
> For example, I would like to run the following sql statement every day at
4 pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically
> on SQL server ?
> Thanks.
>[/vbcol]|||"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
Create a new job in the SQL Server Agent, where it runs your T-SQL
statement. Then schedule it to run every 4 hours (see the Schedules tab).
Steve|||Have you looked at Management / SQL Server Agent / Jobs?
Also, since other things can be running on the server, and your job won't
always run at *precisely* 4:00, and if precision in the amount of data you
delete is important, you may want to say:
DECLARE @.yesterday400 SMALLDATETIME
SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
GETDATE())))
DELETE myTable WHERE myCol < @.yesterday400
(This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
opposed to the time the statement is executed, which could be a few seconds
off.)
You should also consider putting your DELETE statement in a stored
procedure, and calling the stored procedure from the job.
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
> Thanks.
>|||Thank you all for your replies.
In Enterprise Manager, I do not see Management or SQL Server Agent.
I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
run. Is this what you all meant ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>|||I think you're looking in the wrong place.
http://www.aspfaq.com/img/2403.gif
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:#Si9kYcGFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Thank you all for your replies.
> In Enterprise Manager, I do not see Management or SQL Server Agent.
> I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
> run. Is this what you all meant ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
at[vbcol=seagreen]
>|||Thank you all for your replies.
I found the Management / SQL Server Agent / Jobs.
When I went there, and click "Jobs", I did see the job that I created
earlier using the menu "Tools" - "Job Scheduling".
So, creating a job thru menu "Tools" - "Job Scheduling" and thru Management
/ SQL Server Agent / Jobs are the same, right ?
Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8FBAVcGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job won't
> always run at *precisely* 4:00, and if precision in the amount of data you
> delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
> GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
> opposed to the time the statement is executed, which could be a few
> seconds
> off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> 4
> 4
>|||> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
Management
> / SQL Server Agent / Jobs are the same, right ?
To be honest, I've never done the former, so I can't guarantee that they
produce identical results.
I think I prefer the latter because you can visually see which server is
creating the job.
A|||Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OZ1AtTdGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Management
> To be honest, I've never done the former, so I can't guarantee that they
> produce identical results.
> I think I prefer the latter because you can visually see which server is
> creating the job.
> A
>|||Aaron,
Just for my curriosity - What would be the adavantage of putting the DELETE
statement in a stored proc? I know that a Stored Proc often performs a bit
faster and it's good practice to create stored proc's for recurring scripts,
but is there any specific reason here that I'm missing?
Regards
Steen
Aaron [SQL Server MVP] wrote:[vbcol=seagreen]
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job
> won't always run at *precisely* 4:00, and if precision in the amount
> of data you delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY,
> 0, GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00
> PM, as opposed to the time the statement is executed, which could be
> a few seconds off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
Schedule a SQL statement to run from SQL Server ?
How can I schedule a SQL Statement to run from SQL Server say every day at 4
pm ?
For example, I would like to run the following sql statement every day at 4
pm:
"delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically on SQL server ?
Thanks.Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at 4 pm ?
> For example, I would like to run the following sql statement every day at 4 pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule it to run automatically
> on SQL server ?
> Thanks.
>|||"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
Create a new job in the SQL Server Agent, where it runs your T-SQL
statement. Then schedule it to run every 4 hours (see the Schedules tab).
Steve|||Have you looked at Management / SQL Server Agent / Jobs?
Also, since other things can be running on the server, and your job won't
always run at *precisely* 4:00, and if precision in the amount of data you
delete is important, you may want to say:
DECLARE @.yesterday400 SMALLDATETIME
SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
GETDATE())))
DELETE myTable WHERE myCol < @.yesterday400
(This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
opposed to the time the statement is executed, which could be a few seconds
off.)
You should also consider putting your DELETE statement in a stored
procedure, and calling the stored procedure from the job.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
> Thanks.
>|||Thank you all for your replies.
In Enterprise Manager, I do not see Management or SQL Server Agent.
I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
run. Is this what you all meant ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>> How can I schedule a SQL Statement to run from SQL Server say every day
>> at 4 pm ?
>> For example, I would like to run the following sql statement every day at
>> 4 pm:
>> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
>> schedule it to run automatically on SQL server ?
>> Thanks.
>|||I think you're looking in the wrong place.
http://www.aspfaq.com/img/2403.gif
--
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:#Si9kYcGFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Thank you all for your replies.
> In Enterprise Manager, I do not see Management or SQL Server Agent.
> I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
> run. Is this what you all meant ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> > Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "fniles" <fniles@.pfmail.com> wrote in message
> > news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> >> How can I schedule a SQL Statement to run from SQL Server say every day
> >> at 4 pm ?
> >> For example, I would like to run the following sql statement every day
at
> >> 4 pm:
> >> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
> >> schedule it to run automatically on SQL server ?
> >>
> >> Thanks.
> >>
> >
> >
>|||Thank you all for your replies.
I found the Management / SQL Server Agent / Jobs.
When I went there, and click "Jobs", I did see the job that I created
earlier using the menu "Tools" - "Job Scheduling".
So, creating a job thru menu "Tools" - "Job Scheduling" and thru Management
/ SQL Server Agent / Jobs are the same, right ?
Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8FBAVcGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job won't
> always run at *precisely* 4:00, and if precision in the amount of data you
> delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
> GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
> opposed to the time the statement is executed, which could be a few
> seconds
> off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>> How can I schedule a SQL Statement to run from SQL Server say every day
>> at
> 4
>> pm ?
>> For example, I would like to run the following sql statement every day at
> 4
>> pm:
>> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
>> schedule
>> it to run automatically on SQL server ?
>> Thanks.
>>
>|||> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
Management
> / SQL Server Agent / Jobs are the same, right ?
To be honest, I've never done the former, so I can't guarantee that they
produce identical results.
I think I prefer the latter because you can visually see which server is
creating the job.
A|||Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OZ1AtTdGFHA.2356@.TK2MSFTNGP12.phx.gbl...
>> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
> Management
>> / SQL Server Agent / Jobs are the same, right ?
> To be honest, I've never done the former, so I can't guarantee that they
> produce identical results.
> I think I prefer the latter because you can visually see which server is
> creating the job.
> A
>|||Aaron,
Just for my curriosity - What would be the adavantage of putting the DELETE
statement in a stored proc? I know that a Stored Proc often performs a bit
faster and it's good practice to create stored proc's for recurring scripts,
but is there any specific reason here that I'm missing?
Regards
Steen
Aaron [SQL Server MVP] wrote:
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job
> won't always run at *precisely* 4:00, and if precision in the amount
> of data you delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY,
> 0, GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00
> PM, as opposed to the time the statement is executed, which could be
> a few seconds off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>> How can I schedule a SQL Statement to run from SQL Server say every
>> day at 4 pm ?
>> For example, I would like to run the following sql statement every
>> day at 4 pm:
>> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
>> schedule it to run automatically on SQL server ?
>> Thanks.|||> Just for my curriosity - What would be the adavantage of putting the
DELETE
> statement in a stored proc? I know that a Stored Proc often performs a bit
> faster and it's good practice to create stored proc's for recurring
scripts,
> but is there any specific reason here that I'm missing?
- Maintainability, encapsulation
- easy ability to call the DELETE independent of the job
IMHO, all SQL statements should be in stored procedures.
pm ?
For example, I would like to run the following sql statement every day at 4
pm:
"delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically on SQL server ?
Thanks.Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at 4 pm ?
> For example, I would like to run the following sql statement every day at 4 pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule it to run automatically
> on SQL server ?
> Thanks.
>|||"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
Create a new job in the SQL Server Agent, where it runs your T-SQL
statement. Then schedule it to run every 4 hours (see the Schedules tab).
Steve|||Have you looked at Management / SQL Server Agent / Jobs?
Also, since other things can be running on the server, and your job won't
always run at *precisely* 4:00, and if precision in the amount of data you
delete is important, you may want to say:
DECLARE @.yesterday400 SMALLDATETIME
SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
GETDATE())))
DELETE myTable WHERE myCol < @.yesterday400
(This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
opposed to the time the statement is executed, which could be a few seconds
off.)
You should also consider putting your DELETE statement in a stored
procedure, and calling the stored procedure from the job.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
> Thanks.
>|||Thank you all for your replies.
In Enterprise Manager, I do not see Management or SQL Server Agent.
I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
run. Is this what you all meant ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>> How can I schedule a SQL Statement to run from SQL Server say every day
>> at 4 pm ?
>> For example, I would like to run the following sql statement every day at
>> 4 pm:
>> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
>> schedule it to run automatically on SQL server ?
>> Thanks.
>|||I think you're looking in the wrong place.
http://www.aspfaq.com/img/2403.gif
--
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:#Si9kYcGFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Thank you all for your replies.
> In Enterprise Manager, I do not see Management or SQL Server Agent.
> I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
> run. Is this what you all meant ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> > Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "fniles" <fniles@.pfmail.com> wrote in message
> > news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> >> How can I schedule a SQL Statement to run from SQL Server say every day
> >> at 4 pm ?
> >> For example, I would like to run the following sql statement every day
at
> >> 4 pm:
> >> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
> >> schedule it to run automatically on SQL server ?
> >>
> >> Thanks.
> >>
> >
> >
>|||Thank you all for your replies.
I found the Management / SQL Server Agent / Jobs.
When I went there, and click "Jobs", I did see the job that I created
earlier using the menu "Tools" - "Job Scheduling".
So, creating a job thru menu "Tools" - "Job Scheduling" and thru Management
/ SQL Server Agent / Jobs are the same, right ?
Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8FBAVcGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job won't
> always run at *precisely* 4:00, and if precision in the amount of data you
> delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
> GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
> opposed to the time the statement is executed, which could be a few
> seconds
> off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>> How can I schedule a SQL Statement to run from SQL Server say every day
>> at
> 4
>> pm ?
>> For example, I would like to run the following sql statement every day at
> 4
>> pm:
>> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
>> schedule
>> it to run automatically on SQL server ?
>> Thanks.
>>
>|||> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
Management
> / SQL Server Agent / Jobs are the same, right ?
To be honest, I've never done the former, so I can't guarantee that they
produce identical results.
I think I prefer the latter because you can visually see which server is
creating the job.
A|||Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OZ1AtTdGFHA.2356@.TK2MSFTNGP12.phx.gbl...
>> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
> Management
>> / SQL Server Agent / Jobs are the same, right ?
> To be honest, I've never done the former, so I can't guarantee that they
> produce identical results.
> I think I prefer the latter because you can visually see which server is
> creating the job.
> A
>|||Aaron,
Just for my curriosity - What would be the adavantage of putting the DELETE
statement in a stored proc? I know that a Stored Proc often performs a bit
faster and it's good practice to create stored proc's for recurring scripts,
but is there any specific reason here that I'm missing?
Regards
Steen
Aaron [SQL Server MVP] wrote:
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job
> won't always run at *precisely* 4:00, and if precision in the amount
> of data you delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY,
> 0, GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00
> PM, as opposed to the time the statement is executed, which could be
> a few seconds off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>> How can I schedule a SQL Statement to run from SQL Server say every
>> day at 4 pm ?
>> For example, I would like to run the following sql statement every
>> day at 4 pm:
>> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
>> schedule it to run automatically on SQL server ?
>> Thanks.|||> Just for my curriosity - What would be the adavantage of putting the
DELETE
> statement in a stored proc? I know that a Stored Proc often performs a bit
> faster and it's good practice to create stored proc's for recurring
scripts,
> but is there any specific reason here that I'm missing?
- Maintainability, encapsulation
- easy ability to call the DELETE independent of the job
IMHO, all SQL statements should be in stored procedures.
Subscribe to:
Posts (Atom)