SQLAgentMail: scheduled

Showing posts with label scheduled. Show all posts

Wednesday, March 21, 2012

Question on SQL job step invoking .cmd

I have a simple SQL job that is scheduled to invoke a .cmd to perform a
file copy task.
How can I get SQL job to report to correct status when the copy task
fails, such as due to connectivity issue, or file not found, and etc?
thanks.One option is to capture the return code and do a raiserror
if the return code <> 0. Along the lines of:
DECLARE @.ret int
EXEC @.ret = master..xp_cmdshell 'copy ...etc' , NO_OUTPUT
IF @.ret <> 0 RAISERROR('File copy error',16, 1)
-Sue
On 28 Jul 2006 08:23:49 -0700, "Rose" <rose.say@.gmail.com>
wrote:

>I have a simple SQL job that is scheduled to invoke a .cmd to perform a
>file copy task.
>How can I get SQL job to report to correct status when the copy task
>fails, such as due to connectivity issue, or file not found, and etc?
>thanks.

Question on SQL job step invoking .cmd

I have a simple SQL job that is scheduled to invoke a .cmd to perform a
file copy task.
How can I get SQL job to report to correct status when the copy task
fails, such as due to connectivity issue, or file not found, and etc?
thanks.One option is to capture the return code and do a raiserror
if the return code <> 0. Along the lines of:
DECLARE @.ret int
EXEC @.ret = master..xp_cmdshell 'copy ...etc' , NO_OUTPUT
IF @.ret <> 0 RAISERROR('File copy error',16, 1)
-Sue
On 28 Jul 2006 08:23:49 -0700, "Rose" <rose.say@.gmail.com>
wrote:
>I have a simple SQL job that is scheduled to invoke a .cmd to perform a
>file copy task.
>How can I get SQL job to report to correct status when the copy task
>fails, such as due to connectivity issue, or file not found, and etc?
>thanks.sql

Monday, March 12, 2012

