Database Maintenance Plan for SQL Server 2005 Problem

I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.

However, I find that the transaction log backup job doesn't run as expected.
I have checked and found that SQL Server Agent is already started.  Is there
any way to check where the problem is ?

Besides, when I am creating the database maintenance plan, I find that I am
not able to create both DB Backup (early in the morning) and Transaction log
backup (hourly) by using the same DB Maintenance Plan.  Does it mean that I
have to create 2 different plans with the wizard ?

Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it seems that they are run at the same time.  Is there
any way to change the time they are running ?

Thanks
Peter

Hi Peter

If you edit the plan in Management Studio, there is a logging button to send
information to a file, which also has an extended logging check box. If thes
e
are set up you may have information in this file.

I would expect you would want transaction log backups and full backups to be
scheduled separately and therefore the easiest solution would be to have two
plans. I don't think you can add conditional execution in the maintenance
wizard, but you could use BIDS to create a SSIS package that does the
maintenance tasks for you and then add a script task to make what backup you
do conditional.

John

"Peter" wrote:

> I am new to SQL Server 2005 and just set up a number of DB Maintenance Pla
n.
>
> However, I find that the transaction log backup job doesn't run as expecte
d.
> I have checked and found that SQL Server Agent is already started.  Is the
re
> any way to check where the problem is ?
>
> Besides, when I am creating the database maintenance plan, I find that I a
m
> not able to create both DB Backup (early in the morning) and Transaction l
og
> backup (hourly) by using the same DB Maintenance Plan.  Does it mean that 
I
> have to create 2 different plans with the wizard ?
>
> Moreover, for weekly maintenance, database integrity check, rebuild index,
> database backup ... it seems that they are run at the same time.  Is there
> any way to change the time they are running ?
>
> Thanks
> Peter
>
>
>

> However, I find that the transaction log backup job doesn't run as expected. I have check
ed and
> found that SQL Server Agent is already started.  Is there any way to check where t
he problem is ?

Most commonly this is because you have included some databases that are in s
imple recovery mode.
When a maint plan is returned an error inside a task, it will terminate that
 task.


> Besides, when I am creating the database maintenance plan, I find that I a
m not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly) 
by using the same DB
> Maintenance Plan.  Does it mean that I have to create 2 different plans with the w
izard ?

Yes. But in sp2, you will be able to have separate schedules for each task. 
I don't see this as a
big thing since you will still have to create at least two tasks:

One for the databases in simple recovery (master, for example), where you do
n't do log backup.
And another for the database where you do want to do log backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/


"Peter" <Peter@discussions.microsoft.com> wrote in message
news:uUYrZRQHHHA.2236@TK2MSFTNGP02.phx.gbl...
>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan
.
>
> However, I find that the transaction log backup job doesn't run as expecte
d. I have checked and
> found that SQL Server Agent is already started.  Is there any way to check
 where the problem is ?
>
> Besides, when I am creating the database maintenance plan, I find that I a
m not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly) 
by using the same DB
> Maintenance Plan.  Does it mean that I have to create 2 different plans wi
th the wizard ?
>
> Moreover, for weekly maintenance, database integrity check, rebuild index,
 database backup ... it
> seems that they are run at the same time.  Is there any way to change the 
time they are running ?
>
> Thanks
> Peter
>

Dear John and Tibor,

Many thanks for your advice.

To my surprise, I find that the transaction log backup doesn't performed
yesterday (It supposed to start immediately) but it works properly today.
The daily production database backup also doesn't performed last night at
8:00pm.  On the other hand, the daily system database backup is performed
successfully at 2:00am this morning.  It is pretty weird.

I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change it to 3:30pm later.  Would it be a reason for the
problem ?

Besides, from the maintenance log file, it only mentions that the
maintenance plan is executed but it hasn't mentioned any error encountered
(It seems that it hasn't executed and there is of course no error).  If
there is problem when the maintenance plan is executed, will the error
message be shown on the log files OR should be found in SQL Error Log ?

Thanks
Peter



"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:etjWvuRHHHA.4904@TK2MSFTNGP04.phx.gbl... 
>
> Most commonly this is because you have included some databases that are in
> simple recovery mode. When a maint plan is returned an error inside a
> task, it will terminate that task.
>
> 
>
> Yes. But in sp2, you will be able to have separate schedules for each
> task. I don't see this as a big thing since you will still have to create
> at least two tasks:
>
> One for the databases in simple recovery (master, for example), where you
> don't do log backup.
> And another for the database where you do want to do log backups...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Peter" <Peter@discussions.microsoft.com> wrote in message
> news:uUYrZRQHHHA.2236@TK2MSFTNGP02.phx.gbl... 
>
>

> I believe that it may be because when I set up the server, the time is set at 9:00pm and 
I  change
> it to 3:30pm later.  Would it be a reason for the problem ?

I vaguelly recall others seeing similar issues, so I'd say that this is a li
kely cause.


> Besides, from the maintenance log file, it only mentions that the maintena
nce plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't execute
d and there is of course
> no error).

You mean the Maint Plan report file? In general, when I don't see any errors
, I assume that the
statements were executed with success.


> If there is problem when the maintenance plan is executed, will the error 
message be shown on the
> log files OR should be found in SQL Error Log ?

I haven't played enough with 2005 maint plans to say for sure what is logged
 to the various log
destinations (report file, maint plan history tables etc). Whether an error 
is logged to the event
log depends on that error (see the sys.messages catalog view).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/


"Peter" <Peter@discussions.microsoft.com> wrote in message
news:%23CIMj3oHHHA.816@TK2MSFTNGP06.phx.gbl...
> Dear John and Tibor,
>
> Many thanks for your advice.
>
> To my surprise, I find that the transaction log backup doesn't performed y
esterday (It supposed to
> start immediately) but it works properly today. The daily production datab
ase backup also doesn't
> performed last night at 8:00pm.  On the other hand, the daily system datab
ase backup is performed
> successfully at 2:00am this morning.  It is pretty weird.
>
> I believe that it may be because when I set up the server, the time is set
 at 9:00pm and I change
> it to 3:30pm later.  Would it be a reason for the problem ?
>
> Besides, from the maintenance log file, it only mentions that the maintena
nce plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't execute
d and there is of course
> no error).  If there is problem when the maintenance plan is executed, wil
l the error message be
> shown on the log files OR should be found in SQL Error Log ?
>
> Thanks
> Peter
>
>
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote i
n message
> news:etjWvuRHHHA.4904@TK2MSFTNGP04.phx.gbl... 
>
>