Maintenance Plans and Delete files

We have been experiencing a "problem" with errors running Maintenance Plan
Jobs.
This is a Transaction Log and Full Backup Maintenance Plan.
Below is the code for the Full Backup Job step...............
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia DISK -BkUpDB
"L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
We are deleting backups over 36 hours old because we cannot trust the end
time of the previous backup. The SQLServer Agent Job shows a failure for the
MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I have
possibly traced it back to the "DELETE FILES" step of the MaintPlan. This
appears to fail with no error in the MaintPlan History.
The free drivespace is tight, there is slightly more room (~1gb) than 3
fulls of the largest backup(~20gb each) (we are saving 2). The MaintPlan
needs at least enough room to create the new backup before it can delete the
oldest backup. The Backup succeeds, but the delete fails (leaves behind the
oldest backup, total 3 fulls, should be 2), the job fails, but the MaitPlan
doesn't and there is no record of a delete or any attempt to delete or an
error. My guess is that if a delete does not happen for any reason, a
MaintPlan history entry for the delete step is not made.
Any ideas..............
Frankm
You know you are in trouble when a
learning curve becomes a death spiralMake sure you don't have any of the Expire or Retain Dates set but here is
a pretty good overview of why this usually happens by Bill from MS:
-- Log files don't delete --
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
--
Andrew J. Kelly
SQL Server MVP
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> We have been experiencing a "problem" with errors running Maintenance Plan
> Jobs.
> This is a Transaction Log and Full Backup Maintenance Plan.
> Below is the code for the Full Backup Job step...............
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
DISK -BkUpDB
> "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> We are deleting backups over 36 hours old because we cannot trust the end
> time of the previous backup. The SQLServer Agent Job shows a failure for
the
> MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I have
> possibly traced it back to the "DELETE FILES" step of the MaintPlan. This
> appears to fail with no error in the MaintPlan History.
> The free drivespace is tight, there is slightly more room (~1gb) than 3
> fulls of the largest backup(~20gb each) (we are saving 2). The MaintPlan
> needs at least enough room to create the new backup before it can delete
the
> oldest backup. The Backup succeeds, but the delete fails (leaves behind
the
> oldest backup, total 3 fulls, should be 2), the job fails, but the
MaitPlan
> doesn't and there is no record of a delete or any attempt to delete or an
> error. My guess is that if a delete does not happen for any reason, a
> MaintPlan history entry for the delete step is not made.
> Any ideas..............
> Frankm
> You know you are in trouble when a
> learning curve becomes a death spiral
>|||SQL Server service and SQL Server Agent both run under the same domain login
that has local admin rights on the machine. There are no problems logging in
with that login and doing anything, we do it everyday. The job owner is also
that account.
This does not happen all the time, only occasionally. The fact that the
DELETE FILES fails is one part. There is also the problem with the fact that
there is a job failure with a message pointing to the sqlmaint.exe, but
there is absolutely no indication of any failure at the Maintenance Plan.
Good point about another process holding the file(s). I will check to see if
another process has the file, but I am unclear as to why the Maintenance
Plan does not show an error.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> Make sure you don't have any of the Expire or Retain Dates set but here is
> a pretty good overview of why this usually happens by Bill from MS:
>
> -- Log files don't delete --
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer
to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to
consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
to
> local drive), then you will need to modify the above (with respect to
where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > We have been experiencing a "problem" with errors running Maintenance
Plan
> > Jobs.
> > This is a Transaction Log and Full Backup Maintenance Plan.
> > Below is the code for the Full Backup Job step...............
> > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> DISK -BkUpDB
> > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> >
> > We are deleting backups over 36 hours old because we cannot trust the
end
> > time of the previous backup. The SQLServer Agent Job shows a failure for
> the
> > MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I
have
> > possibly traced it back to the "DELETE FILES" step of the MaintPlan.
This
> > appears to fail with no error in the MaintPlan History.
> >
> > The free drivespace is tight, there is slightly more room (~1gb) than 3
> > fulls of the largest backup(~20gb each) (we are saving 2). The MaintPlan
> > needs at least enough room to create the new backup before it can delete
> the
> > oldest backup. The Backup succeeds, but the delete fails (leaves behind
> the
> > oldest backup, total 3 fulls, should be 2), the job fails, but the
> MaitPlan
> > doesn't and there is no record of a delete or any attempt to delete or
an
> > error. My guess is that if a delete does not happen for any reason, a
> > MaintPlan history entry for the delete step is not made.
> >
> > Any ideas..............
> >
> > Frankm
> > You know you are in trouble when a
> > learning curve becomes a death spiral
> >
> >
>|||The lack of traceability with the maintenance plan is one of the many
reasons I never recommend using it. If you created your own scheduled jobs
to do those tasks you would have a much better grasp on the situation when
things go wrong.
--
Andrew J. Kelly
SQL Server MVP
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:O4crQvOXDHA.2592@.TK2MSFTNGP09.phx.gbl...
> SQL Server service and SQL Server Agent both run under the same domain
login
> that has local admin rights on the machine. There are no problems logging
in
> with that login and doing anything, we do it everyday. The job owner is
also
> that account.
> This does not happen all the time, only occasionally. The fact that the
> DELETE FILES fails is one part. There is also the problem with the fact
that
> there is a job failure with a message pointing to the sqlmaint.exe, but
> there is absolutely no indication of any failure at the Maintenance Plan.
> Good point about another process holding the file(s). I will check to see
if
> another process has the file, but I am unclear as to why the Maintenance
> Plan does not show an error.
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> > Make sure you don't have any of the Expire or Retain Dates set but here
is
> > a pretty good overview of why this usually happens by Bill from MS:
> >
> >
> > -- Log files don't delete --
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> >
> > This is likely to be either a permissions problem or a sharing violation
> > problem. The maintenance plan is run as a job, and jobs are run by the
> > SQLServerAgent service.
> >
> > Permissions:
> > 1. Determine the startup account for the SQLServerAgent service
> > (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
> This
> > account is the security context for jobs, and thus the maintenance plan.
> > 2. If SQLServerAgent is started using LocalSystem (as opposed to a
domain
> > account) then skip step 3.
> > 3. On that box, log onto NT as that account. Using Explorer, attempt to
> > delete an expired backup. If that succeeds then go to Sharing Violation
> > section.
> > 4. Log onto NT with an account that is an administrator and use Explorer
> to
> > look at the Properties|Security of the folder (where the backups reside)
> > and ensure the SQLServerAgent startup account has Full Control. If the
> > SQLServerAgent startup account is LocalSystem, then the account to
> consider
> > is SYSTEM.
> > 5. In NT, if an account is a member of an NT group, and if that group
has
> > Access is Denied, then that account will have Access is Denied, even if
> > that account is also a member of the Administrators group. Thus you may
> > need to check group permissions (if the Startup Account is a member of a
> > group).
> > 6. Keep in mind that permissions (by default) are inherited from a
parent
> > folder. Thus, if the backups are stored in C:\bak, and if someone had
> > denied permission to the SQLServerAgent startup account for C:\, then
> > C:\bak will inherit access is denied.
> >
> > Sharing violation:
> > This is likely to be rooted in a timing issue, with the most likely
cause
> > being another scheduled process (such as NT Backup or Anti-Virus
software)
> > having the backup file open at the time when the SQLServerAgent (i.e.,
the
> > maintenance plan job) tried to delete it.
> > 1. Download filemon and handle from www.sysinternals.com.
> > 2. I am not sure whether filemon can be scheduled, or you might be able
to
> > use NT scheduling services to start filemon just before the maintenance
> > plan job is started, but the filemon log can become very large, so it
> would
> > be best to start it some short time before the maintenance plan starts.
> > 3. Inspect the filemon log for another process that has that backup file
> > open (if your lucky enough to have started filemon before this other
> > process grabs the backup folder), and inspect the log for the results
when
> > the SQLServerAgent agent attempts to open that same file.
> > 4. Schedule the job or that other process to do their work at different
> > times.
> > 5. You can use the handle utility if you are around at the time when the
> > job is scheduled to run.
> >
> > If the backup files are going to a \\share or a mapped drive (as opposed
> to
> > local drive), then you will need to modify the above (with respect to
> where
> > the tests and utilities are run).
> >
> > Finally, inspection of the maintenance plan's history report might be
> > useful.
> >
> > Thanks,
> >
> > Bill Hollinshead
> > Microsoft, SQL Server
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > > We have been experiencing a "problem" with errors running Maintenance
> Plan
> > > Jobs.
> > > This is a Transaction Log and Full Backup Maintenance Plan.
> > > Below is the code for the Full Backup Job step...............
> > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> > DISK -BkUpDB
> > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> > >
> > > We are deleting backups over 36 hours old because we cannot trust the
> end
> > > time of the previous backup. The SQLServer Agent Job shows a failure
for
> > the
> > > MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I
> have
> > > possibly traced it back to the "DELETE FILES" step of the MaintPlan.
> This
> > > appears to fail with no error in the MaintPlan History.
> > >
> > > The free drivespace is tight, there is slightly more room (~1gb) than
3
> > > fulls of the largest backup(~20gb each) (we are saving 2). The
MaintPlan
> > > needs at least enough room to create the new backup before it can
delete
> > the
> > > oldest backup. The Backup succeeds, but the delete fails (leaves
behind
> > the
> > > oldest backup, total 3 fulls, should be 2), the job fails, but the
> > MaitPlan
> > > doesn't and there is no record of a delete or any attempt to delete or
> an
> > > error. My guess is that if a delete does not happen for any reason, a
> > > MaintPlan history entry for the delete step is not made.
> > >
> > > Any ideas..............
> > >
> > > Frankm
> > > You know you are in trouble when a
> > > learning curve becomes a death spiral
> > >
> > >
> >
> >
>|||I am not sure if you were thinking of the MVP's as being part of the
Microsoft respondents but did want to make sure people understand we are not
actually employees of MS. We are all just volunteers that have been
acknowledged by MS in the MVP program. In either case I also want to point
out that there are lots of good answers from people other than MS or MVP's
that shouldn't go unrecognized either<g>.
Thanks
Andrew J. Kelly
SQL Server MVP
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:Ouu6hcPXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> Yep, In previous jobs I did my own scripting, but in this job they use
> MaintPlans almost
> exclusively. I guess it's because all you have to do is clickity,
clickity,
> click.... and voila!
> Thanks, I now know that the lack of a error message is a feature, and
adjust
> accordingly..
> *****************************************
> To Microsoft respondents on these newsgroups... Your help is GREATLY
> appreciated and
> worth every penny. It really helps to be able to ask questions, get
answers
> and get on with my job.
> Thanks to all....................................
> *****************************************
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OaWxR4OXDHA.2524@.TK2MSFTNGP09.phx.gbl...
> > The lack of traceability with the maintenance plan is one of the many
> > reasons I never recommend using it. If you created your own scheduled
> jobs
> > to do those tasks you would have a much better grasp on the situation
when
> > things go wrong.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > news:O4crQvOXDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > SQL Server service and SQL Server Agent both run under the same domain
> > login
> > > that has local admin rights on the machine. There are no problems
> logging
> > in
> > > with that login and doing anything, we do it everyday. The job owner
is
> > also
> > > that account.
> > > This does not happen all the time, only occasionally. The fact that
the
> > > DELETE FILES fails is one part. There is also the problem with the
fact
> > that
> > > there is a job failure with a message pointing to the sqlmaint.exe,
but
> > > there is absolutely no indication of any failure at the Maintenance
> Plan.
> > >
> > > Good point about another process holding the file(s). I will check to
> see
> > if
> > > another process has the file, but I am unclear as to why the
Maintenance
> > > Plan does not show an error.
> > >
> > >
> > >
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> > > > Make sure you don't have any of the Expire or Retain Dates set but
> here
> > is
> > > > a pretty good overview of why this usually happens by Bill from MS:
> > > >
> > > >
> > > > -- Log files don't delete --
> > > >
> > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> > > >
> > > > This is likely to be either a permissions problem or a sharing
> violation
> > > > problem. The maintenance plan is run as a job, and jobs are run by
the
> > > > SQLServerAgent service.
> > > >
> > > > Permissions:
> > > > 1. Determine the startup account for the SQLServerAgent service
> > > > (Start|Programs|Administrative
tools|Services|SQLServerAgent|Startup).
> > > This
> > > > account is the security context for jobs, and thus the maintenance
> plan.
> > > > 2. If SQLServerAgent is started using LocalSystem (as opposed to a
> > domain
> > > > account) then skip step 3.
> > > > 3. On that box, log onto NT as that account. Using Explorer, attempt
> to
> > > > delete an expired backup. If that succeeds then go to Sharing
> Violation
> > > > section.
> > > > 4. Log onto NT with an account that is an administrator and use
> Explorer
> > > to
> > > > look at the Properties|Security of the folder (where the backups
> reside)
> > > > and ensure the SQLServerAgent startup account has Full Control. If
the
> > > > SQLServerAgent startup account is LocalSystem, then the account to
> > > consider
> > > > is SYSTEM.
> > > > 5. In NT, if an account is a member of an NT group, and if that
group
> > has
> > > > Access is Denied, then that account will have Access is Denied, even
> if
> > > > that account is also a member of the Administrators group. Thus you
> may
> > > > need to check group permissions (if the Startup Account is a member
of
> a
> > > > group).
> > > > 6. Keep in mind that permissions (by default) are inherited from a
> > parent
> > > > folder. Thus, if the backups are stored in C:\bak, and if someone
had
> > > > denied permission to the SQLServerAgent startup account for C:\,
then
> > > > C:\bak will inherit access is denied.
> > > >
> > > > Sharing violation:
> > > > This is likely to be rooted in a timing issue, with the most likely
> > cause
> > > > being another scheduled process (such as NT Backup or Anti-Virus
> > software)
> > > > having the backup file open at the time when the SQLServerAgent
(i.e.,
> > the
> > > > maintenance plan job) tried to delete it.
> > > > 1. Download filemon and handle from www.sysinternals.com.
> > > > 2. I am not sure whether filemon can be scheduled, or you might be
> able
> > to
> > > > use NT scheduling services to start filemon just before the
> maintenance
> > > > plan job is started, but the filemon log can become very large, so
it
> > > would
> > > > be best to start it some short time before the maintenance plan
> starts.
> > > > 3. Inspect the filemon log for another process that has that backup
> file
> > > > open (if your lucky enough to have started filemon before this other
> > > > process grabs the backup folder), and inspect the log for the
results
> > when
> > > > the SQLServerAgent agent attempts to open that same file.
> > > > 4. Schedule the job or that other process to do their work at
> different
> > > > times.
> > > > 5. You can use the handle utility if you are around at the time when
> the
> > > > job is scheduled to run.
> > > >
> > > > If the backup files are going to a \\share or a mapped drive (as
> opposed
> > > to
> > > > local drive), then you will need to modify the above (with respect
to
> > > where
> > > > the tests and utilities are run).
> > > >
> > > > Finally, inspection of the maintenance plan's history report might
be
> > > > useful.
> > > >
> > > > Thanks,
> > > >
> > > > Bill Hollinshead
> > > > Microsoft, SQL Server
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > > > > We have been experiencing a "problem" with errors running
> Maintenance
> > > Plan
> > > > > Jobs.
> > > > > This is a Transaction Log and Full Backup Maintenance Plan.
> > > > > Below is the code for the Full Backup Job step...............
> > > > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > > > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> > > > DISK -BkUpDB
> > > > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> > > > >
> > > > > We are deleting backups over 36 hours old because we cannot trust
> the
> > > end
> > > > > time of the previous backup. The SQLServer Agent Job shows a
failure
> > for
> > > > the
> > > > > MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan.
I
> > > have
> > > > > possibly traced it back to the "DELETE FILES" step of the
MaintPlan.
> > > This
> > > > > appears to fail with no error in the MaintPlan History.
> > > > >
> > > > > The free drivespace is tight, there is slightly more room (~1gb)
> than
> > 3
> > > > > fulls of the largest backup(~20gb each) (we are saving 2). The
> > MaintPlan
> > > > > needs at least enough room to create the new backup before it can
> > delete
> > > > the
> > > > > oldest backup. The Backup succeeds, but the delete fails (leaves
> > behind
> > > > the
> > > > > oldest backup, total 3 fulls, should be 2), the job fails, but the
> > > > MaitPlan
> > > > > doesn't and there is no record of a delete or any attempt to
delete
> or
> > > an
> > > > > error. My guess is that if a delete does not happen for any
reason,
> a
> > > > > MaintPlan history entry for the delete step is not made.
> > > > >
> > > > > Any ideas..............
> > > > >
> > > > > Frankm
> > > > > You know you are in trouble when a
> > > > > learning curve becomes a death spiral
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I didn't mean to slight anyone. I was unaware of the status and
affiliation of people responding to questions.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eWMgYZTXDHA.2328@.TK2MSFTNGP12.phx.gbl...
> I am not sure if you were thinking of the MVP's as being part of the
> Microsoft respondents but did want to make sure people understand we are
not
> actually employees of MS. We are all just volunteers that have been
> acknowledged by MS in the MVP program. In either case I also want to
point
> out that there are lots of good answers from people other than MS or MVP's
> that shouldn't go unrecognized either<g>.
> Thanks
>
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> news:Ouu6hcPXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> > Yep, In previous jobs I did my own scripting, but in this job they use
> > MaintPlans almost
> > exclusively. I guess it's because all you have to do is clickity,
> clickity,
> > click.... and voila!
> >
> > Thanks, I now know that the lack of a error message is a feature, and
> adjust
> > accordingly..
> >
> > *****************************************
> > To Microsoft respondents on these newsgroups... Your help is GREATLY
> > appreciated and
> > worth every penny. It really helps to be able to ask questions, get
> answers
> > and get on with my job.
> > Thanks to all....................................
> > *****************************************
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OaWxR4OXDHA.2524@.TK2MSFTNGP09.phx.gbl...
> > > The lack of traceability with the maintenance plan is one of the many
> > > reasons I never recommend using it. If you created your own
scheduled
> > jobs
> > > to do those tasks you would have a much better grasp on the situation
> when
> > > things go wrong.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > news:O4crQvOXDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > > SQL Server service and SQL Server Agent both run under the same
domain
> > > login
> > > > that has local admin rights on the machine. There are no problems
> > logging
> > > in
> > > > with that login and doing anything, we do it everyday. The job owner
> is
> > > also
> > > > that account.
> > > > This does not happen all the time, only occasionally. The fact that
> the
> > > > DELETE FILES fails is one part. There is also the problem with the
> fact
> > > that
> > > > there is a job failure with a message pointing to the sqlmaint.exe,
> but
> > > > there is absolutely no indication of any failure at the Maintenance
> > Plan.
> > > >
> > > > Good point about another process holding the file(s). I will check
to
> > see
> > > if
> > > > another process has the file, but I am unclear as to why the
> Maintenance
> > > > Plan does not show an error.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> > > > > Make sure you don't have any of the Expire or Retain Dates set but
> > here
> > > is
> > > > > a pretty good overview of why this usually happens by Bill from
MS:
> > > > >
> > > > >
> > > > > -- Log files don't delete --
> > > > >
> > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> > > > >
> > > > > This is likely to be either a permissions problem or a sharing
> > violation
> > > > > problem. The maintenance plan is run as a job, and jobs are run by
> the
> > > > > SQLServerAgent service.
> > > > >
> > > > > Permissions:
> > > > > 1. Determine the startup account for the SQLServerAgent service
> > > > > (Start|Programs|Administrative
> tools|Services|SQLServerAgent|Startup).
> > > > This
> > > > > account is the security context for jobs, and thus the maintenance
> > plan.
> > > > > 2. If SQLServerAgent is started using LocalSystem (as opposed to a
> > > domain
> > > > > account) then skip step 3.
> > > > > 3. On that box, log onto NT as that account. Using Explorer,
attempt
> > to
> > > > > delete an expired backup. If that succeeds then go to Sharing
> > Violation
> > > > > section.
> > > > > 4. Log onto NT with an account that is an administrator and use
> > Explorer
> > > > to
> > > > > look at the Properties|Security of the folder (where the backups
> > reside)
> > > > > and ensure the SQLServerAgent startup account has Full Control. If
> the
> > > > > SQLServerAgent startup account is LocalSystem, then the account to
> > > > consider
> > > > > is SYSTEM.
> > > > > 5. In NT, if an account is a member of an NT group, and if that
> group
> > > has
> > > > > Access is Denied, then that account will have Access is Denied,
even
> > if
> > > > > that account is also a member of the Administrators group. Thus
you
> > may
> > > > > need to check group permissions (if the Startup Account is a
member
> of
> > a
> > > > > group).
> > > > > 6. Keep in mind that permissions (by default) are inherited from a
> > > parent
> > > > > folder. Thus, if the backups are stored in C:\bak, and if someone
> had
> > > > > denied permission to the SQLServerAgent startup account for C:\,
> then
> > > > > C:\bak will inherit access is denied.
> > > > >
> > > > > Sharing violation:
> > > > > This is likely to be rooted in a timing issue, with the most
likely
> > > cause
> > > > > being another scheduled process (such as NT Backup or Anti-Virus
> > > software)
> > > > > having the backup file open at the time when the SQLServerAgent
> (i.e.,
> > > the
> > > > > maintenance plan job) tried to delete it.
> > > > > 1. Download filemon and handle from www.sysinternals.com.
> > > > > 2. I am not sure whether filemon can be scheduled, or you might be
> > able
> > > to
> > > > > use NT scheduling services to start filemon just before the
> > maintenance
> > > > > plan job is started, but the filemon log can become very large, so
> it
> > > > would
> > > > > be best to start it some short time before the maintenance plan
> > starts.
> > > > > 3. Inspect the filemon log for another process that has that
backup
> > file
> > > > > open (if your lucky enough to have started filemon before this
other
> > > > > process grabs the backup folder), and inspect the log for the
> results
> > > when
> > > > > the SQLServerAgent agent attempts to open that same file.
> > > > > 4. Schedule the job or that other process to do their work at
> > different
> > > > > times.
> > > > > 5. You can use the handle utility if you are around at the time
when
> > the
> > > > > job is scheduled to run.
> > > > >
> > > > > If the backup files are going to a \\share or a mapped drive (as
> > opposed
> > > > to
> > > > > local drive), then you will need to modify the above (with respect
> to
> > > > where
> > > > > the tests and utilities are run).
> > > > >
> > > > > Finally, inspection of the maintenance plan's history report might
> be
> > > > > useful.
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Bill Hollinshead
> > > > > Microsoft, SQL Server
> > > > >
> > > > > --
> > > > >
> > > > > Andrew J. Kelly
> > > > > SQL Server MVP
> > > > >
> > > > >
> > > > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > > > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > > > > > We have been experiencing a "problem" with errors running
> > Maintenance
> > > > Plan
> > > > > > Jobs.
> > > > > > This is a Transaction Log and Full Backup Maintenance Plan.
> > > > > > Below is the code for the Full Backup Job
step...............
> > > > > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > > > > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> > > > > DISK -BkUpDB
> > > > > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> > > > > >
> > > > > > We are deleting backups over 36 hours old because we cannot
trust
> > the
> > > > end
> > > > > > time of the previous backup. The SQLServer Agent Job shows a
> failure
> > > for
> > > > > the
> > > > > > MaintPlan (sqlmaint.exe) but there is no failure in the
MaintPlan.
> I
> > > > have
> > > > > > possibly traced it back to the "DELETE FILES" step of the
> MaintPlan.
> > > > This
> > > > > > appears to fail with no error in the MaintPlan History.
> > > > > >
> > > > > > The free drivespace is tight, there is slightly more room (~1gb)
> > than
> > > 3
> > > > > > fulls of the largest backup(~20gb each) (we are saving 2). The
> > > MaintPlan
> > > > > > needs at least enough room to create the new backup before it
can
> > > delete
> > > > > the
> > > > > > oldest backup. The Backup succeeds, but the delete fails (leaves
> > > behind
> > > > > the
> > > > > > oldest backup, total 3 fulls, should be 2), the job fails, but
the
> > > > > MaitPlan
> > > > > > doesn't and there is no record of a delete or any attempt to
> delete
> > or
> > > > an
> > > > > > error. My guess is that if a delete does not happen for any
> reason,
> > a
> > > > > > MaintPlan history entry for the delete step is not made.
> > > > > >
> > > > > > Any ideas..............
> > > > > >
> > > > > > Frankm
> > > > > > You know you are in trouble when a
> > > > > > learning curve becomes a death spiral
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||We realize most people aren't aware of the situations here in the newsgroups
until they have been around for a while so I just wanted to bring that to
light for everyone's knowledge. Keep up the good questions<g>
Andrew J. Kelly
SQL Server MVP
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:ekwyz1aXDHA.1748@.TK2MSFTNGP12.phx.gbl...
> I didn't mean to slight anyone. I was unaware of the status and
> affiliation of people responding to questions.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eWMgYZTXDHA.2328@.TK2MSFTNGP12.phx.gbl...
> > I am not sure if you were thinking of the MVP's as being part of the
> > Microsoft respondents but did want to make sure people understand we are
> not
> > actually employees of MS. We are all just volunteers that have been
> > acknowledged by MS in the MVP program. In either case I also want to
> point
> > out that there are lots of good answers from people other than MS or
MVP's
> > that shouldn't go unrecognized either<g>.
> >
> > Thanks
> >
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > news:Ouu6hcPXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> > > Yep, In previous jobs I did my own scripting, but in this job they use
> > > MaintPlans almost
> > > exclusively. I guess it's because all you have to do is clickity,
> > clickity,
> > > click.... and voila!
> > >
> > > Thanks, I now know that the lack of a error message is a feature, and
> > adjust
> > > accordingly..
> > >
> > > *****************************************
> > > To Microsoft respondents on these newsgroups... Your help is GREATLY
> > > appreciated and
> > > worth every penny. It really helps to be able to ask questions, get
> > answers
> > > and get on with my job.
> > > Thanks to all....................................
> > > *****************************************
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:OaWxR4OXDHA.2524@.TK2MSFTNGP09.phx.gbl...
> > > > The lack of traceability with the maintenance plan is one of the
many
> > > > reasons I never recommend using it. If you created your own
> scheduled
> > > jobs
> > > > to do those tasks you would have a much better grasp on the
situation
> > when
> > > > things go wrong.
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > > news:O4crQvOXDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > > > SQL Server service and SQL Server Agent both run under the same
> domain
> > > > login
> > > > > that has local admin rights on the machine. There are no problems
> > > logging
> > > > in
> > > > > with that login and doing anything, we do it everyday. The job
owner
> > is
> > > > also
> > > > > that account.
> > > > > This does not happen all the time, only occasionally. The fact
that
> > the
> > > > > DELETE FILES fails is one part. There is also the problem with the
> > fact
> > > > that
> > > > > there is a job failure with a message pointing to the
sqlmaint.exe,
> > but
> > > > > there is absolutely no indication of any failure at the
Maintenance
> > > Plan.
> > > > >
> > > > > Good point about another process holding the file(s). I will check
> to
> > > see
> > > > if
> > > > > another process has the file, but I am unclear as to why the
> > Maintenance
> > > > > Plan does not show an error.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > > news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> > > > > > Make sure you don't have any of the Expire or Retain Dates set
but
> > > here
> > > > is
> > > > > > a pretty good overview of why this usually happens by Bill from
> MS:
> > > > > >
> > > > > >
> > > > > > -- Log files don't delete --
> > > > > >
> > > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> > > > > >
> > > > > > This is likely to be either a permissions problem or a sharing
> > > violation
> > > > > > problem. The maintenance plan is run as a job, and jobs are run
by
> > the
> > > > > > SQLServerAgent service.
> > > > > >
> > > > > > Permissions:
> > > > > > 1. Determine the startup account for the SQLServerAgent service
> > > > > > (Start|Programs|Administrative
> > tools|Services|SQLServerAgent|Startup).
> > > > > This
> > > > > > account is the security context for jobs, and thus the
maintenance
> > > plan.
> > > > > > 2. If SQLServerAgent is started using LocalSystem (as opposed to
a
> > > > domain
> > > > > > account) then skip step 3.
> > > > > > 3. On that box, log onto NT as that account. Using Explorer,
> attempt
> > > to
> > > > > > delete an expired backup. If that succeeds then go to Sharing
> > > Violation
> > > > > > section.
> > > > > > 4. Log onto NT with an account that is an administrator and use
> > > Explorer
> > > > > to
> > > > > > look at the Properties|Security of the folder (where the backups
> > > reside)
> > > > > > and ensure the SQLServerAgent startup account has Full Control.
If
> > the
> > > > > > SQLServerAgent startup account is LocalSystem, then the account
to
> > > > > consider
> > > > > > is SYSTEM.
> > > > > > 5. In NT, if an account is a member of an NT group, and if that
> > group
> > > > has
> > > > > > Access is Denied, then that account will have Access is Denied,
> even
> > > if
> > > > > > that account is also a member of the Administrators group. Thus
> you
> > > may
> > > > > > need to check group permissions (if the Startup Account is a
> member
> > of
> > > a
> > > > > > group).
> > > > > > 6. Keep in mind that permissions (by default) are inherited from
a
> > > > parent
> > > > > > folder. Thus, if the backups are stored in C:\bak, and if
someone
> > had
> > > > > > denied permission to the SQLServerAgent startup account for C:\,
> > then
> > > > > > C:\bak will inherit access is denied.
> > > > > >
> > > > > > Sharing violation:
> > > > > > This is likely to be rooted in a timing issue, with the most
> likely
> > > > cause
> > > > > > being another scheduled process (such as NT Backup or Anti-Virus
> > > > software)
> > > > > > having the backup file open at the time when the SQLServerAgent
> > (i.e.,
> > > > the
> > > > > > maintenance plan job) tried to delete it.
> > > > > > 1. Download filemon and handle from www.sysinternals.com.
> > > > > > 2. I am not sure whether filemon can be scheduled, or you might
be
> > > able
> > > > to
> > > > > > use NT scheduling services to start filemon just before the
> > > maintenance
> > > > > > plan job is started, but the filemon log can become very large,
so
> > it
> > > > > would
> > > > > > be best to start it some short time before the maintenance plan
> > > starts.
> > > > > > 3. Inspect the filemon log for another process that has that
> backup
> > > file
> > > > > > open (if your lucky enough to have started filemon before this
> other
> > > > > > process grabs the backup folder), and inspect the log for the
> > results
> > > > when
> > > > > > the SQLServerAgent agent attempts to open that same file.
> > > > > > 4. Schedule the job or that other process to do their work at
> > > different
> > > > > > times.
> > > > > > 5. You can use the handle utility if you are around at the time
> when
> > > the
> > > > > > job is scheduled to run.
> > > > > >
> > > > > > If the backup files are going to a \\share or a mapped drive (as
> > > opposed
> > > > > to
> > > > > > local drive), then you will need to modify the above (with
respect
> > to
> > > > > where
> > > > > > the tests and utilities are run).
> > > > > >
> > > > > > Finally, inspection of the maintenance plan's history report
might
> > be
> > > > > > useful.
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Bill Hollinshead
> > > > > > Microsoft, SQL Server
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Andrew J. Kelly
> > > > > > SQL Server MVP
> > > > > >
> > > > > >
> > > > > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > > > > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > > > > > > We have been experiencing a "problem" with errors running
> > > Maintenance
> > > > > Plan
> > > > > > > Jobs.
> > > > > > > This is a Transaction Log and Full Backup Maintenance Plan.
> > > > > > > Below is the code for the Full Backup Job
> step...............
> > > > > > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > > > > > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> > > > > > DISK -BkUpDB
> > > > > > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> > > > > > >
> > > > > > > We are deleting backups over 36 hours old because we cannot
> trust
> > > the
> > > > > end
> > > > > > > time of the previous backup. The SQLServer Agent Job shows a
> > failure
> > > > for
> > > > > > the
> > > > > > > MaintPlan (sqlmaint.exe) but there is no failure in the
> MaintPlan.
> > I
> > > > > have
> > > > > > > possibly traced it back to the "DELETE FILES" step of the
> > MaintPlan.
> > > > > This
> > > > > > > appears to fail with no error in the MaintPlan History.
> > > > > > >
> > > > > > > The free drivespace is tight, there is slightly more room
(~1gb)
> > > than
> > > > 3
> > > > > > > fulls of the largest backup(~20gb each) (we are saving 2). The
> > > > MaintPlan
> > > > > > > needs at least enough room to create the new backup before it
> can
> > > > delete
> > > > > > the
> > > > > > > oldest backup. The Backup succeeds, but the delete fails
(leaves
> > > > behind
> > > > > > the
> > > > > > > oldest backup, total 3 fulls, should be 2), the job fails, but
> the
> > > > > > MaitPlan
> > > > > > > doesn't and there is no record of a delete or any attempt to
> > delete
> > > or
> > > > > an
> > > > > > > error. My guess is that if a delete does not happen for any
> > reason,
> > > a
> > > > > > > MaintPlan history entry for the delete step is not made.
> > > > > > >
> > > > > > > Any ideas..............
> > > > > > >
> > > > > > > Frankm
> > > > > > > You know you are in trouble when a
> > > > > > > learning curve becomes a death spiral
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi Frank,
The idea about maintenance plans a great idea for a future product
enhancement. I'd recommend that you forward the recommendation to the
Microsoft Wish Program:
Microsoft offers several ways for you to send comments or suggestions about
Microsoft products. If you have suggestions for product enhancements that
you would like to see in future versions of Microsoft products, please
contact us using one of the methods listed later in this article.
Let us know how we can improve our products.
Product Enhancement suggestions can include:
Improvements on existing products.
Suggestions for additional features.
Ways to make products easier to use.
All product enhancement suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.
World Wide Web - To send a comment or suggestion via the Web, use one of
the following methods:
In Internet Explorer 6, click Send Feedback on the Help menu and then click
the link in the Product Suggestion section of the page that appears.
In Windows XP, click Help and Support on the Start menu. Click Send your
feedback to Microsoft, and then fill out the Product Suggestion page that
appears.
Visit the following Microsoft Web site:
http://www.microsoft.com/ms.htm
Click Microsoft.com Guide in the upper-right corner of the page and then
click Contact Us . Click the link in the Product Suggestion section of the
page that appears.
Visit the following Microsoft Product Feedback Web site
http://register.microsoft.com/mswish/suggestion.asp
and then complete and submit the form.
E-mail - To send comments or suggestions via e-mail, use the following
Microsoft Wish Program e-mail address, mswish@.microsoft.com.
FAX - To send comments or suggestions via FAX, use the following Microsoft
FAX number, (425) 936-7329.
NOTE : Address the FAX to the attention of the Microsoft Wish Program.
US Mail - To send comments or suggestions via US Mail, use the following
Microsoft mailing address:
Microsoft Corporation
Attn. Microsoft Wish Program
One Microsoft Way
Redmond, WA 98052-6399
MORE INFORMATION
Each product suggestion is read by a member of our product feedback team,
classified for easy access, and routed to the product or service team to
drive Microsoft product and/or service improvements. Because we receive an
abundance of suggestions (over 69,000 suggestions a year!) we can't
guarantee that each request makes it into a final product or service. But
we can tell you that each suggestion has been received and is being
reviewed by the team that is most capable of addressing it.
All product or service suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.
Sincerely,
Alick Ye, MCSD
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
| From: "frankm" <frank@.nxspxm.mallardcentral.com>
| References: <#NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl> <eKPZ7PHXDHA.656
|
| SQL Server service and SQL Server Agent both run under the same domain
login
| that has local admin rights on the machine. There are no problems logging
in
| with that login and doing anything, we do it everyday. The job owner is
also
| that account.
| This does not happen all the time, only occasionally. The fact that the
| DELETE FILES fails is one part. There is also the problem with the fact
that
| there is a job failure with a message pointing to the sqlmaint.exe, but
| there is absolutely no indication of any failure at the Maintenance Plan.
|
| Good point about another process holding the file(s). I will check to see
if
| another process has the file, but I am unclear as to why the Maintenance
| Plan does not show an error.
|
|
|
|
|
| "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
| news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
| > Make sure you don't have any of the Expire or Retain Dates set but here
is
| > a pretty good overview of why this usually happens by Bill from MS:
| >
| >
| > -- Log files don't delete --
| >
| > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
| >
| > This is likely to be either a permissions problem or a sharing violation
| > problem. The maintenance plan is run as a job, and jobs are run by the
| > SQLServerAgent service.
| >
| > Permissions:
| > 1. Determine the startup account for the SQLServerAgent service
| > (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
| This
| > account is the security context for jobs, and thus the maintenance plan.
| > 2. If SQLServerAgent is started using LocalSystem (as opposed to a
domain
| > account) then skip step 3.
| > 3. On that box, log onto NT as that account. Using Explorer, attempt to
| > delete an expired backup. If that succeeds then go to Sharing Violation
| > section.
| > 4. Log onto NT with an account that is an administrator and use Explorer
| to
| > look at the Properties|Security of the folder (where the backups reside)
| > and ensure the SQLServerAgent startup account has Full Control. If the
| > SQLServerAgent startup account is LocalSystem, then the account to
| consider
| > is SYSTEM.
| > 5. In NT, if an account is a member of an NT group, and if that group
has
| > Access is Denied, then that account will have Access is Denied, even if
| > that account is also a member of the Administrators group. Thus you may
| > need to check group permissions (if the Startup Account is a member of a
| > group).
| > 6. Keep in mind that permissions (by default) are inherited from a
parent
| > folder. Thus, if the backups are stored in C:\bak, and if someone had
| > denied permission to the SQLServerAgent startup account for C:\, then
| > C:\bak will inherit access is denied.
| >
| > Sharing violation:
| > This is likely to be rooted in a timing issue, with the most likely
cause
| > being another scheduled process (such as NT Backup or Anti-Virus
software)
| > having the backup file open at the time when the SQLServerAgent (i.e.,
the
| > maintenance plan job) tried to delete it.
| > 1. Download filemon and handle from www.sysinternals.com.
| > 2. I am not sure whether filemon can be scheduled, or you might be able
to
| > use NT scheduling services to start filemon just before the maintenance
| > plan job is started, but the filemon log can become very large, so it
| would
| > be best to start it some short time before the maintenance plan starts.
| > 3. Inspect the filemon log for another process that has that backup file
| > open (if your lucky enough to have started filemon before this other
| > process grabs the backup folder), and inspect the log for the results
when
| > the SQLServerAgent agent attempts to open that same file.
| > 4. Schedule the job or that other process to do their work at different
| > times.
| > 5. You can use the handle utility if you are around at the time when the
| > job is scheduled to run.
| >
| > If the backup files are going to a \\share or a mapped drive (as opposed
| to
| > local drive), then you will need to modify the above (with respect to
| where
| > the tests and utilities are run).
| >
| > Finally, inspection of the maintenance plan's history report might be
| > useful.
| >
| > Thanks,
| >
| > Bill Hollinshead
| > Microsoft, SQL Server
| >
| > --
| >
| > Andrew J. Kelly
| > SQL Server MVP
| >
| >
| > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
| > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
| > > We have been experiencing a "problem" with errors running Maintenance
| Plan
| > > Jobs.
| > > This is a Transaction Log and Full Backup Maintenance Plan.
| > > Below is the code for the Full Backup Job step...............
| > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
| > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
| > DISK -BkUpDB
| > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
| > >
| > > We are deleting backups over 36 hours old because we cannot trust the
| end
| > > time of the previous backup. The SQLServer Agent Job shows a failure
for
| > the
| > > MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I
| have
| > > possibly traced it back to the "DELETE FILES" step of the MaintPlan.
| This
| > > appears to fail with no error in the MaintPlan History.
| > >
| > > The free drivespace is tight, there is slightly more room (~1gb) than
3
| > > fulls of the largest backup(~20gb each) (we are saving 2). The
MaintPlan
| > > needs at least enough room to create the new backup before it can
delete
| > the
| > > oldest backup. The Backup succeeds, but the delete fails (leaves
behind
| > the
| > > oldest backup, total 3 fulls, should be 2), the job fails, but the
| > MaitPlan
| > > doesn't and there is no record of a delete or any attempt to delete or
| an
| > > error. My guess is that if a delete does not happen for any reason, a
| > > MaintPlan history entry for the delete step is not made.
| > >
| > > Any ideas..............
| > >
| > > Frankm
| > > You know you are in trouble when a
| > > learning curve becomes a death spiral
| > >
| > >
| >
| >
|
|
|