Tell me more ×
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.
up vote -1 down vote favorite

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|improve this question

migrated from stackoverflow.com Apr 18 at 12:48

2 Answers

up vote 3 down vote

This interpretaion is incorrect. A good step by step explanation is here: http://bradmcgehee.com/wp-content/uploads/presentations/Inside%20the%20SQL%20Server%20Transaction%20Log.pdf

In simple recovery mode, everytime data is updated, the transaction is written to the transaction log (more data , bigger transaction description). Once the actual database data has been updated (as soon as SQL detect free time to do it). It marks dirty data blocks with old data as reusable in the main database file and marks the associated transation statement in the tranasaction log as (reusable / overwritable). The size of the log will depend on the biggest sequence of trransaction that cannot be writtten imediately.

If full recovery mode, the transaction log statements are kep untill the log is backedup. then all transactions are marked as (reusable / overwritable). The log is not shrunk unless explicitly done.

share|improve this answer
up vote 1 down vote

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|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.