Solved

Is transaction log file necessary?

Posted on 2004-04-02
8
965 Views
Last Modified: 2012-05-04
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
Comment
Question by:gromul
8 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 50 total points
ID: 10744255
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
 
LVL 6

Assisted Solution

by:acampoma
acampoma earned 50 total points
ID: 10744598
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
 

Assisted Solution

by:afuchigami
afuchigami earned 50 total points
ID: 10744625
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 300 total points
ID: 10744840
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:gromul
ID: 10745769
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
 
LVL 5

Assisted Solution

by:amit1978
amit1978 earned 50 total points
ID: 10747432
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
 
LVL 34

Expert Comment

by:arbert
ID: 10750136
"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
 

Author Comment

by:gromul
ID: 10755261
Thanks guys.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now