Database Maintenance Plan Problems

We are also experiencing this problem on all of our SQL Server 2000
servers (sp3a), so I'm glad to have found your discussion. I'll reply
back as soon as I can figure out if KB 303292 resolves our problem.
Thanks,
Tom
On Sat, 15 Nov 2003 01:36:00 GMT, jyothip@.online.microsoft.com (Jyothi
Pai [MSFT]) wrote:

>Hi David,
>You may also be running into a known issue as explained in this article.
>Please check to see if this is the case. If this is indeed the problem, you
>may want to create separate maintenance plans for the system databases such
>as master and msdb and another plan for the user databases (if all of the
>user databases are using FULL recovery model). Otherwise, create separate
>maintenance plans for the user databases based on their recovery model.
>If this is not the case, please post the report file to assist you further.
>BUG: Expired Transaction Log Backups May Not Be Deleted by Maintenance Plan
>http://support.microsoft.com/defaul...KB;EN-US;303292
>A maintenance plan job to backup transaction logs may fail. The
>"Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'"
>job history may show the following error message:
>The job failed. The Job was invoked by Schedule 3 (Schedule 1). The last
>step to run was step 1 (Step 1).
>The job step details history contains this error message:
>sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
>If the Write history to the table msdb.dbo.sysdbmaintplan_history reporting
>option is selected the history of the plan contains an entry similar to:
>Backup can not be performed on this database. This sub task is ignored
>If the Write report to a text file in directory reporting option is
>selected the report file contains this entry:
>Backup can not be performed on database 'pubs'. This sub task is ignored.
>In addition, expired transaction log backups may not be deleted. Although
>the maintenance plan job fails, all backups are performed correctly.
>This behavior occurs under the following circumstances:
>- Multiple databases are backed up in a single Maintenance Plan.
>- The Maintenance Plan is configured to perform transaction log backups.
>- The recovery model of at least one of the databases in the Maintenance
>Plan is set to SIMPLE.
>CAUSE
>Selecting the "Backup the transaction log as part of the maintenance plan"
>option directs SQLMaint.exe to execute a BACKUP LOG command on each
>database in the plan.
>Issuing a BACKUP LOG command against a database in SIMPLE recovery mode
>causes this error message:
>Server: Msg 4208, Level 16, State 1, Line 1
>The statement BACKUP LOG is not allowed while the recovery model is SIMPLE.
>Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
>Server: Msg 3013, Level 16, State 1, Line 1
>BACKUP LOG is terminating abnormally.
>SQLMaint.exe interprets this as a non-fatal error and continues to back up
>the transaction logs of other databases in the Maintenance Plan. However,
>after the log backups are complete SQLMaint exits without removing expired
>backups.
>WORKAROUND
>To work around this problem either:
>- Create two Maintenance Plans. One Maintenance Plan contains only
>databases that use the FULL or BULK LOGGED recovery models. The second
>Maintenance Plan contains databases in SIMPLE recovery mode. Do not perform
>transaction log backups in the second Maintenance Plan.
>- Change the recovery model of the databases from SIMPLE to either FULL or
>BULK LOGGED and perform a full database backup on the databases.
>~~~~~~~~~~~~~~~~~~
>Hope this helps.
>Regards,
>Jyothi Pai
>Microsoft Online Support Engineer
>Get Secure! www.microsoft.com/security
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>--
>| From: "David DeJarnett" <no@.email.com>
>| References: <OaPuhO6pDHA.2848@.TK2MSFTNGP10.phx.gbl>
><KvdbvPAqDHA.444@.cpmsftngxa06.phx.gbl>
><eqFOJJGqDHA.1084@.tk2msftngp13.phx.gbl>
><gA17gTHqDHA.576@.cpmsftngxa06.phx.gbl>
>| Subject: Re: Database Maintenance Plan Problems
>| Date: Tue, 11 Nov 2003 11:52:51 -0600
>| Lines: 206
>| X-Priority: 3
>| X-MSMail-Priority: Normal
>| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>| Message-ID: <#zx$buHqDHA.1124@.TK2MSFTNGP09.phx.gbl>
>| Newsgroups: microsoft.public.sqlserver.server
>| NNTP-Posting-Host: node81.carco.com 209.145.188.81
>| Path:
>cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP
0
>8.phx.gbl!TK2MSFTNGP09.phx.gbl
>| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:315914
>| X-Tomcat-NG: microsoft.public.sqlserver.server
>|
>| William,
>|
>| What kind of problems will this cause if users are in the databases that
>I'm
>| testing during the 1 minute interval tests?
>|
>| Also, in the job history of the "DB Backup Job for Maintenance Plan...",
>| here is what it shows:
>|
>| The job failed. The Job was invoked by Schedule 9 (Schedule 1). The las
t
>| step to run was step 1 (Step 1).
>|
>| STEP DETAILS:
>|
>| Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
>42000]
>| (Error 22029). The step failed.
>|
>| Thanks,
>| David
>|
>| "William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
>| news:gA17gTHqDHA.576@.cpmsftngxa06.phx.gbl...
>| > Hi David,
>| >
>| > Thanks for the update. The output file could be set when you created th
e
>| Maintenace Plan using the Database
>| > Maintenance Plan Wizard. Check the option "Write Report to a text file
>in
>| directory" at the step "Reports to
>| > Generate".
>| >
>| > Specify the full path and name of the text file into which the report i
s
>| to be generated. The report contains
>| > details of the steps executed by the maintenance plan, including any
>error
>| information. The report maintains
>| > version information by adding a date to the file name. The date is
>| generated as a suffix to the file name but
>| > before the extension, in the form _YYYYMMDDHHMM. For example: "DB
>| Maintenance Plan10_199804090838.txt".
>| >
>| > Click the browse (...) button to change the default directory for the
>text
>| file. Only directories on the computer
>| > running the maintenance plan can be selected.
>| >
>| > Please also expand SQL Server Group->Server name->Management->Sql serve
r
>| agent->Jobs, check the jobs
>| > named "DB Backup Job for Maintenace Plan..." and "Transaction log for
>| Backup Job for Maintenace Plan..." to
>| > see who is the owner of the job and if the owner has the delete
>| permission.
>| >
>| > Since the error message in the output file would be helpful to
>| troubleshoot this issue, if you didn't check the
>| > option "Write Report to a text file in directory" at the step "Reports
>to
>| Generate" of the Database Maintenance
>| > Plan Wizard, I'd recommend creating a new Maintenace Plan to test the
>| problem. You may want to set the
>| > domain admin as the startup account of both SQL Server Service and SQL
>| Agent Service. For the purpose of
>| > testing, you could set a short period of time such as 1 minute which
>the
>| backup files are older than will be
>| > deleted.
>| >
>| > Sincerely,
>| >
>| > William Wang
>| > Microsoft Partner Online Support
>| >
>| > Get Secure! - www.microsoft.com/security
>| > ========================================
=============
>| > When responding to posts, please "Reply to Group" via
>| > your newsreader so that others may learn and benefit
>| > from your issue.
>| > ========================================
=============
>| >
>| > This posting is provided "AS IS" with no warranties, and confers no
>| rights.
>| > --
>| > > From: "David DeJarnett" <no@.email.com>
>| > > References: <OaPuhO6pDHA.2848@.TK2MSFTNGP10.phx.gbl>
>| <KvdbvPAqDHA.444@.cpmsftngxa06.phx.gbl>
>| > > Subject: Re: Database Maintenance Plan Problems
>| > > Date: Tue, 11 Nov 2003 08:51:34 -0600
>| > > Lines: 96
>| > > X-Priority: 3
>| > > X-MSMail-Priority: Normal
>| > > X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>| > > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>| > > Message-ID: <eqFOJJGqDHA.1084@.tk2msftngp13.phx.gbl>
>| > > Newsgroups: microsoft.public.sqlserver.server
>| > > NNTP-Posting-Host: node81.carco.com 209.145.188.81
>| > > Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
>| > > Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:315843
>| > > X-Tomcat-NG: microsoft.public.sqlserver.server
>| > >
>| > > William,
>| > >
>| > > I tried setting the SQL Agent startup account as both Local System an
d
>| the
>| > > Domain Administrator account and had the same problems. The NTFS
>| > > permissions on the folder with the SQL backups is set to allow
>everyone
>| full
>| > > access.
>| > >
>| > > Yes, I am able to manually delete the files using the Domain Admin
>| account.
>| > >
>| > > I shut off all the antivirus services before the job ran and still no
>| luck.
>| > >
>| > > Where do I find the Database Maintenance Plan output file to send to
>| you?
>| > >
>| > > Thanks for your help.
>| > >
>| > > David
>| > >
>| > > "William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in mes
sage
>| > > news:KvdbvPAqDHA.444@.cpmsftngxa06.phx.gbl...
>| > > > Hi David,
>| > > >
>| > > > Thanks for posting here. First please check the Database Maintenanc
e
>| Plan
>| > > output file to see if there are any
>| > > > error messages, you may also want to send the output file to me:
>| > > v-rxwang@.microsoft.com.
>| > > >
>| > > > Please perform the following steps and let me know the results:
>| > > >
>| > > > 1. Verify the SQL Agent startup account has Full Control,
>specifically
>| the
>| > > Delete permissions to the backup files.
>| > > > 2. Verify no process such as NT backup or Anti-Virus is locking the
>| files.
>| > > You may want to stop all unnecessary
>| > > > services and applications to test the problem.
>| > > > 3. Log on the server as the SQL Agent startup account. Delete the
>| backup
>| > > files that are older than 3 days
>| > > > manually. Can you delete them successfully?
>| > > >
>| > > > I'm looking forward to hearing from you.
>| > > >
>| > > > Sincerely,
>| > > >
>| > > > William Wang
>| > > > Microsoft Partner Online Support
>| > > >
>| > > > Get Secure! - www.microsoft.com/security
>| > > > ========================================
=============
>| > > > When responding to posts, please "Reply to Group" via
>| > > > your newsreader so that others may learn and benefit
>| > > > from your issue.
>| > > > ========================================
=============
>| > > >
>| > > > This posting is provided "AS IS" with no warranties, and confers no
>| > > rights.
>| > > > --
>| > > > > From: "David DeJarnett" <no@.email.com>
>| > > > > Subject: Database Maintenance Plan Problems
>| > > > > Date: Mon, 10 Nov 2003 10:06:43 -0600
>| > > > > Lines: 18
>| > > > > X-Priority: 3
>| > > > > X-MSMail-Priority: Normal
>| > > > > X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>| > > > > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>| > > > > Message-ID: <OaPuhO6pDHA.2848@.TK2MSFTNGP10.phx.gbl>
>| > > > > Newsgroups: microsoft.public.sqlserver.server
>| > > > > NNTP-Posting-Host: node81.carco.com 209.145.188.81
>| > > > > Path:
>cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
>| > > > > Xref: cpmsftngxa06.phx.gbl
>microsoft.public.sqlserver.server:315611
>| > > > > X-Tomcat-NG: microsoft.public.sqlserver.server
>| > > > >
>| > > > > Hello All!
>| > > > >
>| > > > > I'm new to SQL 2000, so please go easy on me.
>| > > > >
>| > > > > I'm running an SBS2000 server with SQL serving up about 7 small
>| > > databases.
>| > > > > I have a maintenance plan in place that runs every night backing
>up
>| each
>| > > of
>| > > > > the databases to a folder which in turn gets backed up by tape tw
o
>| hours
>| > > > > later. I have the plan configured to delete transaction logs and
>| > > database
>| > > > > files older than 3 days. The backups are running fine, but the
>plan
>| is
>| > > not
>| > > > > deleting the old backup files and transaction logs.
>| > > > >
>| > > > > Where should I start as far as determining the problem?
>| > > > >
>| > > > > Thank you for any help in advance!
>| > > > >
>| > > > > David
>| > > > >
>| > > > >
>| > > > >
>| > > >
>| > > >
>| > >
>| > >
>| > >
>| >
>| >
>|
>|
>|Hi Tom,
Thanks for your posting. If the methods in the KB
article 303292 doesn't resolve your problem. Please
open a new post for your question. That way each
issue can receive full attention from the Support
Professional to whom it is assigned. This will also
make the thread more clear and consistent for your
reference.
Sincerely,
William Wang
Microsoft Online Support Engineer
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties,
and confers no rights.
--
>From: Tom Griffin <spam@.ham.com>
>Newsgroups: microsoft.public.sqlserver.server
>Subject: Re: Database Maintenance Plan Problems
>Organization: SPAM is ham, Treat is mystery meat!
>Message-ID:
<vcdq30503lolosprqee1k2e9pssonjfbcq@.4ax.com>
>References: <OaPuhO6pDHA.2848@.TK2MSFTNGP10.phx.gbl>
<KvdbvPAqDHA.444@.cpmsftngxa06.phx.gbl>
<eqFOJJGqDHA.1084@.tk2msftngp13.phx.gbl>
<gA17gTHqDHA.576@.cpmsftngxa06.phx.gbl>
<#zx$buHqDHA.1124@.TK2MSFTNGP09.phx.gbl>
<3m1XUjxqDHA.1548@.cpmsftngxa06.phx.gbl>
>X-Newsreader: Forte Agent 1.91/32.564
>MIME-Version: 1.0
>Content-Type: text/plain; charset=ISO-8859-1
>Content-Transfer-Encoding: 8bit
>Lines: 342
>X-Complaints-To: abuse@.easynews.com
>X-Complaints-Info: Please be sure to forward a copy
of ALL headers otherwise we will be unable to process
your complaint properly.
>Date: Wed, 25 Feb 2004 23:56:56 GMT
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!newsfeed.freenet.de!fr.ip.n
dsoftware.net!newshosting.com!nx01.iad01.newshosting.c
om!140.99.99.194.MISMATCH!newsfeed1.easynews.com!easyn
ews.com!easynews!easynews-local!news.easynews.com.POST
ED!not-for-mail
>Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.server:331223
>X-Tomcat-NG: microsoft.public.sqlserver.server
>We are also experiencing this problem on all of our
SQL Server 2000
>servers (sp3a), so I'm glad to have found your
discussion. I'll reply
>back as soon as I can figure out if KB 303292
resolves our problem.
>Thanks,
>Tom
>
>On Sat, 15 Nov 2003 01:36:00 GMT,
jyothip@.online.microsoft.com (Jyothi
>Pai [MSFT]) wrote:
>
explained in this article.
indeed the problem, you
the system databases such
databases (if all of the
Otherwise, create separate
their recovery model.
file to assist you further.
Deleted by Maintenance Plan
-US;303292
may fail. The
'DB Maintenance Plan1'"
(Schedule 1). The last
message:
22029). The step failed.
msdb.dbo.sysdbmaintplan_history reporting
an entry similar to:
sub task is ignored
reporting option is
This sub task is ignored.
not be deleted. Although
performed correctly.
circumstances:
Maintenance Plan.
transaction log backups.
databases in the Maintenance
of the maintenance plan"
command on each
SIMPLE recovery mode
recovery model is SIMPLE.
using ALTER DATABASE.
and continues to back up
Maintenance Plan. However,
without removing expired
Plan contains only
models. The second
recovery mode. Do not perform
Plan.
SIMPLE to either FULL or
the databases.
warranties, and confers no rights.
<OaPuhO6pDHA.2848@.TK2MSFTNGP10.phx.gbl>
6.00.2800.1158
V6.00.2800.1165
<#zx$buHqDHA.1124@.TK2MSFTNGP09.phx.gbl>
XA05.phx.gbl!TK2MSFTNGP0
microsoft.public.sqlserver.server:315914
are in the databases that
for Maintenance Plan...",
9 (Schedule 1). The last
sqlmaint.exe failed. [SQLSTATE
<v-rxwang@.online.microsoft.com> wrote in message
set when you created the
"Write Report to a text file
into which the report is
maintenance plan, including any
file name. The date is
_YYYYMMDDHHMM. For example: "DB
default directory for the
name->Management->Sql server
and "Transaction log for
owner has the delete
would be helpful to
directory" at the step "Reports
Maintenace Plan to test the
Server Service and SQL
such as 1 minute which
========================================
=============
Group" via
benefit
========================================
=============
warranties, and confers no
<OaPuhO6pDHA.2848@.TK2MSFTNGP10.phx.gbl>
Problems
6.00.2800.1158
V6.00.2800.1165
<eqFOJJGqDHA.1084@.tk2msftngp13.phx.gbl>
209.145.188.81
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13
.phx.gbl
microsoft.public.sqlserver.server:315843
as both Local System and
problems. The NTFS
backups is set to allow
using the Domain Admin
the job ran and still no
output file to send to
<v-rxwang@.online.microsoft.com> wrote in message
the Database Maintenance
the output file to me:
me know the results:
Full Control,
Anti-Virus is locking the
problem.
startup account. Delete the
========================================
=============
Group" via
and benefit
========================================
=============
warranties, and confers no
Problems
6.00.2800.1158
V6.00.2800.1165
<OaPuhO6pDHA.2848@.TK2MSFTNGP10.phx.gbl>
microsoft.public.sqlserver.server
209.145.188.81
10.phx.gbl
microsoft.public.sqlserver.server
me.
serving up about 7 small
runs every night backing
gets backed up by tape two
delete transaction logs and
running fine, but the
transaction logs.
determining the problem?
>