CODEWITHSUNDEEP
sqlsql-serversql-server-2008backup
now he who must not be named.
now he who must not be named.

Reputation: 9398

What happens when a transaction is being carried out during backing up of LDF files?

My DB Admin advised that I should regularly take backup of .ldf files. Fine, this SQL post here explains this beautifully.

Consider that a transaction is being done in SQL Server. And at the same time, a scheduled process tries to access the .ldf file for backing it up.

What happens ? How this works ?

Upvotes: 0

Views: 50

Answers (1)

Shanky
Shanky

Reputation: 626

You must read Article Understanding SQL Server backup by Paul Randal. That is the best I can see which is available and can explain you in details various aspects.

Coming to your question a transaction log backup includes all information from previous transaction log back or full backup that started the log chain. Backup simply means reading information froma file(data or log) and writing it to destination disk. The transaction any would work independed of log backup running. A transaction follow a WAL(write ahead logging) protocol, for practical purposes all transaction information is first written in log file and then changes are later made to data file. So when transaction is running it would not be affected by transaction log backup job which is running both are doing different task and are muttually exclusive events. Current backup would try to backup all logs which are marked as committed and would truncate the logs if no transaction requires it. If any portion of log is committed after log backup has read that portion it would not come in current log backup but would come under further log backup.

Transacion log backup has important role in crash recovery it helps in determining what all operations has to be roll forwared and what has to be rolled back. Without transaction log backup or transaction log crash recovery is not possible

You must also read Logging and recovery in SQL Server to know about life cycle of a transaction.

The excat answer as to what acctual steps happens inside is beyond scope of discussion as nobody can exactly tell you what would happen but reading the article would give you a good idea.

Please let me know if you have any further questions.

Upvotes: 3

Related Questions