Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1007
  • Last Modified:

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
0
gromul
Asked:
gromul
5 Solutions
 
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
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
 
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
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.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now