How do I manage my transaction log? My data is only 300 megs, my log is 48 GIGS!!! Help!

I have a database that, each night, I pull files from my mainframe and write them to SQL.  I first delete the existing file, then pull down the new data from the AS400.  I dont know anything about transaction logs, do I HAVE to have em?  Can I turn it off on this database?  How do I shrink it?  I obviously dont need records of all my old table iterations!!

Suggestions are welcome please!
LVL 1
JUSTICEAsked:
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.

Scott PletcherSenior DBACommented:
Try shrinking the log by running these commands from QA:

BACKUP LOG yourDbName
WITH TRUNCATE_ONLY

DBCC SHRINKFILE (logical_file_name)
-- you can find the logical file name using EXEC sp_helpfile with current db = yourDbName
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
Scott PletcherSenior DBACommented:
If still not shrunk, use the BACKUP LOG ... TRUNCATE_ONLY one more time.
0
JUSTICEAuthor Commented:
Perfect!!  :)  Thank you!!!
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.

BillAn1Commented:
if you don't want/need transaction logs, you can set the recovery model to simple
(from EM right-click the database, and then click Properties.Click the Options tab. click on Simple)
This will keep the transaction log from growing. However, it means that if you have a database failure, you can only recover to your last backup. Also, the transactionlog may still grow to a certain size, as it needs to keep details of a transaction until the transaction is complete. e.g. if you are loading a large amount of data in one transaction, it may still get large temporarily.
0
JUSTICEAuthor Commented:
Thats perfect.  All of this data is completely transitory, as I pull it from my AS400 each night, simply to make sure nobody has web access to our mainframe. :)
0
Scott PletcherSenior DBACommented:
Btw, I suggest setting the log size to a reasonable starting size yourself -- say 50M -- and changing the growth to a fixed amount, say 25-50M, rather than 10%.  10% seems to cause a much bigger performance hit when expanding than a fixed amount.

You can make these changes in EM by typing over the value in the Database "Properties" tab or from QA using ALTER DATABASE commands.

If you need additonal info, just let me know :-)

Thanks for the points!!
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
Fonts Typography

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.