Question on optimization done by Enterprize manager.

Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
wondering if anyone knows exactly what it does? How does it shrink the
database and how does this help performance?
thanks.
This is what it lists as being done.
Reorganize data and index pages, changing the free space to 10 percent of
the original space.
Shrink database when it grows beyound 50 MB. Leave 10 percent of data space
as free space.
--
Paul G
Software engineer.Paul,
What you're refering to is a Database Maintanence Plan. The plan will
create several jobs (depending on the options selected) for the database. A
reorg rebuilds the indexes applying a new fillfactor to remove
fragmentation. A shrink database reduces the size of the database. While I
would recommend the former, I would not recommend performing an automatic
shrink of the database. Also, I would recommend that you periodically
backup the transaction log as part of the plan to keep the size of the log
managable.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F8635F69-6A9C-4985-B22C-E53E9035C54D@.microsoft.com...
> Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
> wondering if anyone knows exactly what it does? How does it shrink the
> database and how does this help performance?
> thanks.
> This is what it lists as being done.
> Reorganize data and index pages, changing the free space to 10 percent of
> the original space.
> Shrink database when it grows beyound 50 MB. Leave 10 percent of data
> space
> as free space.
> --
> Paul G
> Software engineer.|||ok the database is not that large so think I will remove this option. Thanks.
--
Paul G
Software engineer.
"Jerry Spivey" wrote:
> Paul,
> What you're refering to is a Database Maintanence Plan. The plan will
> create several jobs (depending on the options selected) for the database. A
> reorg rebuilds the indexes applying a new fillfactor to remove
> fragmentation. A shrink database reduces the size of the database. While I
> would recommend the former, I would not recommend performing an automatic
> shrink of the database. Also, I would recommend that you periodically
> backup the transaction log as part of the plan to keep the size of the log
> managable.
> HTH
> Jerry
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:F8635F69-6A9C-4985-B22C-E53E9035C54D@.microsoft.com...
> > Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
> > wondering if anyone knows exactly what it does? How does it shrink the
> > database and how does this help performance?
> > thanks.
> > This is what it lists as being done.
> > Reorganize data and index pages, changing the free space to 10 percent of
> > the original space.
> > Shrink database when it grows beyound 50 MB. Leave 10 percent of data
> > space
> > as free space.
> >
> > --
> > Paul G
> > Software engineer.
>
>|||Hi Paul,
Jerry is right on shrinking of log file.
But did u think that reindexing will make any effect on ur performance.
Reindexing takes lot of resources and u should not go for indexing
daily.
make it once a week or one time in two weeks.
once ur database will grow it will take lot of buffer size which will
effect ur database performance.
hope u understand
from
Doller|||Reorganize (defragment indexes):
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F8635F69-6A9C-4985-B22C-E53E9035C54D@.microsoft.com...
> Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
> wondering if anyone knows exactly what it does? How does it shrink the
> database and how does this help performance?
> thanks.
> This is what it lists as being done.
> Reorganize data and index pages, changing the free space to 10 percent of
> the original space.
> Shrink database when it grows beyound 50 MB. Leave 10 percent of data space
> as free space.
> --
> Paul G
> Software engineer.|||This is a multi-part message in MIME format.
--020500030202020509000109
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
The index rebuild frequency will depend on a number of things but mostly
how often data is changed.
If your tables, on average, have huge amounts of change each day (eg. >
25% of the indexed data changes on a daily basis) then you will want to
reorganise your indexes more often (perhaps daily in the example
given). If, on the other hand, your tables, on average, are mostly
static (eg. < 1% of the indexed data changes on a daily basis) then you
won't need to reorganise as often (in that example once a month would
probably be fine). This should ensure your indexes have a satisfactory
fragmentation (i.e. low) and a reasonably even distribution throughout
the b-tree.
If you have specific tables that have quite a different change rate from
the other tables you may want to implement the reindexing through T-SQL
code you have more control over (rather than using the DB Maint
Wizard). For example, if you have a dozen large tables with lots of
indexes that are mostly static and you had a couple large tables with
lots of indexes that had a huge amount of daily change, then you'd want
to rebuild the indexes on those couple tables quite often (perhaps
daily) and not so often for the rest (perhaps monthly). You'd do that
by scheduling a couple SQLAgent jobs to run the DBCC DBREINDEX (or DBCC
DEFRAGINDEX) statement on specific tables - one job for those very
dynamic tables that runs daily and the other job for the relatively
static tables that runs monthly.
You can't lay a blanket rule down for every database, but you do start
with a general plan (like through the DB Maint Wizard) and
monitor/modify as appropriate for each database.
Modifying indexes does result in significant I/O & CPU load on the
server though, as Doller mentioned. So you should do the reindexing out
of hours. Also, moving all those index pages around results in a large
volume of data logged in the transaction log. So you'll want to keep an
eye on your transaction log size and make sure it doesn't get out of
hand. (Sometimes, in certain circumstances, it's even beneficial to
change the DB recovery model to simple, do your reindexing, change the
recovery model back to full or bulk-logged, and do a full DB backup.
Sometimes a full DB backup is smaller than the transaction log after a
substantial reindexing session.)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:
>Hi Paul,
>Jerry is right on shrinking of log file.
>But did u think that reindexing will make any effect on ur performance.
>Reindexing takes lot of resources and u should not go for indexing
>daily.
>make it once a week or one time in two weeks.
>once ur database will grow it will take lot of buffer size which will
>effect ur database performance.
>hope u understand
>from
>Doller
>
>
--020500030202020509000109
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">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>The index rebuild frequency will depend on a number of things but
mostly how often data is changed. 
 
If your tables, on average, have huge amounts of change each day (eg.
> 25% of the indexed data changes on a daily basis) then you will
want to reorganise your indexes more often (perhaps daily in the
example given). If, on the other hand, your tables, on average, are
mostly static (eg. < 1% of the indexed data changes on a daily
basis) then you won't need to reorganise as often (in that example once
a month would probably be fine). This should ensure your indexes have
a satisfactory fragmentation (i.e. low) and a reasonably even
distribution throughout the b-tree. 
 
If you have specific tables that have quite a different change rate
from the other tables you may want to implement the reindexing through
T-SQL code you have more control over (rather than using the DB Maint
Wizard). For example, if you have a dozen large tables with lots of
indexes that are mostly static and you had a couple large tables with
lots of indexes that had a huge amount of daily change, then you'd want
to rebuild the indexes on those couple tables quite often (perhaps
daily) and not so often for the rest (perhaps monthly). You'd do that
by scheduling a couple SQLAgent jobs to run the DBCC DBREINDEX (or DBCC
DEFRAGINDEX) statement on specific tables - one job for those very
dynamic tables that runs daily and the other job for the relatively
static tables that runs monthly. 
 
