.TRN files will not delete

I am using SQL 2000 and using the database maintenance plan to delete both the .bak and .trn files.

The scheduler deletes the .bak files just fine but NOT the .trn files.  The .trn files keep stacking up which I have to manually delete via Windows Explorer.

Any ideas?
LVL 2
mcnuttlawAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this happens if you have the database maintenance plan to do a transaction log backup on ALL databases, ie on at least 1 database that is NOT in recovery model = full.
it's a "bug" in SQL Server that it does not delete ANY .trn file as soon as there is a problem in ANY of the transaction log backups :-(

The solution:
Create 1 maintenance plan for the full backup (possibly all databases)
Create 1 maintenance plan for the transaction backup (only the databases in full recovery mode)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcnuttlawAuthor Commented:
okay angelIII,

It seemed to work.  I have one vendor-created database (for spam detection) that is set to simple.  Once I took that db off the transaction maintenance, all is well and the .trn files get deleted right on schedule.

Is there a problem in simple databases not having their transactions backed up?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Is there a problem in simple databases not having their transactions backed up?
the only functionality you loose is that you cannot restore to a point in time.
with software like you described, the restore process is usually faster by reinstallating the software completely and configuring the few settings and it's gone.

technically, you cannot backup (ie it makes no sense) the transaction log of a database that is in simple recovery mode.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.