Is transaction log file necessary?

If ldf files contain only logs, are they really necessary? Can the log file be somehow deleted, or can it start logging from scratch, so that only new transactions are logged?

Thanks
gromulAsked:
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.

arbertCommented:
Yes, the transaction log is what guarantees consistency/integrity of the data.

As long as you have a backup plan in place, the log files will truncate and then you can shrink them so they don't take up space...
0
acampomaCommented:
Yes, I agree with Arbert
The transaction logs are there for recovery.
They ensure that all data is committed or rolled back during recovery.
you can do a "backup log databaseName With Truncate_only" and shedule this every day or several times a day to prevent the log file from growing too big.
If if grows too big,you will have to shrink it after truncating it. Space claimed by the log is not given up when it is emptied, it must be shrunk
0
afuchigamiCommented:
Not sure what you're looking for in your question - are you worried about the Transaction log file growing very large?  What do you mean by only new transactions are logged?

Transactions are used to recover to a certain point in time.  You can't run a SQL Server database without them.  If your backup scheme doesn't need this point in time recovery (you only need to be able to recover to the last full or differential backup), then you can set your Transaction log to truncate so that the log file doesn't get too big.  (In SQL7.0, there's a Truncate on Checkpoint option, and in SQL2K, set the Recovery Model to Simple)  Otherwise, your Transaction log won't get truncated until you do a backup (full, diff or transaction log backup)

Keep in mind your transaction log has to be big enough to handle all the transactions between truncation.  So you still need to either make sure the file is large enough, or set the Auto-grow properties.

Hope this helps.
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.

Scott PletcherSenior DBACommented:
Yes, a transaction log is *integral* (essential) to a db.  In fact, it is best to think of a functioning database as its data *and* log files together.  (I guess that theoretically a read-only db wouldn't need a log, but that's a rare situation.)

To understand why, you have to keep in mind that SQL Server's biggest focus is to insure that data is *always* accurate (to the degree required; that is, if you explicitly issue a SET TRANSACTION ISOLATION LEVEL command, you have changed what SQL considers "accurate").  In order to do that, it *must* have a log.  Otherwise, when an error occurred, SQL might not be able to accurately put the db back to the state it was in before the statement(s) in error ran, that is, back to accurate state.

You can force a new, essentially empty log to be created by detaching and re-attaching the db.  This requires the db to be unavailable for the period between the detach and re-attach.  If you want more info on this method of "reducing" the log, please let me know.
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
gromulAuthor Commented:
I'm currently testing my asp.net application so I do a lot of inserting/deleting. Because of that, the .ldf file has grown 50% larger: 4 to 6mb. That's not too much, but when I actually deploy the app, I thought of starting the new log file from scratch. After that, I probably won't mess with it anymore.
0
amit1978Commented:
Hi gromul,

First two comment r absulately right ......... there is no need to have Log files (.ldf) its basically use to recovery.

On deply of database u doesnot required ldf file its automatically create as requred. and u also fixed the size of log file like log file size cannot be grater then 10MB.

Amit Jain
0
arbertCommented:
"there is no need to have Log files (.ldf) its basically use to recovery."

I don't think you meant to say "there is no need"....
0
gromulAuthor Commented:
Thanks guys.
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.

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.