Re: Transaction log backup

From: Andrew Loree (nospam_at_please.com)
Date: 08/04/04 

Date: Tue, 3 Aug 2004 21:41:13 -0400

Any word on if this will ever be "fixed". Just one of those annoying things
that create log term headaches (pun intented). Why sqlmaint would every try
to issue a BACKUP log to a db in SIMPLE recovery makes zero sense.

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:eDFJwzWeEHA.2352@TK2MSFTNGP09.phx.gbl...
> Below KB might help:
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
>
> ------ Log files don't delete ----
> 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
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Rjean" <anonymous@discussions.microsoft.com> wrote in message
news:ae1d01c4796b$f199b320$a401280a@phx.gbl...
> > HI,
> >
> > each day, im backing up a dozen database with the options
> > to delete 2 days older previous backup . It works with
> > the databes but not for the transaction logs. It doesn't
> > delete at all previous backups. I recreate the data
> > maintenace plan without success , it still keep the old
> > trtansaction log backups . so i have to manually delete
> > them (very annoying )
> >
> > im running sq2000 (with latest SP) on a windows 2003
> > server
> >
> > WHAT CAN I DO ??
> >
>
>

Relevant Pages

  • Re: Mantanace Plan not deleting old files
    ... The problem I had was someone set up a maintenance plan to backup ALL ... > account is the security context for jobs, ... > and ensure the SQLServerAgent startup account has Full Control. ...
    (microsoft.public.sqlserver.server)
  • Re: Maintenance Plans
    ... This is likely to be either a permissions problem or a sharing violation ... account is the security context for jobs, and thus the maintenance plan. ... delete an expired backup. ...
    (microsoft.public.sqlserver.server)
  • Re: maintenance plan does not delete old .bak files
    ... Are you trying to perform log backup? ... account is the security context for jobs, and thus the maintenance plan. ... SQLServerAgent startup account is LocalSystem, ...
    (microsoft.public.sqlserver.server)
  • Re: Removing old transaction log backup files
    ... This is likely to be either a permissions problem or a sharing violation ... account is the security context for jobs, ... delete an expired backup. ... SQLServerAgent startup account is LocalSystem, ...
    (microsoft.public.sqlserver.server)
  • Re: "Remove files older than" does not work
    ... This is likely to be either a permissions problem or a sharing violation ... account is the security context for jobs, ... delete an expired backup. ... SQLServerAgent startup account is LocalSystem, ...
    (microsoft.public.sqlserver.server)