You can't lay a blanket rule down for every database, but you do start
with a general plan (like through the DB Maint Wizard) and
monitor/modify as appropriate for each database. 
 
Modifying indexes does result in significant I/O & CPU load on the
server though, as Doller mentioned. So you should do the reindexing
out of hours. Also, moving all those index pages around results in a
large volume of data logged in the transaction log. So you'll want to
keep an eye on your transaction log size and make sure it doesn't get
out of hand. (Sometimes, in certain circumstances, it's even
beneficial to change the DB recovery model to simple, do your
reindexing, change the recovery model back to full or bulk-logged, and
do a full DB backup. Sometimes a full DB backup is smaller than the
transaction log after a substantial reindexing session.) 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
doller wrote:
<blockquote
cite="mid1127969601.827232.288720@.g14g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">Hi Paul,
Jerry is right on shrinking of log file.
But did u think that reindexing will make any effect on ur performance.
Reindexing takes lot of resources and u should not go for indexing
daily.
make it once a week or one time in two weeks.
once ur database will grow it will take lot of buffer size which will
effect ur database performance.
hope u understand
from
Doller
</pre>
</blockquote>
</body>
</html>
--020500030202020509000109--|||Hi thanks for the additional information. I do not expect large amounts of
data daily perhaps 30 records in several tables so have set indexing to 1 per
week.
--
Paul G
Software engineer.
"Mike Hodgson" wrote:
> The index rebuild frequency will depend on a number of things but mostly
> how often data is changed.
> If your tables, on average, have huge amounts of change each day (eg. >
> 25% of the indexed data changes on a daily basis) then you will want to
> reorganise your indexes more often (perhaps daily in the example
> given). If, on the other hand, your tables, on average, are mostly
> static (eg. < 1% of the indexed data changes on a daily basis) then you
> won't need to reorganise as often (in that example once a month would
> probably be fine). This should ensure your indexes have a satisfactory
> fragmentation (i.e. low) and a reasonably even distribution throughout
> the b-tree.
> If you have specific tables that have quite a different change rate from
> the other tables you may want to implement the reindexing through T-SQL
> code you have more control over (rather than using the DB Maint
> Wizard). For example, if you have a dozen large tables with lots of
> indexes that are mostly static and you had a couple large tables with
> lots of indexes that had a huge amount of daily change, then you'd want
> to rebuild the indexes on those couple tables quite often (perhaps
> daily) and not so often for the rest (perhaps monthly). You'd do that
> by scheduling a couple SQLAgent jobs to run the DBCC DBREINDEX (or DBCC
> DEFRAGINDEX) statement on specific tables - one job for those very
> dynamic tables that runs daily and the other job for the relatively
> static tables that runs monthly.
> You can't lay a blanket rule down for every database, but you do start
> with a general plan (like through the DB Maint Wizard) and
> monitor/modify as appropriate for each database.
> Modifying indexes does result in significant I/O & CPU load on the
> server though, as Doller mentioned. So you should do the reindexing out
> of hours. Also, moving all those index pages around results in a large
> volume of data logged in the transaction log. So you'll want to keep an
> eye on your transaction log size and make sure it doesn't get out of
> hand. (Sometimes, in certain circumstances, it's even beneficial to
> change the DB recovery model to simple, do your reindexing, change the
> recovery model back to full or bulk-logged, and do a full DB backup.
> Sometimes a full DB backup is smaller than the transaction log after a
> substantial reindexing session.)
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> doller wrote:
> >Hi Paul,
> >
> >Jerry is right on shrinking of log file.
> >But did u think that reindexing will make any effect on ur performance.
> >Reindexing takes lot of resources and u should not go for indexing
> >daily.
> >
> >make it once a week or one time in two weeks.
> >once ur database will grow it will take lot of buffer size which will
> >effect ur database performance.
> >
> >hope u understand
> >
> >from
> >Doller
> >
> >
> >
>

Wednesday, March 21, 2012

Question on SQL job step invoking .cmd

Question on SQL job step invoking .cmd

Monday, March 12, 2012

Question on optimization done by Enterprize manager.

Question on optimization done by Enterprize manager.

Question on optimization done by Enterprize manager.

SQLAgentMail

Blog Archive

About Me