question about SQL 2005 Maintenance Plan...

it's about the Maintance plan clean up task...in sql2k, you can just
specify all system databases in maintenance plan and purge files older
than a specified time (i.e. 2 days old), but in 2k5, there is a
seperate maintenance clean up task, you can't specify the database
(i.e. all system dbs) or directory (and include sub folders). does that
mean I have to create 3 tasks just to delete old files for master,
msdb, and model? that would be really dumb, or am I missing something?
Thank you.I have the exact same question.
Anybody can help ?
=== Steve L === wrote:
> it's about the Maintance plan clean up task...in sql2k, you can just
> specify all system databases in maintenance plan and purge files older
> than a specified time (i.e. 2 days old), but in 2k5, there is a
> seperate maintenance clean up task, you can't specify the database
> (i.e. all system dbs) or directory (and include sub folders). does that
> mean I have to create 3 tasks just to delete old files for master,
> msdb, and model? that would be really dumb, or am I missing something?
> Thank you.|||That is the way it is designed. Consider proposing a change at
http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<xiaoyuh.huang@.gmail.com> wrote in message
news:1143835327.889394.267740@.z34g2000cwc.googlegroups.com...
>I have the exact same question.
> Anybody can help ?
> === Steve L === wrote:
>

question about scheduled job in general..

background info: sql2k on nt5
I have a database backup job created by maintenance plan. so the backup file
looks like
MyDatabase_20040430.bak
the job was run nightly so the suffix of the backup file changes daily ( to
20040501, 20040502 and so on)
my question is, i have another scheduled tasks doing some process on
MyDatabase, and I want to back up the database after the process is done, as
the last step fo the scheduled task.
can i call out the scheduled job within a scheduled task? (what would the
syntax be?)
the other concern is, the job created by the manitenance plan actually
backing up several other databases as well. If I just want to back up the
MyDatabase, I need to issue a more specific backup statement, but then how
I'm going to specify the existing dump (backup) file name for MyDatabase,
since the file name changes (as indicated above).
Any suggestions? Thank you.
Steve> can i call out the scheduled job within a scheduled task? (what would the
> syntax be?)
I believe the syntax is
EXEC msdb..sp_start_job @.jobname='<job name>'
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

question about scheduled job in general..

background info: sql2k on nt5
I have a database backup job created by maintenance plan. so the backup file
looks like
MyDatabase_20040430.bak
the job was run nightly so the suffix of the backup file changes daily ( to
20040501, 20040502 and so on)
my question is, i have another scheduled tasks doing some process on
MyDatabase, and I want to back up the database after the process is done, as
the last step fo the scheduled task.
can i call out the scheduled job within a scheduled task? (what would the
syntax be?)
the other concern is, the job created by the manitenance plan actually
backing up several other databases as well. If I just want to back up the
MyDatabase, I need to issue a more specific backup statement, but then how
I'm going to specify the existing dump (backup) file name for MyDatabase,
since the file name changes (as indicated above).
Any suggestions? Thank you.
Steve> can i call out the scheduled job within a scheduled task? (what would the
> syntax be?)
I believe the syntax is
EXEC msdb..sp_start_job @.jobname='<job name>'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/sql

question about scheduled job in general..

background info: sql2k on nt5
I have a database backup job created by maintenance plan. so the backup file
looks like
MyDatabase_20040430.bak
the job was run nightly so the suffix of the backup file changes daily ( to
20040501, 20040502 and so on)
my question is, i have another scheduled tasks doing some process on
MyDatabase, and I want to back up the database after the process is done, as
the last step fo the scheduled task.
can i call out the scheduled job within a scheduled task? (what would the
syntax be?)
the other concern is, the job created by the manitenance plan actually
backing up several other databases as well. If I just want to back up the
MyDatabase, I need to issue a more specific backup statement, but then how
I'm going to specify the existing dump (backup) file name for MyDatabase,
since the file name changes (as indicated above).
Any suggestions? Thank you.
Steve
> can i call out the scheduled job within a scheduled task? (what would the
> syntax be?)
I believe the syntax is
EXEC msdb..sp_start_job @.jobname='<job name>'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Question about scheduled job

