Solved

Is transaction log file necessary?

Posted on 2004-04-02
8
969 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 69

Accepted Solution

by:
Scott Pletcher 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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

792 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