MS SQL Server - Need advice on db maintenance plan

Author

Need advice on db maintenance plan

jcvoon

2006-03-24, 3:23 am

Hi:

I need advice on creating the maintenance plan.

12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)

Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?

I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.

If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?

If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?

Please help.
Thanks
JCVoon

Tibor Karaszi

2006-03-24, 3:23 am

What version of SQL Server?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"jcvoon" <jcvoon@maximas.com.my> wrote in message
news:1143183567.147117.216070@e56g2000cwe.googlegroups.com...
> Hi:
>
> I need advice on creating the maintenance plan.
>
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
>
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
>
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
>
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
>
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
>
> Please help.
> Thanks
> JCVoon
>

jcvoon

2006-03-24, 3:23 am

Tibor Karaszi:

Oops...sorry, forgot to mention. It is SQL Server 2000 ent

Regards
JCVoon

Tibor Karaszi

2006-03-24, 7:26 am

Prior to 2005, a database backup will block a log backup. So you can have your log backups scheduled
if you wish, the log backup job will just sit a wait until the database has been performed. Also,
Agent will not start a job if it is already running.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"jcvoon" <jcvoon@maximas.com.my> wrote in message
news:1143188648.197598.226630@t31g2000cwb.googlegroups.com...
> Tibor Karaszi:
>
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
>
> Regards
> JCVoon
>

jcvoon

2006-03-25, 3:23 am

Tibor Karaszi

Thanks.

>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?

Regards
JCVoon

Tibor Karaszi

2006-03-26, 7:25 am

> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?

No, it will wait until the database backup has finished.

> will the log backup block the full database
> backup ?

Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"jcvoon" <jcvoon@maximas.com.my> wrote in message
news:1143253350.681426.250150@t31g2000cwb.googlegroups.com...
> Tibor Karaszi
>
> Thanks.
>
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
>
> Regards
> JCVoon
>

jcvoon

2006-03-26, 8:23 pm

Thanks

JCVoon

Sponsored Links