"Chris Newby" <Chris.Ne***@Rockcreekglobal.com> wrote in message
news:Ou1TQ1fQGHA.2040@TK2MSFTNGP14.phx.gbl...
>I created a maintenance plan using enterprise manager 8 for a sql server 2000 database. In regards
>to the transaction log, the maintenance plan is configured to:
>
>    - backup the transaction log
>    - verify the backup's integrity
>    - use a non-default file system location for storage
>    - *remove files older than 1 day*
>    - use TRN as a file extension
>    - scheduled to run nightly
>
> Everything works fine with one exception: the older transaction log backups are not being deleted
> (those that are older than one day).
>
> This same maintenance plan is also configured identically to backup the database data, and in that
> case *is* deleting the older data backups. The plan is also configured to run optimizations once a
> week on Sundays which include reorganization and shrinking.
>
> Obviously, I'd rather not have to delete these extra backup files manually or with an extraneous
> script. However the setup and configuration of the maintenance plan is very straight forward ...
> it simply appears not to work (on the point of the transaction logs).
>
> Our service packs are all up to date and I checked the maintenance plan history log which
> contained entries like "Delete old db backup files" but did not contain anything like "Delete old
> transaction backup files" ... so what gives?
>
> Any ideas are much apreciated!
>
> TIA//
>
>

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
message news:u$gg71gQGHA.4900@TK2MSFTNGP09.phx.gbl...
> Did you include any databases in simple recovery in that plan?
>
> Also see
>
> 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/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "Chris Newby" <Chris.Ne***@Rockcreekglobal.com> wrote in message
> news:Ou1TQ1fQGHA.2040@TK2MSFTNGP14.phx.gbl...
>>I created a maintenance plan using enterprise manager 8 for a sql server
>>2000 database. In regards to the transaction log, the maintenance plan is
>>configured to:
>>
>>    - backup the transaction log
>>    - verify the backup's integrity
>>    - use a non-default file system location for storage
>>    - *remove files older than 1 day*
>>    - use TRN as a file extension
>>    - scheduled to run nightly
>>
>> Everything works fine with one exception: the older transaction log
>> backups are not being deleted (those that are older than one day).
>>
>> This same maintenance plan is also configured identically to backup the
>> database data, and in that case *is* deleting the older data backups. The
>> plan is also configured to run optimizations once a week on Sundays which
>> include reorganization and shrinking.
>>
>> Obviously, I'd rather not have to delete these extra backup files
>> manually or with an extraneous script. However the setup and
>> configuration of the maintenance plan is very straight forward ... it
>> simply appears not to work (on the point of the transaction logs).
>>
>> Our service packs are all up to date and I checked the maintenance plan
>> history log which contained entries like "Delete old db backup files" but
>> did not contain anything like "Delete old transaction backup files" ...
>> so what gives?
>>
>> Any ideas are much apreciated!
>>
>> TIA//
>>
>>
>