Database Maintenance Plan Problems

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!
DavidHi 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!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 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
>
>|||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!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 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
> >
> >
> >
>|||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!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 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
> > >
> > >
> > >
> >
> >
>
>|||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!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 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
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >
>|||Hi David,
Thanks for the update. The job history doesn't give us much useful information, it just indicates that some task
in the Database Maintenance plan failed. Testing a new Database Maintenance plan should not affect the users
in the databases. If you don't want to create a new Database Maintenance plan, you may perform the following
steps:
1. In SQL Server Enterprise Manager, navigate to the Database Maintenance Plans. Select and then double-click
the plan you want.
2. Click the Reporting tab. In the Reporting dialog box, you can choose to write a report to a text file in a
specified directory. The report contains details of the steps executed by the maintenance plan, and includes any
error information.
When the issue occurs, send the following files to me for further research:
- The Maintenance plan output file.
- The error log file.
Please verify there is no process such as NT Backup is locking on the files that are to be deleted when the job is
running.
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>
<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!TK2MSFTNGP08.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!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 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
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>
>|||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!TK2MSFTNGXA05.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!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 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
| > > > >
| > > > >
| > > > >
| > > >
| > > >
| > >
| > >
| > >
| >
| >
|
|
||||Hi David,
We are currently standing by for an update from you and would like to know how things are going there. Please
update this thread with your response at your convenience. Should you have any questions or concerns regarding
to this issue, please don't hesitate to let us know directly. It's our pleasure to be of assistance.
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.
--
> X-Tomcat-ID: 45274058
> 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>
> MIME-Version: 1.0
> Content-Type: text/plain
> Content-Transfer-Encoding: 7bit
> From: jyothip@.online.microsoft.com (Jyothi Pai [MSFT])
> Organization: Microsoft
> Date: Sat, 15 Nov 2003 01:36:00 GMT
> Subject: Re: Database Maintenance Plan Problems
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Message-ID: <3m1XUjxqDHA.1548@.cpmsftngxa06.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> Lines: 305
> Path: cpmsftngxa06.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:316668
> NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
> 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!TK2MSFTNGXA05.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!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 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
> | > > > >
> | > > > >
> | > > > >
> | > > >
> | > > >
> | > >
> | > >
> | > >
> | >
> | >
> |
> |
> |
>|||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!TK2MSFTNGXA05.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!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 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
>| > > > >
>| > > > >
>| > > > >
>| > > >
>| > > >
>| > >
>| > >
>| > >
>| >
>| >
>|
>|
>||||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