You are the administrator of SQL Server 2000 computer. You create a job that
performs several maintenance tasks on the server’s databases. You want the
job to run whenever the server’s processor utilization falls below 5 percent.
You create a new schedule for the job and specify the start whenever the CPU(
s) become idle option. After several days, you notice that the job has never
executed although the server’s processor utilization has fallen below 5
percent several times.
What should you do?
A. Modify SQL Server Agent properties and specify a smaller idle time.
B. Modify SQL server agent properties and specify a larger idle time.
C. Write a stored procedure that executes the job whenever the @.@. IDLE
system variable is less than 5.
D. Write a stored procedure that executes the job whenever the @.@. IDLE
system variable is greater
than 1.
The processor utilization option works in conjunction with the
settings for the idle time or number of seconds the cpu remains below
the utilization percent specified. If you fall below the utilization
and the job never fires, it's generally due to the number of seconds
you have specified for the idle time never being hit so you'd want to
try lowering the number of seconds that the processor utilization
needs to be at the specified percentage. Once you hit the specified
number of seconds, the CPU is considered idle.
-Sue
On Wed, 8 Sep 2004 07:33:03 -0700, "Eric"
<Eric@.discussions.microsoft.com> wrote:

>You are the administrator of SQL Server 2000 computer. You create a job that
>performs several maintenance tasks on the servers databases. You want the
>job to run whenever the servers processor utilization falls below 5 percent.
>You create a new schedule for the job and specify the start whenever the CPU(
>s) become idle option. After several days, you notice that the job has never
>executed although the servers processor utilization has fallen below 5
>percent several times.
>What should you do?
>A. Modify SQL Server Agent properties and specify a smaller idle time.
>B. Modify SQL server agent properties and specify a larger idle time.
>C. Write a stored procedure that executes the job whenever the @.@. IDLE
>system variable is less than 5.
>D. Write a stored procedure that executes the job whenever the @.@. IDLE
>system variable is greater
>than 1.

Question about how SQL Server decides when to auto-update statistics

