Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Could someone tell me the role of transaction logs in SQL Server ?

I have a transaction log file, after making a lot of transactions for testing purpose I take the backup of it via command. Sometimes the size of it is around 200KB and sometimes its 10 MB ?

My concept of it is, when the transactions logs file is full it flushes the data/transactions to database if recovery mode is simple, isn't? But if the recovery mode is full then every time after the transaction log file is full the backup of log file has to be taken in order to empty the transactions logs file, but should the backup not be of uniform size ? How can it differ like 200KB and 10 MB ??

:)

share
comments disabled on deleted / locked posts

migration rejected from stackoverflow.com Nov 10 '13 at 23:52

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as too broad by Mark Storey-Smith, Max Vernon, RolandoMySQLDBA, Michael - sqlbot, Paul White Nov 10 '13 at 23:52

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

Every SQL Server database is mapped over a set of operating-system files. These files store data and log information. Individual files are used only by one database, and data and log information are never mixed in the same file. While data is stored in an MDF file, all transactions and the SQL Server database modifications made by each transaction are stored in an LDF file – a transaction log file which is an essential component of the database. Conceptually, the log file is a string of log records. Physically, the log records are stored in one or the set of physical LDF files that implement the transaction log

The primary purpose of an LDF file is to provide the ACID concept - Atomicity, Consistency, Isolation, and Durability

  • atomicity: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged
  • consistency :any transaction brings the database from one valid state to another
  • isolation: the execution of concurrent transactions brings the database to a state as if the transactions were executed serially, one by one
  • durability: once committed, the transaction remain so, even in the case of errors, power loss, or crashes

An LDF file stores enough information to replay or undo a change, or recover the database to a specific point in time. Therefore, due to various auditing or recovery requirements, there is often a need to open the LDF file and view its contents. But viewing LDF file content is not an easy task

share
add comment

You need to read up on T-log from below excellent articles and after that you will realize that what you are thinking is not correct :

Understanding Logging and Recovery in SQL Server by Paul

Stairway to Transaction Log Management in SQL Server by Gail

share
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.