Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 995
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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