Maintenance plan log offloads (SQL2000)

Maintenance plan log offloads (SQL2000)

Post by Ken Procto » Sun, 22 Apr 2001 01:24:39

SQL7 and 2000 appear to treat certain failures differently. We typically
define one plan to backup/offload the logs of all databases. If SQL 7
attempts to offload a log whose DB has 'trunc log on checkpoint' set it
ignores the error and carries on, SQL2000 returns an error and the log
offload job is marked as failed. I read KB article Q242500 as saying that
the SQL7 behaviour was due to a bug in SQLMAINT for SQL7, so I guess this
bug has been fixed in SQL2000. Does anyone know this for sure?

We'd assumed the SQL7 behaviour was a feature and this was useful to us as
we sometimes have a mix of 'recovery modes' across our DBs. Looks like for
SQL2000 we may have to code a script to determine which DBs have logs that
can be offloaded (i.e. aren't 'trunc logged') & then attempt to offload only


1. Transaction Log Backup Through Maintenance Plan in SQL2000

Dear All,

In SQL 7.0 days, I used to depends on maintenance plan to create backup task
for all database in a server. Just select "All database", and specify the
Full backup & Transaction Log backup option, then the plan can kept unchange
even new database is created afterwards.

However, it no longer works on SQL 2000, If I specify "All database" and
also select Transaction Log backup, the Transaction log backup task will
just fail (the backup still done but deletion of files beyond retention
period will be given up), as master db / model db 's recovery option just
not allow transaction log backup.

In MS Knowledge Base - Q242500, it even said SQLMAINT.EXE (in SQL 7.0) not
report error for BACKUP LOG for DB with "truncate log on checkpoint" set is
a bug, and hence Q285288 (SQL2000) , it just inform what I encountered - the
combination of "All database", and "Transaction Log backup" is now not

What in my mind is that the MS guy carrying out SQL server's maintenance
just don't understand the creator's original intention, and thus change the
behaviour in subsequent version. That in my opinion, the change itself is a
bug instead.

Nevertheless, is there anyone who has a better alternative than changing the
maintenance plan every time there is a new database added ?


2. Stored procedure on a linked Server

3. Maintenance Plan jobs failing with memory allocation error SQL2000

4. Unique Values and NULL output

5. repost: Maintenance Plan jobs failing with memory allocation error SQL2000

6. Prefix all column names in a Select

7. Maintenance Plan seems to shrink Transaction Log...

8. Update Method with DAO 3.5

9. Backup of log files failing in Maintenance Plan

10. Database maintenance plan backing up logs

11. maintenance plans / backup db and log will not run

12. SQL 7.0 SP4 Backup log - Maintenance plan

13. Maintenance Plan Failing to backup Transaction Logs