Re: Database Maintenance Plan Problems

From: William Wang[MSFT] (v-rxwang_at_online.microsoft.com)
Date: 02/26/04 

Date: Thu, 26 Feb 2004 01:15:47 GMT

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:
>
>>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/default.aspx?scid=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!TK2MSFTNG
XA05.phx.gbl!TK2MSFTNGP0
>>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 last
>>| 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 the
>>| 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 is
>>| 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 server
>>| 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 and
>>| 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 message
>>| > > news:KvdbvPAqDHA.444@cpmsftngxa06.phx.gbl...
>>| > > > Hi David,
>>| > > >
>>| > > > Thanks for posting here. First please check
the Database Maintenance
>>| 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!TK2MSFTNGP
10.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 two
>>| 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
>>| > > > >
>>| > > > >
>>| > > > >
>>| > > >
>>| > > >
>>| > >
>>| > >
>>| > >
>>| >
>>| >
>>|
>>|
>>|
>
>