I am reading the Microsoft article 'Statistical maintenance
functionality (autostats) in SQL Server'
(http://support.microsoft.com/kb/q195565/) and I'm wondering about this
part:
"NOTE: In addition to cardinality, the selectivity of the predicate
also affects AutoStats generation. This means that statistics may not
be updated afer every 500 modifications if cardinality were < 500 or
for every 20% of changes if cardinality were > 500. A scale up factor
(value ranges from 1 to 4, 1 and 4 inclusive) is generated depending on
the selectivity and a product of this factor and the number of changes
as obtained from the algorithm would be the actual number of
modifications required for AutoStats generation."
Does anyone know if a high selectivity means the factor is 1 or 4? Does
anyone know more about how that value is determined?
I'm also wondering about this part of the same article:
"Consider a second table, t2, that has a cardinality of 1,000. For
tables with greater than 500 rows, SQL Server will UPDATE STATISTICS
when (500 + 20 percent) changes have been made. Doing the math, 20
percent of 1,000 is 200, so you can expect to see AutoStat start after
approximately 700 modifications have been made to the table."
I don't understand why they say that AutoStats will execute after 700
modifications. 20 percent of 1000 rows is 200 rows, so wouldn't we
expect to see AutoStats after 200 rows, not 700 rows?
Thanks> Does anyone know if a high selectivity means the factor is 1 or 4? Does
> anyone know more about how that value is determined?
Does it really matter which is which? To the best of my knowledge you can't
see it anyway unless you decode the statblob. The point is that if the
selectivity is low you can get by with more rows changing before you have to
update the stats.
> I don't understand why they say that AutoStats will execute after 700
> modifications. 20 percent of 1000 rows is 200 rows, so wouldn't we
> expect to see AutoStats after 200 rows, not 700 rows?
It says 500 + 20%. If 20% is 200 rows then if you add that to 500 you get
700. Make sense?
If you are using sql 2005 these figures have chaged somewhat and you want to
ensure you are using the correct whitepaper.
--
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1156534531.968254.118570@.75g2000cwc.googlegroups.com...
>I am reading the Microsoft article 'Statistical maintenance
> functionality (autostats) in SQL Server'
> (http://support.microsoft.com/kb/q195565/) and I'm wondering about this
> part:
> "NOTE: In addition to cardinality, the selectivity of the predicate
> also affects AutoStats generation. This means that statistics may not
> be updated afer every 500 modifications if cardinality were < 500 or
> for every 20% of changes if cardinality were > 500. A scale up factor
> (value ranges from 1 to 4, 1 and 4 inclusive) is generated depending on
> the selectivity and a product of this factor and the number of changes
> as obtained from the algorithm would be the actual number of
> modifications required for AutoStats generation."
> Does anyone know if a high selectivity means the factor is 1 or 4? Does
> anyone know more about how that value is determined?
> I'm also wondering about this part of the same article:
> "Consider a second table, t2, that has a cardinality of 1,000. For
> tables with greater than 500 rows, SQL Server will UPDATE STATISTICS
> when (500 + 20 percent) changes have been made. Doing the math, 20
> percent of 1,000 is 200, so you can expect to see AutoStat start after
> approximately 700 modifications have been made to the table."
> I don't understand why they say that AutoStats will execute after 700
> modifications. 20 percent of 1000 rows is 200 rows, so wouldn't we
> expect to see AutoStats after 200 rows, not 700 rows?
> Thanks
>

question about best way to store an up or down value

I'm creating a table for maintenance records.

In each record many of the values are simply checkboxes.

In the database for these attributes, is a good way to store the state of these checkboxes as simple as 0 for false, 1 for true?

-DavidWithout getting into design issues, the best way would be to use a BIT datatype, with 0 used to indicate FALSE or OFF, and 1 to indicate TRUE or ON.

Question

I got a maintenance plan that does a checkdb everyday at
1:00 pm (for example). If i add a job to this maintenance
plan (going to job, adding a step to the job that
exectutes the maintenance plan..) it works fien but when
i open my maintenance plan to alter it (ex: add a
database) i get an error and all my setting are erased.
If i reconfigure it, it creates ANOTHER job using the
previous name and adding a 1 to it.. anyone know why this
is happeneing and how can i work arround this?!?!?!?
Don't modify the jobs created by maint wiz. Main wiz is a simple to use tool for the most basic installations.
If you need customizations beyond maint wiz, create your own jobs (or use CmdExec jobsteps to execute
sqlmaint.exe).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"?" <anonymous@.discussions.microsoft.com> wrote in message news:1c53c01c452bd$3c2bda60$a301280a@.phx.gbl...
> I got a maintenance plan that does a checkdb everyday at
> 1:00 pm (for example). If i add a job to this maintenance
> plan (going to job, adding a step to the job that
> exectutes the maintenance plan..) it works fien but when
> i open my maintenance plan to alter it (ex: add a
> database) i get an error and all my setting are erased.
> If i reconfigure it, it creates ANOTHER job using the
> previous name and adding a 1 to it.. anyone know why this
> is happeneing and how can i work arround this?!?!?!?

Question

I got a maintenance plan that does a checkdb everyday at
1:00 pm (for example). If i add a job to this maintenance
plan (going to job, adding a step to the job that
exectutes the maintenance plan..) it works fien but when
i open my maintenance plan to alter it (ex: add a
database) i get an error and all my setting are erased.
If i reconfigure it, it creates ANOTHER job using the
previous name and adding a 1 to it.. anyone know why this
is happeneing and how can i work arround this?!?!?!?Don't modify the jobs created by maint wiz. Main wiz is a simple to use tool for the most basic installations.
If you need customizations beyond maint wiz, create your own jobs (or use CmdExec jobsteps to execute
sqlmaint.exe).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"'" <anonymous@.discussions.microsoft.com> wrote in message news:1c53c01c452bd$3c2bda60$a301280a@.phx.gbl...
> I got a maintenance plan that does a checkdb everyday at
> 1:00 pm (for example). If i add a job to this maintenance
> plan (going to job, adding a step to the job that
> exectutes the maintenance plan..) it works fien but when
> i open my maintenance plan to alter it (ex: add a
> database) i get an error and all my setting are erased.
> If i reconfigure it, it creates ANOTHER job using the
> previous name and adding a 1 to it.. anyone know why this
> is happeneing and how can i work arround this?!?!?!?

Question

I got a maintenance plan that does a checkdb everyday at
1:00 pm (for example). If i add a job to this maintenance
plan (going to job, adding a step to the job that
exectutes the maintenance plan..) it works fien but when
i open my maintenance plan to alter it (ex: add a
database) i get an error and all my setting are erased.
If i reconfigure it, it creates ANOTHER job using the
previous name and adding a 1 to it.. anyone know why this
is happeneing and how can i work arround this?!?!?!?Don't modify the jobs created by maint wiz. Main wiz is a simple to use tool
for the most basic installations.
If you need customizations beyond maint wiz, create your own jobs (or use Cm
dExec jobsteps to execute
sqlmaint.exe).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"'" <anonymous@.discussions.microsoft.com> wrote in message news:1c53c01c452bd$3c2bda60$a301
280a@.phx.gbl...
> I got a maintenance plan that does a checkdb everyday at
> 1:00 pm (for example). If i add a job to this maintenance
> plan (going to job, adding a step to the job that
> exectutes the maintenance plan..) it works fien but when
> i open my maintenance plan to alter it (ex: add a
> database) i get an error and all my setting are erased.
> If i reconfigure it, it creates ANOTHER job using the
> previous name and adding a 1 to it.. anyone know why this
> is happeneing and how can i work arround this?!?!?!?