SQL TRN files are accumulating

I have a client which I did a SQL upgrade in August. I hired a SQL Engineer to do that. My client is experiencing issues with the 60 gig hard drive filling up and running out of space. There is a directory below the SQL file structure called Backup that is accumulating *.trn files. This is also the directory that houses the .bak files. SQL is set to backup everyday for a week and delete the older files. There are 165 trn files and about 43 Gigs of them.
My question is what are these trn files and why are they accumulating and can I delete them. Better yet can I get SQL to keep them from accumulating in the first place.
LVL 1
fi8224Asked:
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.

sanjeev559Commented:
On the first day create a full
backup, on the second and the third a differential backup, on the fourth day
repeat the pattern.

Make sure transaction log backups are frequent enough (based on your
workload).

This practice alows you to remove all files older than six days. And when I
say remove, I mean store in a dark, cool and dry place. :)
0
fi8224Author Commented:
I want to thank you for taking the time to address this issue. I feel I need to clarify a few things. First everything (OS and SQL)is backed  up full on tape everyday. Second SQL is doing its own backups, now a full one everday of a single week and then it repeats. I would think that the transaction logs (*.TRN files) are sql's way of tracking changes between backups. So in my way of thinking there should only be logs for between backups. (To me a backup is a *.BAK file). I have *.TRN files going all the way back to 8/01/05 when I did the migration to the new server.
So with this in mind  are the backups that you are talking about the "in SQL backups" or on tape backups. And why are the *.TRN files accumulating and not being cleaned up by SQL.
This has caused the database to report errors twice due to the hard drive running out of disk space. And I don't feel comfortable going in and manually deleting the *.TRN files until I feel I know what exactly I'm deleting.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sanjeev559Commented:
thats what you want exactly in the links which i sent
Please go through the links which i mentioned in the above comments
0
josephwalshCommented:
You could try the following :
Open SQL Enterprise Manager
Goto Management/Database Maintenance Plans, right click, & select new maintenance plan.
Here, select your database ( or all ) you can configure a backup maintenance plan that will auto remove old backups ( 5th window )
This will help keep your disk space tidy.

If you are not familiar with this I can give detailed instructions, but it is very easy to use.
0
fi8224Author Commented:
Ok you have me dealing with the future database maintenance. And don't let me get you wrong. There is already a database maintenance going on. The question is, "Why are the *.trn files accumulating in the first place?"
0
fi8224Author Commented:
I just looked at the database maintenance routine. And it is already set to delete transaction logs older that 2 weeks. Well that hasn't been happening. What do I do about that?
0
josephwalshCommented:
Have you looked at the SQL Server Agent ?
Are there backup jobs scheduled there ?

On your maintenance plan is the transaction log being backed up, and have you set it to auto delete older files. ?

Joe
0
fi8224Author Commented:
Yes...
and based on that thought, I took the liberty of deleting any trn file that was older than 3 weeks. That cleared up 43 gigs....
Now to find out why its not deleting them automatically. It seems to purge the bak files older than a week just fine.
0
josephwalshCommented:
I presume that you have deleted the maintenance plan for your database and re-created it.

Joe
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
fi8224Author Commented:
er, actually no, but on that recomendation i will give that a try. And thanks again for your feedback.
0
fi8224Author Commented:
I meant to split the award to both joseph and sanjeeve as both of you were recommending fixes through the SQL enterprise manager. Perhaps someone could fix that. The solution revolves around the maintenance plans and the deletion of the older BAK and TRN files automatically. I now have to wait these next two weeks to see if the older files are actually being deleted.
Thanks again guys for your help.
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
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.